Hello Everyone,
I have a requirement to build a cube with distinct customer count that is dynamic across time dimension. I am using FM to generate IQDs as datasources for Transformer. Here is the sample data
Cust ID TransactionID TransactionDate
-------- ------------- -----------------
101 1A 01-01-2007
102 1B 01-31-2007
101 1C 02-20-2007
101 2A 01-31-2008
103 2B 02-01-2008
I have built a time dimension on Transaction Date column. And I need to show the distinct cust. count by this dimension. So Overall cust. count should be 3 and drill down on time dimension by year should show year 2 for 2007 and for 2 2008.
I cannot use running count and set the minimum of running count to 1 as the subset on which the running count needs to be applied changes with the level on the time dimension.
Also, category count is ruled out as I have over 50 million customers. External rollups are not feasible as there a quite a few dimensions to rollup.
Any inputs you have are greatly appreciated!!!
Thanks in advance!
I am still stuck with this issue and any suggestions you have are much appreciated. Thanks!