Determine Quarter End and Beginning Dates

Hello,

I have a query that I would like to schedule in DTS. The criteria of
this query checks for records in the table that are within the current
quarter. Here is what I have.

WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'

I would like to dynamically generate the Quarter End and Quarter
Beginning dates within my where clause based on the date that DTWS
package is being executed on. Can anyone show me how this can be
accomplished?

Thank You."Matt" <matt_marshall@.manning-napier.com> wrote in message
news:1112196440.142834.300110@.z14g2000cwz.googlegr oups.com...
> Hello,
> I have a query that I would like to schedule in DTS. The criteria of
> this query checks for records in the table that are within the current
> quarter. Here is what I have.
> WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
> I would like to dynamically generate the Quarter End and Quarter
> Beginning dates within my where clause based on the date that DTWS
> package is being executed on. Can anyone show me how this can be
> accomplished?
>
> Thank You.

Quick and dirty solution - see DATEPART in Books Online.

The longer answer is that using DATEPART might not be good for performance
(applying a function to a column prevents MSSQL using an index on that
column), so you may need another approach. One would be to write a stored
proc to return the first and last days of the current quarter, so you can
put them in variables and use them in your query; another would be to create
a calendar table (which is very useful anyway) and join on it in your query.

A couple of other small points - BETWEEN with datetime columns can give you
unexpected results if you don't allow for the time portion. In your case,
this is probably safer:

where submit_date >= '20050101' and submit_date < '20050401'

Also, try to use the YYYYMMDD date format if possible - it will always be
interpreted correctly by MSSQL regardless of client or server settings. More
information here:

http://www.karaszi.com/sqlserver/info_datetime.asp

Simon|||> WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
> I would like to dynamically generate the Quarter End and Quarter
> Beginning dates within my where clause based on the date that DTWS
> package is being executed on. Can anyone show me how this can be
> accomplished?

The easy way to do this is to set up a dates table that has columns for
quarter and year and then join
e.g. If you have a table: dates
D as datetime, Year as integer, Quarter as integer
20050101, 2005, 1
20050102, 2005, 1
...
20051231, 2005, 4

And then in your query:
Join (select Quarter, Year from dates where d = CONVERT(getdate(), datetime,
112) as t
Join dates on submitdate = d
where d.Quarter = t.Quarter and d.year = t.Year

The Hard Way is to calculate it in line:

If you want current quarter then:
WHERE submit_date BETWEEN
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-1) / 3 * 3 + 1 as varchar(2)),2) + '01' AND
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST((Month(GetDate())-1) / 3 * 3 + 4 as varchar(2)), 2) + '01'

Last quarter is harder:
WHERE submit_Date BETWEEN
CASE WHEN Month(GetDate()) < 4 THEN
CAST(Year(GetDate()) - 1 as varchar(4)) + '0901'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()-1) / 3 * 3 - 2 as varchar(2)),2) + '01'
END
AND
CASE WHEN Month(GetDate()) < 4 THEN
CAST(Year(GetDate()) as varchar(4)) + '0101'
ELSE
CAST(Year(GetDate()) as varchar(4)) + Right('0' +
CAST(Month(GetDate()) / 3 * 3 + 1 as varchar(2)), 2) + '01'
END|||On 30 Mar 2005 07:27:20 -0800, Matt wrote:

>Hello,
>I have a query that I would like to schedule in DTS. The criteria of
>this query checks for records in the table that are within the current
>quarter. Here is what I have.
>WHERE submit_date BETWEEN '01/01/2005' AND '03/31/2005'
>I would like to dynamically generate the Quarter End and Quarter
>Beginning dates within my where clause based on the date that DTWS
>package is being executed on. Can anyone show me how this can be
>accomplished?
>
>Thank You.

Hi Matt,

In addition to the answers Simon and James gave, here's a quick formula
to calculate the first and last date of the quarter:

declare @.test datetime
set @.test = '20051201'
SELECT DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test),
'20000101'),
DATEADD(quarter, DATEDIFF(quarter, '20000101', @.test) + 1,
'19991231')

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sql

Determine Nearest Day

Hi,
Users of an application can retrieve sales between 2 dates that they pick in
the front end (DateFrom & DateTo)
I have sales data that these values will be used to query, the data is
always summarised to the Sunday of each w.
Before my stored procedure attempts to query, it needs to work out the
nearest Sunday for each of the dates. Eg;
DateFrom = 14th December 2005
This date needs to be converted to look 'backwards' for the nearest Sunday.
It needs to find 11th December.
DateTo = 29th December 2005
This needs to be converted forwards to look to the nearest Sunday. It needs
to find 01 Jan 2006.
Is there any TSQL that can be used to work this out, or is it a case of
using a lookup table (which I do have available)
Thanks
DylanHi
DECLARE @.Today datetime
SET @.Today = '20060105'
SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7+6 , '1900')
"DylanM" <DylanM@.discussions.microsoft.com> wrote in message
news:031DC14C-596B-411C-B15D-88D17743AB7E@.microsoft.com...
> Hi,
> Users of an application can retrieve sales between 2 dates that they pick
> in
> the front end (DateFrom & DateTo)
> I have sales data that these values will be used to query, the data is
> always summarised to the Sunday of each w.
> Before my stored procedure attempts to query, it needs to work out the
> nearest Sunday for each of the dates. Eg;
> DateFrom = 14th December 2005
> This date needs to be converted to look 'backwards' for the nearest
> Sunday.
> It needs to find 11th December.
> DateTo = 29th December 2005
> This needs to be converted forwards to look to the nearest Sunday. It
> needs
> to find 01 Jan 2006.
> Is there any TSQL that can be used to work this out, or is it a case of
> using a lookup table (which I do have available)
>
> Thanks
> Dylan
>|||Excellent, thanks Uri.
And to convert backwards, I thinks it's just as follows...' (-1 instead of
+6)
SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7-1 , '1900')
Thanks again.
"Uri Dimant" wrote:

> Hi
> DECLARE @.Today datetime
> SET @.Today = '20060105'
> SELECT DATEADD(day, DATEDIFF(day, '1900', @.Today)/7*7+6 , '1900')
>
>|||This rounds it up to the nearest Sunday. It prints DateTo, the day or the
w dateto is, and then the rounded up date, and the day of the w the
rounded up day is.
I suspect you can use the modulus operator to make this perform better, but
I'm not SK.
create table salesData
(pk int not null identity primary key,
DateTo datetime,
DateFrom Datetime)
GO
insert into salesData values (getdate()-101,getdate()-103)
insert into salesData values (getdate()-105,getdate()-108)
GO
select case when DATENAME ( dw , DateTo )='Sunday' then dateto
when DATENAME ( dw , DateTo )='Monday' then dateto-1
when DATENAME ( dw , DateTo )='Tuesday' then dateto-2
when DATENAME ( dw , DateTo )='Wednesday' then dateto-3
when DATENAME ( dw , DateTo )='Thursday' then dateto-4
when DATENAME ( dw , DateTo )='Friday' then dateto-5
when DATENAME ( dw , DateTo )='Saturday' then dateto-6
end,
dateto, datename(wday,dateto),
case when DATENAME ( dw , DateTo )='Sunday' then datename(wday, dateto)
when DATENAME ( dw , DateTo )='Monday' then datename(wday, dateto-1)
when DATENAME ( dw , DateTo )='Tuesday' then datename(wday, dateto-2)
when DATENAME ( dw , DateTo )='Wednesday' then datename(wday, dateto-3)
when DATENAME ( dw , DateTo )='Thursday' then datename(wday, dateto-4)
when DATENAME ( dw , DateTo )='Friday' then datename(wday, dateto-5)
when DATENAME ( dw , DateTo )='Saturday' then datename(wday, dateto-6)
end
from salesdata
go
drop table salesdata
go
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"DylanM" <DylanM@.discussions.microsoft.com> wrote in message
news:031DC14C-596B-411C-B15D-88D17743AB7E@.microsoft.com...
> Hi,
> Users of an application can retrieve sales between 2 dates that they pick
> in
> the front end (DateFrom & DateTo)
> I have sales data that these values will be used to query, the data is
> always summarised to the Sunday of each w.
> Before my stored procedure attempts to query, it needs to work out the
> nearest Sunday for each of the dates. Eg;
> DateFrom = 14th December 2005
> This date needs to be converted to look 'backwards' for the nearest
> Sunday.
> It needs to find 11th December.
> DateTo = 29th December 2005
> This needs to be converted forwards to look to the nearest Sunday. It
> needs
> to find 01 Jan 2006.
> Is there any TSQL that can be used to work this out, or is it a case of
> using a lookup table (which I do have available)
>
> Thanks
> Dylan
>|||To do it in one shot, try this:
SELECT DATEADD(day, DATEDIFF(day, '18991231', @.Today+3)/7*7 , '18991231')
It will find "next" Sunday, if today is Thursday, Friday, or Saturday,
it will find "This"
Sunday if today is Sunday, and it will find "last" Sunday if today is
Monday, Tuesday, or Wednesday.
Steve Kass
Drew Unviersity
DylanM wrote:

>Hi,
>Users of an application can retrieve sales between 2 dates that they pick i
n
>the front end (DateFrom & DateTo)
>I have sales data that these values will be used to query, the data is
>always summarised to the Sunday of each w.
>Before my stored procedure attempts to query, it needs to work out the
>nearest Sunday for each of the dates. Eg;
>DateFrom = 14th December 2005
>This date needs to be converted to look 'backwards' for the nearest Sunday.
>It needs to find 11th December.
>DateTo = 29th December 2005
>This needs to be converted forwards to look to the nearest Sunday. It needs
>to find 01 Jan 2006.
>Is there any TSQL that can be used to work this out, or is it a case of
>using a lookup table (which I do have available)
>
>Thanks
>Dylan
>
>sql

Determine Duration between Two Dates

All
I have a table of members and I would like to determine how long each member
has been a member based upon the current date. I would like the result
retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
assistance would be appreciated.
CREATE TABLE member
(
MemberID INT,
DateJoined SMALLDATETIME
)
INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
Desired Result:
1 1999-01-11 2005-10-13 6y 9m 2d
2 2004-12-26 2005-10-13 0y 10m 18d
3 2005-01-01 2005-10-13 0y 10m 12d
ThanksHi David,
There must be a simpler way than this, but what the heck...
SELECT id, dt, y, m,
diff_d - CASE WHEN dt_ym + diff_d > today THEN 1 ELSE 0 END AS d
FROM
(
SELECT *, DATEDIFF(day, dt_ym, today) AS diff_d
FROM
(
SELECT id, dt, today, y, m,
DATEADD(month, m, dt_y) AS dt_ym
FROM
(
SELECT id, dt, today, y, dt_y,
m_diff - CASE WHEN DATEADD(month, m_diff, dt_y) > today
THEN 1 ELSE 0 END AS m
FROM
(
SELECT *, DATEDIFF(month, dt_y, today) AS m_diff
FROM
(
SELECT *, DATEADD(year, y, dt) AS dt_y
FROM
(
SELECT id, dt, today,
y_diff - CASE WHEN DATEADD(year, y_diff, dt) > today
THEN 1 ELSE 0 END AS y
FROM
(
SELECT *, DATEDIFF(year, dt, today) AS y_diff
FROM
(
SELECT MemberID AS id, DateJoined AS dt,
CAST(CONVERT(VARCHAR(8), GETDATE(), 112) AS DATETIME) AS today
FROM member
) AS D1
) AS D2
) AS D3
) AS D4
) AS D5
) AS D6
) AS D7
) AS D8;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"David" <David@.discussions.microsoft.com> wrote in message
news:44CF768B-1213-453F-8A62-024857AF5089@.microsoft.com...
> All
> I have a table of members and I would like to determine how long each
> member
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks|||Hi David
Probably you can check the link.
http://chanduas.blogspot.com/2005/0...lating-age.html
This is not the exact solution but can give u an idea
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"David" wrote:

> All
> I have a table of members and I would like to determine how long each memb
er
> has been a member based upon the current date. I would like the result
> retuned in the Number of Years, Months and Days ie. 5y 6m 26d. Any
> assistance would be appreciated.
> CREATE TABLE member
> (
> MemberID INT,
> DateJoined SMALLDATETIME
> )
> INSERT INTO member SELECT 1, '1999-01-11 00:00:00'
> INSERT INTO member SELECT 2, '2004-12-26 00:00:00'
> INSERT INTO member SELECT 3, '2005-01-01 00:00:00'
>
> Desired Result:
> 1 1999-01-11 2005-10-13 6y 9m 2d
> 2 2004-12-26 2005-10-13 0y 10m 18d
> 3 2005-01-01 2005-10-13 0y 10m 12d
>
> Thanks

Determine a date not in the table

Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegr oups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~

Determine a date not in the table

Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~

Determine a date not in the table

Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~

Desupport dates

Is the a page online that lists any desupport dates for
the various versions of SQL Server (v7, 2000 etc)http://support.microsoft.com/defaul...en-gb;lifeprods
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:b62101c407bb$afa9b4f0$a401280a@.phx.gbl...
> Is the a page online that lists any desupport dates for
> the various versions of SQL Server (v7, 2000 etc)

Designing a fact table to hold customer-product ownership by day

If I'm designing a fact table to hold customer-product ownership by day, can one somehow get by with just storing customer purchase dates and return dates, rather than a record for each day the customer owns a product?

I need to make sure that if a customer bought a product in Jan and returned it 1 month later, but then bought it again in April - that they show up as having that item in
Jan and April - to date (but excluding Feb, March)

One thing that will be tough is the potential for this to happen several times with the same customer, but still showing all gaps of not having product. If a date dimension is not used, the I'd really like to take the customer's current status for a product - is it possible to do this?

Also, another question, speaking of customer counts and rollup
once it's determined that a customer has a certain product on x day (count of 1) and doesn't have a product on a different day (count of -1),
how would one determine that for the level above product, -subcategory-, that there were 5 customers that had Accessories?

I see that adventure works uses distinct count to make up customer count, but what about when returns are entered into the picture?
I'm thinking of doing them as -1, but maybe there's a better way to handle them and ultimately, the 'distinct customer count' rollup to higher levels?

I realize this post contains a lot of questions - if I can get at least one answered for starters, that would be awesome.

Hi,

if you contact me by mail and send me more detail and if it's possible some data I can try to figure out a possible solution. (If you can wait few days)

francesco.dechirico(AT)fastwebnet.it

|||

Still looking into this problem - months later

With a fact table like:

Customer Product Date Qty

1 1 1/1/2005 1

1 1 3/2/2005 -1

1 2 4/1/2005 1

I'd like to have a query show that a customer is active on dates 1/1/2005-3/1/2005, and from 4/1/2005 on... this should also be done by distinct count of customer ID, but down to product if possible.

Here is an example mdx statement I composed, but the results aren't exactly what I'm seeking. Yes, it adds up Invoice Qty, but I was hoping to 'create' the date rows in between.

with member x

as DistinctCount(sum({null:[Transaction Date].[Calendar Date].CurrentMember}, [Invoice Qty]), Customer.Customer.[Customer ID].CurrentMember)

member y as [Transaction Date].[Calendar Date].CurrentMember.UniqueName

select {[invoice sales],[Measures].[Invoice Qty], x, y} on 0,

[Transaction Date].[Calendar Month].[Calendar Month] on 1

from sales

where (customer.customer.&[1])

|||

Any ideas on this one?

Mainly, I'm looking for something to create the non existant date rows in the output. Whether it be a script SCOPE or calculated member..

|||

Ok, I got a lot further after reading the ideas in this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1205862&SiteID=1

I've defined a measure to be Cumulative Qty Shipped - summing up all shipped qty to the day in question.

Then another measure (CustomerCount) that does: iif([Cumulative Qty Shipped] > 0, 1, NULL) -- in order to make sure that a customer only gets a tally of 1, not any higher.

Now, what I'm concerned about is the bleeding that happens. ie:

If looking at 2 customers, the execution path of AS is to sum all qty shipped to the current date section, then evaluate the logic: iif([Cumulative Qty Shipped] > 0, 1, NULL)

This would also happen if looking at 1 customer and multiple products. All of the products' qty would be summed, then evaluated for > 0..

Is there a way to sort of turn this around so that the calculation is first done on strictly customer and product, then doing the iif([Cumulative Qty Shipped] > 0, 1, NULL) check?

Plus another behavior needed is 'distinct' customer count. If looking at the all products level, and a customer owns 3 products, they should still only be counted once. If looking the members of Products, then the customer could be tallied once per product.

|||

I tried another script scope, hoping that it would work to cover the needs discussed, but it did not.

This is what I'd used. Hopefully it will spark some ideas from others!

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

this = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

ENDSCOPE;

and differently:

SCOPE(Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

Measures.CustomerCount = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

ENDSCOPE;

|||

I hate to be responding so many times in a row, but I'm looking for some direction on this one.

Thank you!!

Designing a fact table to hold customer-product ownership by day

If I'm designing a fact table to hold customer-product ownership by day, can one somehow get by with just storing customer purchase dates and return dates, rather than a record for each day the customer owns a product?

I need to make sure that if a customer bought a product in Jan and returned it 1 month later, but then bought it again in April - that they show up as having that item in
Jan and April - to date (but excluding Feb, March)

One thing that will be tough is the potential for this to happen several times with the same customer, but still showing all gaps of not having product. If a date dimension is not used, the I'd really like to take the customer's current status for a product - is it possible to do this?

Also, another question, speaking of customer counts and rollup
once it's determined that a customer has a certain product on x day (count of 1) and doesn't have a product on a different day (count of -1),
how would one determine that for the level above product, -subcategory-, that there were 5 customers that had Accessories?

I see that adventure works uses distinct count to make up customer count, but what about when returns are entered into the picture?
I'm thinking of doing them as -1, but maybe there's a better way to handle them and ultimately, the 'distinct customer count' rollup to higher levels?

I realize this post contains a lot of questions - if I can get at least one answered for starters, that would be awesome.

Hi,

if you contact me by mail and send me more detail and if it's possible some data I can try to figure out a possible solution. (If you can wait few days)

francesco.dechirico(AT)fastwebnet.it

|||

Still looking into this problem - months later

With a fact table like:

Customer Product Date Qty

1 1 1/1/2005 1

1 1 3/2/2005 -1

1 2 4/1/2005 1

I'd like to have a query show that a customer is active on dates 1/1/2005-3/1/2005, and from 4/1/2005 on... this should also be done by distinct count of customer ID, but down to product if possible.

Here is an example mdx statement I composed, but the results aren't exactly what I'm seeking. Yes, it adds up Invoice Qty, but I was hoping to 'create' the date rows in between.

with member x

as DistinctCount(sum({null:[Transaction Date].[Calendar Date].CurrentMember}, [Invoice Qty]), Customer.Customer.[Customer ID].CurrentMember)

member y as [Transaction Date].[Calendar Date].CurrentMember.UniqueName

select {[invoice sales],[Measures].[Invoice Qty], x, y} on 0,

[Transaction Date].[Calendar Month].[Calendar Month] on 1

from sales

where (customer.customer.&[1])

|||

Any ideas on this one?

Mainly, I'm looking for something to create the non existant date rows in the output. Whether it be a script SCOPE or calculated member..

|||

Ok, I got a lot further after reading the ideas in this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1205862&SiteID=1

I've defined a measure to be Cumulative Qty Shipped - summing up all shipped qty to the day in question.

Then another measure (CustomerCount) that does: iif([Cumulative Qty Shipped] > 0, 1, NULL) -- in order to make sure that a customer only gets a tally of 1, not any higher.

Now, what I'm concerned about is the bleeding that happens. ie:

If looking at 2 customers, the execution path of AS is to sum all qty shipped to the current date section, then evaluate the logic: iif([Cumulative Qty Shipped] > 0, 1, NULL)

This would also happen if looking at 1 customer and multiple products. All of the products' qty would be summed, then evaluated for > 0..

Is there a way to sort of turn this around so that the calculation is first done on strictly customer and product, then doing the iif([Cumulative Qty Shipped] > 0, 1, NULL) check?

Plus another behavior needed is 'distinct' customer count. If looking at the all products level, and a customer owns 3 products, they should still only be counted once. If looking the members of Products, then the customer could be tallied once per product.

|||

I tried another script scope, hoping that it would work to cover the needs discussed, but it did not.

This is what I'd used. Hopefully it will spark some ideas from others!

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

this = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

END SCOPE;

and differently:

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

Measures.CustomerCount = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

END SCOPE;

Designing a fact table to hold customer-product ownership by day

If I'm designing a fact table to hold customer-product ownership by day, can one somehow get by with just storing customer purchase dates and return dates, rather than a record for each day the customer owns a product?

I need to make sure that if a customer bought a product in Jan and returned it 1 month later, but then bought it again in April - that they show up as having that item in
Jan and April - to date (but excluding Feb, March)

One thing that will be tough is the potential for this to happen several times with the same customer, but still showing all gaps of not having product. If a date dimension is not used, the I'd really like to take the customer's current status for a product - is it possible to do this?

Also, another question, speaking of customer counts and rollup
once it's determined that a customer has a certain product on x day (count of 1) and doesn't have a product on a different day (count of -1),
how would one determine that for the level above product, -subcategory-, that there were 5 customers that had Accessories?

I see that adventure works uses distinct count to make up customer count, but what about when returns are entered into the picture?
I'm thinking of doing them as -1, but maybe there's a better way to handle them and ultimately, the 'distinct customer count' rollup to higher levels?

I realize this post contains a lot of questions - if I can get at least one answered for starters, that would be awesome.

Hi,

if you contact me by mail and send me more detail and if it's possible some data I can try to figure out a possible solution. (If you can wait few days)

francesco.dechirico(AT)fastwebnet.it

|||

Still looking into this problem - months later

With a fact table like:

Customer Product Date Qty

1 1 1/1/2005 1

1 1 3/2/2005 -1

1 2 4/1/2005 1

I'd like to have a query show that a customer is active on dates 1/1/2005-3/1/2005, and from 4/1/2005 on... this should also be done by distinct count of customer ID, but down to product if possible.

Here is an example mdx statement I composed, but the results aren't exactly what I'm seeking. Yes, it adds up Invoice Qty, but I was hoping to 'create' the date rows in between.

with member x

as DistinctCount(sum({null:[Transaction Date].[Calendar Date].CurrentMember}, [Invoice Qty]), Customer.Customer.[Customer ID].CurrentMember)

member y as [Transaction Date].[Calendar Date].CurrentMember.UniqueName

select {[invoice sales],[Measures].[Invoice Qty], x, y} on 0,

[Transaction Date].[Calendar Month].[Calendar Month] on 1

from sales

where (customer.customer.&[1])

|||

Any ideas on this one?

Mainly, I'm looking for something to create the non existant date rows in the output. Whether it be a script SCOPE or calculated member..

|||

Ok, I got a lot further after reading the ideas in this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1205862&SiteID=1

I've defined a measure to be Cumulative Qty Shipped - summing up all shipped qty to the day in question.

Then another measure (CustomerCount) that does: iif([Cumulative Qty Shipped] > 0, 1, NULL) -- in order to make sure that a customer only gets a tally of 1, not any higher.

Now, what I'm concerned about is the bleeding that happens. ie:

If looking at 2 customers, the execution path of AS is to sum all qty shipped to the current date section, then evaluate the logic: iif([Cumulative Qty Shipped] > 0, 1, NULL)

This would also happen if looking at 1 customer and multiple products. All of the products' qty would be summed, then evaluated for > 0..

Is there a way to sort of turn this around so that the calculation is first done on strictly customer and product, then doing the iif([Cumulative Qty Shipped] > 0, 1, NULL) check?

Plus another behavior needed is 'distinct' customer count. If looking at the all products level, and a customer owns 3 products, they should still only be counted once. If looking the members of Products, then the customer could be tallied once per product.

|||

I tried another script scope, hoping that it would work to cover the needs discussed, but it did not.

This is what I'd used. Hopefully it will spark some ideas from others!

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

this = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

END SCOPE;

and differently:

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

Measures.CustomerCount = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

END SCOPE;

Designing a fact table to hold customer-product ownership by day

If I'm designing a fact table to hold customer-product ownership by day, can one somehow get by with just storing customer purchase dates and return dates, rather than a record for each day the customer owns a product?

I need to make sure that if a customer bought a product in Jan and returned it 1 month later, but then bought it again in April - that they show up as having that item in
Jan and April - to date (but excluding Feb, March)

One thing that will be tough is the potential for this to happen several times with the same customer, but still showing all gaps of not having product. If a date dimension is not used, the I'd really like to take the customer's current status for a product - is it possible to do this?

Also, another question, speaking of customer counts and rollup
once it's determined that a customer has a certain product on x day (count of 1) and doesn't have a product on a different day (count of -1),
how would one determine that for the level above product, -subcategory-, that there were 5 customers that had Accessories?

I see that adventure works uses distinct count to make up customer count, but what about when returns are entered into the picture?
I'm thinking of doing them as -1, but maybe there's a better way to handle them and ultimately, the 'distinct customer count' rollup to higher levels?

I realize this post contains a lot of questions - if I can get at least one answered for starters, that would be awesome.

Hi,

if you contact me by mail and send me more detail and if it's possible some data I can try to figure out a possible solution. (If you can wait few days)

francesco.dechirico(AT)fastwebnet.it

|||

Still looking into this problem - months later

With a fact table like:

Customer Product Date Qty

1 1 1/1/2005 1

1 1 3/2/2005 -1

1 2 4/1/2005 1

I'd like to have a query show that a customer is active on dates 1/1/2005-3/1/2005, and from 4/1/2005 on... this should also be done by distinct count of customer ID, but down to product if possible.

Here is an example mdx statement I composed, but the results aren't exactly what I'm seeking. Yes, it adds up Invoice Qty, but I was hoping to 'create' the date rows in between.

with member x

as DistinctCount(sum({null:[Transaction Date].[Calendar Date].CurrentMember}, [Invoice Qty]), Customer.Customer.[Customer ID].CurrentMember)

member y as [Transaction Date].[Calendar Date].CurrentMember.UniqueName

select {[invoice sales],[Measures].[Invoice Qty], x, y} on 0,

[Transaction Date].[Calendar Month].[Calendar Month] on 1

from sales

where (customer.customer.&[1])

|||

Any ideas on this one?

Mainly, I'm looking for something to create the non existant date rows in the output. Whether it be a script SCOPE or calculated member..

|||

Ok, I got a lot further after reading the ideas in this post

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1205862&SiteID=1

I've defined a measure to be Cumulative Qty Shipped - summing up all shipped qty to the day in question.

Then another measure (CustomerCount) that does: iif([Cumulative Qty Shipped] > 0, 1, NULL) -- in order to make sure that a customer only gets a tally of 1, not any higher.

Now, what I'm concerned about is the bleeding that happens. ie:

If looking at 2 customers, the execution path of AS is to sum all qty shipped to the current date section, then evaluate the logic: iif([Cumulative Qty Shipped] > 0, 1, NULL)

This would also happen if looking at 1 customer and multiple products. All of the products' qty would be summed, then evaluated for > 0..

Is there a way to sort of turn this around so that the calculation is first done on strictly customer and product, then doing the iif([Cumulative Qty Shipped] > 0, 1, NULL) check?

Plus another behavior needed is 'distinct' customer count. If looking at the all products level, and a customer owns 3 products, they should still only be counted once. If looking the members of Products, then the customer could be tallied once per product.

|||

I tried another script scope, hoping that it would work to cover the needs discussed, but it did not.

This is what I'd used. Hopefully it will spark some ideas from others!

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

this = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

END SCOPE;

and differently:

SCOPE (Measures.CustomerCount, [Product].[Product].[Product], [Customer].[Customer].[Customer]);

Measures.CustomerCount = IIF([Cumulative Qty Shipped] > 0, 1, NULL);

END SCOPE;

design question - so many columns....

Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?
On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.
|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.
|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -
|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.
|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.

design question - so many columns....

Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > Got a situation where our main, core data in our main tables consist
> > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > several "sets" of booleans. Example - health risk factors....high
> > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > say. The user can choose none, all, or any combo in between in these
> > sets of booleans. Well each of those fundamentally is just a bit
> > column, with a zero or one, attached to the record concerning the
> > individual person/event record. Well what if I have 8 or 9 "sets" of
> > these boolean questions? This results in 150-200+ columns in my
> > table. I know sql can handle up to 1024, and these data really belong
> > on this record with this individual person/event.
> > I just wanted to see what others thought from the design perspective.
> > Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
> > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > Got a situation where our main, core data in our main tables consist
> > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > several "sets" of booleans. Example - health risk factors....high
> > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > say. The user can choose none, all, or any combo in between in these
> > > sets of booleans. Well each of those fundamentally is just a bit
> > > column, with a zero or one, attached to the record concerning the
> > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > these boolean questions? This results in 150-200+ columns in my
> > > table. I know sql can handle up to 1024, and these data really belong
> > > on this record with this individual person/event.
> > > I just wanted to see what others thought from the design perspective.
> > > Any suggestions?
> > That's quite easy. Let's say you have one column called 'Risk Factor'
> > that can take 20 possible values. You can store values like
> > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > would prefer this. Another solution is you can have a varchar(20) with
> > values like 10001000 etc where you set a bit for particular risk
> > factor. You have to know the ordinal position for a particular risk
> > and if that bit is set or not.
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>> Got a situation where our main, core data in our main tables consist
>> of 15-20 normal columns (dates, integers, varchar, etc.) and then
>> several "sets" of booleans. Example - health risk factors....high
>> blood pressure, diabetes, depression....up to 20 risk factors, let's
>> say. The user can choose none, all, or any combo in between in these
>> sets of booleans. Well each of those fundamentally is just a bit
>> column, with a zero or one, attached to the record concerning the
>> individual person/event record. Well what if I have 8 or 9 "sets" of
>> these boolean questions? This results in 150-200+ columns in my
>> table. I know sql can handle up to 1024, and these data really belong
>> on this record with this individual person/event.
>> I just wanted to see what others thought from the design perspective.
>> Any suggestions?
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers => 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
> > On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> > > On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> > > > Got a situation where our main, core data in our main tables consist
> > > > of 15-20 normal columns (dates, integers, varchar, etc.) and then
> > > > several "sets" of booleans. Example - health risk factors....high
> > > > blood pressure, diabetes, depression....up to 20 risk factors, let's
> > > > say. The user can choose none, all, or any combo in between in these
> > > > sets of booleans. Well each of those fundamentally is just a bit
> > > > column, with a zero or one, attached to the record concerning the
> > > > individual person/event record. Well what if I have 8 or 9 "sets" of
> > > > these boolean questions? This results in 150-200+ columns in my
> > > > table. I know sql can handle up to 1024, and these data really belong
> > > > on this record with this individual person/event.
> > > > I just wanted to see what others thought from the design perspective.
> > > > Any suggestions?
> > > That's quite easy. Let's say you have one column called 'Risk Factor'
> > > that can take 20 possible values. You can store values like
> > > 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> > > would prefer this. Another solution is you can have a varchar(20) with
> > > values like 10001000 etc where you set a bit for particular risk
> > > factor. You have to know the ordinal position for a particular risk
> > > and if that bit is set or not.
> > and of course if you want to be relational then you have a row for
> > each 'Risk Factor'. So instead of growing the table horizontally you
> > grow them vertically. So if a person has 'Diabetes' and 'High Blood
> > Pressure' there are 2 rows for that person. Then you can group by a
> > particular 'Risk Factor'. For example, find all patients where 'Risk
> > Factor' is 'Diabetes' and sum how much they spent etc on medication
> > etc.- Hide quoted text -
> > - Show quoted text -- Hide quoted text -
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.

design question - so many columns....

Got a situation where our main, core data in our main tables consist
of 15-20 normal columns (dates, integers, varchar, etc.) and then
several "sets" of booleans. Example - health risk factors....high
blood pressure, diabetes, depression....up to 20 risk factors, let's
say. The user can choose none, all, or any combo in between in these
sets of booleans. Well each of those fundamentally is just a bit
column, with a zero or one, attached to the record concerning the
individual person/event record. Well what if I have 8 or 9 "sets" of
these boolean questions? This results in 150-200+ columns in my
table. I know sql can handle up to 1024, and these data really belong
on this record with this individual person/event.
I just wanted to see what others thought from the design perspective.
Any suggestions?On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> Got a situation where our main, core data in our main tables consist
> of 15-20 normal columns (dates, integers, varchar, etc.) and then
> several "sets" of booleans. Example - health risk factors....high
> blood pressure, diabetes, depression....up to 20 risk factors, let's
> say. The user can choose none, all, or any combo in between in these
> sets of booleans. Well each of those fundamentally is just a bit
> column, with a zero or one, attached to the record concerning the
> individual person/event record. Well what if I have 8 or 9 "sets" of
> these boolean questions? This results in 150-200+ columns in my
> table. I know sql can handle up to 1024, and these data really belong
> on this record with this individual person/event.
> I just wanted to see what others thought from the design perspective.
> Any suggestions?
That's quite easy. Let's say you have one column called 'Risk Factor'
that can take 20 possible values. You can store values like
'Diabetes,High Blood Pressure,Depression'. This is one solution and I
would prefer this. Another solution is you can have a varchar(20) with
values like 10001000 etc where you set a bit for particular risk
factor. You have to know the ordinal position for a particular risk
and if that bit is set or not.|||On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
>
>
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
and of course if you want to be relational then you have a row for
each 'Risk Factor'. So instead of growing the table horizontally you
grow them vertically. So if a person has 'Diabetes' and 'High Blood
Pressure' there are 2 rows for that person. Then you can group by a
particular 'Risk Factor'. For example, find all patients where 'Risk
Factor' is 'Diabetes' and sum how much they spent etc on medication
etc.|||I've seen that approach used in some other places in reading online,
mainly with surveys & questionarres. But some issues I have with it
are....
1 - The relational form of a tall skinny table, with each answer as a
row seems good if you may not have data for every question/element, so
you save space on the questions that aren't answered. We will likely
have data for each element. Which means we'll have billions of
rows.....per year. The size will start to become an issue after
several years.
2 - Storage....At first glance the wide tables seem like they'll be
larger. But a lot of the columns are bits, which are optimized for
storage, so if I have a wide table with 32 bit columns, they'll only
take up 4 bytes of storage. But if I store the same answers (1 or 0)
in another table as rows in a generic catch-all varchar column, and
then have two IDs of int or bigint tying them back to the question &
respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
250+ bytes just for the one respondent. And there's way more than 32
- probably 100 or so.
3 - Data integrity. If everything has its own column in a wide table,
then you can make sure that a bit is a bit, and a date is a datetime,
and an integer is an int. But if everything is put in one generic
column, then you give up a little bit of ground on the data integrity,
and then I'm depending on the ETL process, or the developer to
validate all data types.
As crappy as it sounds, the wide table looks better to me in this
situation. Unless someone out here can talk me out of it.
On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
> On Jun 15, 10:09 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
> and of course if you want to be relational then you have a row for
> each 'Risk Factor'. So instead of growing the table horizontally you
> grow them vertically. So if a person has 'Diabetes' and 'High Blood
> Pressure' there are 2 rows for that person. Then you can group by a
> particular 'Risk Factor'. For example, find all patients where 'Risk
> Factor' is 'Diabetes' and sum how much they spent etc on medication
> etc.- Hide quoted text -
> - Show quoted text -|||SB wrote:
> On Jun 15, 1:05 am, CoreyB <unc27...@.yahoo.com> wrote:
> That's quite easy. Let's say you have one column called 'Risk Factor'
> that can take 20 possible values. You can store values like
> 'Diabetes,High Blood Pressure,Depression'. This is one solution and I
> would prefer this. Another solution is you can have a varchar(20) with
> values like 10001000 etc where you set a bit for particular risk
> factor. You have to know the ordinal position for a particular risk
> and if that bit is set or not.
>
Well, bit fields create a searching an filtering nightmares (ask me how
I know :-).)
I don't believe there is a bullet-proof solution.
I would consider even a de-normalized 1-to-1 relationship architecture
option.
For example, having a Patient table (PatientID + personal data), then
RiskFactors table (PatientID + 20+ risk factor bit fields), etc. Since
in most cases you search either for a singe patient or for group of
patients that meet certain criteria, massive multi-table joins will not
be required too often.|||On Jun 15, 6:41 pm, CoreyB <unc27...@.yahoo.com> wrote:
> I've seen that approach used in some other places in reading online,
> mainly with surveys & questionarres. But some issues I have with it
> are....
> 1 - The relational form of a tall skinny table, with each answer as a
> row seems good if you may not have data for every question/element, so
> you save space on the questions that aren't answered. We will likely
> have data for each element. Which means we'll have billions of
> rows.....per year. The size will start to become an issue after
> several years.
> 2 - Storage....At first glance the wide tables seem like they'll be
> larger. But a lot of the columns are bits, which are optimized for
> storage, so if I have a wide table with 32 bit columns, they'll only
> take up 4 bytes of storage. But if I store the same answers (1 or 0)
> in another table as rows in a generic catch-all varchar column, and
> then have two IDs of int or bigint tying them back to the question &
> respondent, that's already 8 or 9 bytes per row minimum * 32 answers =
> 250+ bytes just for the one respondent. And there's way more than 32
> - probably 100 or so.
> 3 - Data integrity. If everything has its own column in a wide table,
> then you can make sure that a bit is a bit, and a date is a datetime,
> and an integer is an int. But if everything is put in one generic
> column, then you give up a little bit of ground on the data integrity,
> and then I'm depending on the ETL process, or the developer to
> validate all data types.
> As crappy as it sounds, the wide table looks better to me in this
> situation. Unless someone out here can talk me out of it.
> On Jun 15, 12:29 am, SB <othell...@.yahoo.com> wrote:
>
>
>
>
>
>
>
>
> - Show quoted text -
I think the first solution should work where you store the values as
coma (or any other delimiter) separated values. For example for
certain risk factor, for a customer values can be: diabetes,high blood
pressure.

Design of 'age' ( dimension ? )

my reports are run over a period of time of certain dates, let s say :

Period 1) DateStartPeriod1 - DateEndPeridod1

Period 2) DateStartPeriod1 - DateEndPeridod1

Period n ) .....

The subject is born in DBORN, so when i need to rollup to the age at the end of the period 1 it is going to be

DateEndPeriod1 - DBORN and the same when the second date is used, i.e. DateEndPeridod1 - DBORN.

QUESTION : How do i model this in Analasys services ? In other words how do i explain AS that when i use Period 1

on the columns i want the age DateEndPeriod1 - DBORN on the rows

I tried to use calculated memebrs AgePeriod1 and AgePeriod2 ... something like

MEMBER AgendPeriod1 as 'DateEndPeriod1 - DBORN'

but it does not seem to work.

I m pretty sure that the answer is straightforward but because i m new to OLAP i just can t think of it.

Thanks

Lui

One possible solution: define a calculated measure.

Formula could be the following:

'[Period].CurrentMember.MemberValue - [Subject].[LastLevel].CurrentMember.Properties("DBORN")'

When designing your cube, make sure ValueColumn for the [Period] hierarchy points to a relational column that has 'date' type, and actually contains the date for the end of each period.

I am also assuming you will have a hierarchy named [Subject], and each member at the last level of this hierarchy has a member property called DBORN, that is also of type date.