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;