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

Howdy all. I have developers wanting to design a table that will hold not
only client info, but what type of program they are eligible for. I have
narrowed it down to 2 options, but was really looking for opinions. The
current design is like this:
Client Table:
ClientId
LName
FName
(Lots of other info that I will look at later)
IsCash(bit)
IsFoodStamps(bit)
IsMedical(bit)
But I'd like to split it out a bit so the various programs will be in
another table(s):
Option 1:
CLIENT TABLE:
ClientId
LName
FName
PROGRAMS TABLE:
ProgramId
ProgramName
CLIENT PROGRAM LOOKUP TABLE:
ClientId
ProgramId
Option 2:
CLIENT TABLE:
ClientId
LName
FName
CLIENT PROGRAM TABLE:
ClientId
Program (with a CHECK constraint so that only certain values can be entered)
Other ideas are welcomed as well.
TIA, ChrisR> Howdy all. I have developers wanting to design a table that will hold not
> only client info, but what type of program they are eligible for. I have
> narrowed it down to 2 options, but was really looking for opinions. The
> current design is like this:
> Client Table:
> ClientId
> LName
> FName
> (Lots of other info that I will look at later)
> IsCash(bit)
> IsFoodStamps(bit)
> IsMedical(bit)
I'm leaning toward your option 1, or this, with 1 Program = many Clients:
Program table:
ProgramID
Description
Client Table:
ClientID
LName
FName
ProgramID
I think your decision should be based on whether or not a client can belong
to one program at a time, or more than one at a time. So if I can belong to
both Cash and FoodStamps programs, this design won't work and you will want
to go with your Option 1.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei|||To answer your question "yes, a client can and will belong to more than one
program". But why wouldn't Option 2 work?
CLIENT TABLE:
ClientId
LName
FName
CLIENT PROGRAM TABLE:
ClientId
Program
Insert into Client(1234,'Blow','Joe')
Insert into ClientProgram(1234, 'FoodStamps')
Insert into ClientProgram(1234, 'Medical')
I know it's probably not the proper way to do things, but would reduce the
amount of joins. Plus if I had a CHECK constraint on the column "program",
only the right info could be entered.
Thoughts?
"Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
news:OrAyv0gSGHA.4608@.tk2msftngp13.phx.gbl...
not
> I'm leaning toward your option 1, or this, with 1 Program = many Clients:
> Program table:
> ProgramID
> Description
> Client Table:
> ClientID
> LName
> FName
> ProgramID
> I think your decision should be based on whether or not a client can
belong
> to one program at a time, or more than one at a time. So if I can belong
to
> both Cash and FoodStamps programs, this design won't work and you will
want
> to go with your Option 1.
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>|||ChrisR wrote:
> To answer your question "yes, a client can and will belong to more than on
e
> program". But why wouldn't Option 2 work?
> CLIENT TABLE:
> ClientId
> LName
> FName
> CLIENT PROGRAM TABLE:
> ClientId
> Program
>
> Insert into Client(1234,'Blow','Joe')
> Insert into ClientProgram(1234, 'FoodStamps')
> Insert into ClientProgram(1234, 'Medical')
> I know it's probably not the proper way to do things, but would reduce the
> amount of joins. Plus if I had a CHECK constraint on the column "program",
> only the right info could be entered.
> Thoughts?
>
Two things to consider. Under option 2 the users won't be able to add
new Programs - that would require a schema change to alter the CHECK
constraint. Also, if you want to change the program description for any
reason then you'll have to change it on every row, not just once.
Perhaps option 2 will also require more storage - it appears so from
your sample data.
In general I'd say that Option 1 is better for "descriptive" text
(because business users are more likely to want changes to
descriptions), whereas Option 2 is better for sets of codes because
they tend to change less frequently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:eMm9AAhSGHA.4740@.TK2MSFTNGP14.phx.gbl...
> To answer your question "yes, a client can and will belong to more than
> one
> program". But why wouldn't Option 2 work?
It would work.
Option one (clients, programs, and a third table to match the two) is the
standard method for representing a many to many relationship.
Option two would work if the only information about the program that you
care about is the program name. If you plan on entering any other details
about the program, then this isn't good enough.|||I hadn't thought of either of the points you guys vrought up. Thanks a lot
and have a great wend.
"ChrisR" <ChrisR@.noEmail.com> wrote in message
news:eMm9AAhSGHA.4740@.TK2MSFTNGP14.phx.gbl...
> To answer your question "yes, a client can and will belong to more than
one
> program". But why wouldn't Option 2 work?
> CLIENT TABLE:
> ClientId
> LName
> FName
> CLIENT PROGRAM TABLE:
> ClientId
> Program
>
> Insert into Client(1234,'Blow','Joe')
> Insert into ClientProgram(1234, 'FoodStamps')
> Insert into ClientProgram(1234, 'Medical')
> I know it's probably not the proper way to do things, but would reduce the
> amount of joins. Plus if I had a CHECK constraint on the column "program",
> only the right info could be entered.
> Thoughts?
>
> "Mike Labosh" <mlabosh_at_hotmail.com> wrote in message
> news:OrAyv0gSGHA.4608@.tk2msftngp13.phx.gbl...
> not
have
The
Clients:
> belong
belong
> to
> want
>