Tuesday, October 23, 2012

How to define a semi-additive MDX measure in cubing services (conditional distinct and then average based on date dimension)

Business requirement

Get number of distinct FALLOUT_ACCOUNT_KEY only if END_FALLOUT_DATE_KEY = 0 for each day, and then do the average based on it

Implemented in Cube services:

1)      Add calculated measure in the model as [Distinct Opened Fallout count 0]


Aggregation: COUNT

2)      Add MDX calculated measure in the model as
Expression: AVG([Report Date].[Day].MEMBERS,[Measures].[Distinct Opened Fallout count 0])

3)      Add these two measures in cube

Implemented in Cognos DMR model:

Step 1: define Distinct Opened Fallout count 0 as
else (null)
Step 2:

Another tip  to address MDX measure definition issue
In design studio, when adding a simple MDX Calculated Measure, such as define Current Difference measure as [Measures].[MeasueA] - [Measures].[MeasureB],  there is no validation problem. However, you can’t deploy cube.  When bowering members, there is error as
Some validation errors are detected.
Encountered a parsing error in "[Measures].Current Difference".: Not implemented
The solution is to make all the MDX calculated measure with [] as [Current Difference]   

No comments:

Post a Comment