I refer to the original problem posted in this thread by @"nimonic-JavaNet"
Running Count Distinct Calculated Measure
It's archived and i'm not able to reply/add to it. So new post
************
I think there is a lot of posts/discussions on how to achieve Distinct Count Measure (as such).
E.g:
how to do a count distinct value in awm
From the last reply in this thread by @"Nasar-Oracle" :
Take a look at these 3 posts regarding 'Count Distinct' in OLAP
https://forums.oracle.com/thread/2436996
https://forums.oracle.com/thread/2228449
https://forums.oracle.com/message/9531513
and lots more by searching on Count Distinct Measure in OLAP Discussions.
***************
The problem relating to getting Distinct Count Measure as a Running Total is quite interesting. The default Period To Date calculated measure is incorrect since while the original Count Distinct is valid for each day taken individually, their sum or running total or sum is not necessarily correct for the period to date time range taken altogether (as a whole ... duplicates across days counted earlier are not filtered out in latter days).
However this can perhaps be achieved if we build a new cube with new measure and load a variation of the original fact table data by repeating the base data for each day onto each day that the record would be valid for /apply to in a month level Period To Date kind of dimension sense. We would need a new cube, measure for each distinct level of time that we want this functionality. Maybe some OLAP DML optimization is possible to do this via single new additional cube for all levels - Month, Quarter, Year etc.
Fact Table
=========
Active Date: Customer ID:
01-JAN-2015 111
01-JAN-2015 112
----
02-JAN-2015 111
02-JAN-2015 112
----
03-JAN-2015 113
----
04-JAN-2015 111
04-JAN-2015 112
04-JAN-2015 112
04-JAN-2015 113
----
...
Current Cube - Activity using same strategy as Distinct Count (MAX for all dimensions, SUM for CUSTOMER ... CUSTOMER last dimension used for aggregation )
Current Measure - Activity Distinct Counts ... Count of Distinct Customers at Day level, Month level etc.
will give you:
...
20-APR-2015 15 ... 15 unique customers on 20-APR-2015
21-APR-2015 25 ... 25 unique customers on 20-APR-2015
...
Also
...
Mar-2015 2500 ... 2500 unique customers for March 2015
Apr-2015 2300 ... 2300 unique customers for March 2015
...
etc.
Create alternate Fact View (relational) by joining Fact table with Time Dimension and a period to date helper dimension table (1-many for each day) to get the following ... sql to create this view is not readily available.. can be done in many ways, i think.
Period to Date Fact View:
==================
Active Date: Customer ID:
01-JAN-2015 111
01-JAN-2015 112
----
02-JAN-2015 111
02-JAN-2015 112
02-JAN-2015 111
02-JAN-2015 112
----
03-JAN-2015 111
03-JAN-2015 112
03-JAN-2015 111
03-JAN-2015 112
03-JAN-2015 113
----
04-JAN-2015 111
04-JAN-2015 112
04-JAN-2015 111
04-JAN-2015 112
04-JAN-2015 113
04-JAN-2015 111
04-JAN-2015 112
04-JAN-2015 112
04-JAN-2015 113
----
...
Design new cube - MTD_Activity using same strategy as Distinct Count (MAX for all dimensions, SUM for CUSTOMER ... CUSTOMER last dimension used for aggregation )
Create new measure - Activity Period to Date Distinct Counts ... Running Count of Distinct Customers at Day level (calculated from start of the Month).
will give you:
...
20-APR-2015 1250 ... 1250 unique customers in Apr 2015 upto 20-APR-2015
21-APR-2015 1310 ... 1310 unique customers in Apr 2015 upto 21-APR-2015 ... i.e. 50 new, unique active customers on 21-Apr-2015 (who were not previously active in April 2015)
...
HTH
Shankar