Distinct customer count across time dimension in Transformer

[satyagorantla]

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!
                 

[satyagorantla]

I am still stuck with this issue and any suggestions you have are much appreciated. Thanks!