Counting days with mondrian and MDX

This is probably a problem mostly everyone dealing with daily averages pondered measures has to deal with, which is knowing the exact number of days accounted for some kind of calculation.
After some days (weeks) fiddling with this I came up with some solutions…

Approach #1 (aka the naive time consuming, not so efficient approach):

In my DW’s I usually keep a smart date key in the yyyymmdd format which is great for doing these types of calculations… so in a more naive approach you would think that setting a measure such as distinct-count with mondrian would work:

<Measure name="Number of Days" column="id_date" aggregator="distinct-count">
</Measure>

Ok so this is great, and it works… but mondrian will have to query your DW for every dimension level you use in your queries for a count(distinct id_date) operation (more on this here). So as you can guess, doing this for a large amount of data is highly unefficient and it can take a hell lot of aggregate tables as described in the mondrian docs.

Approach #2 (efficient but not so perfect solution):

So, after some time looking up the vast and immense internet world for a solution i found this post which had a possible solution for the problem. So in short, you pass the responsibility of the counting to the OLAP layer only defining a calculated member depending on the date dimension:

Count(Descendants([Date].CurrentMember, [Date].[Day]), EXCLUDEEMPTY)

This will count all the non-empty date dimension day level members from the cube. Great, exactly what I needed and no evil distinct-count measure needed. Of course this solution had to have a catch to give me some more trouble. This solution only works when you have all the day members in the query non-aggregated, so that means the following won’t work:

with member [Date].[Date Range] as 'Aggregate([Date].[2010].[11].[1]:[Date].[2010].[11].[5])'
select [Measure].[Number of Days] on columns
from [My Cube]
where [Date].[Date Range]

It will always return 1, because the [Date].CurrentMember will always be 1 day.

Approach #3 (Efficient and working solution. Elegant? Maybe not…):

After thinking this throught I figured out I had to make mondrian to always count a set of days even when they are not present unaggregated in the query. So why not set the date dimension as a cube itself?

<Cube name="date cube">
<Table name="dim_date">
</Table>
<DimensionUsage name="date" source="date" foreignKey="id_date">
</DimensionUsage>
<Measure name="Number of Days #3" column="id_date" aggregator="count">
</Measure>
</Cube>

Now the former query works because mondrian will query the date dimension filtering the sql query by he selected day level members and the count with return 5 as expected. But… there won’t be any “empty” members as this dummy cube will always have all the members (and it should because it’s your date dimension after all). How do you solve this? Combining approach #2 and #3!

Step 1: Build a virtualcube with your own cube, where you use the date cube also:

<VirtualCube name="My Combined Cube">
<CubeUsages>
<CubeUsage cubeName="date cube" ignoreUnrelatedDimensions="true"/> <!-- This will force mondrian to make measures of this cube valid in all non related dimensions -->
<CubeUsage cubeName="my cube"/>
</CubeUsages>
...
<VirtualCubeMeasure cubeName="date cube" name="[Measures].[Number of Days #3]"/>
...
</VirtualCube>

Step 2: Add the approach #2 measure as a calculated member:

<VirtualCube name="My Combined Cube">
...
<CalculatedMember name="Number of Days #2" formula="Count(Descendants([Date].CurrentMember, [Date].[Day]), EXCLUDEEMPTY)" dimension="Measures">
</CalculatedMember>
...
</VirtualCube>

Step 3: Add a final measure that combines both:

<VirtualCube name="My Combined Cube">
...
<CalculatedMember name="Number of Days" formula="IIf([Measures].[Number of Days #2] = 1,ValidMeasure([Measures].[Number of Days #3]),[Measures].[Number of Days #2])" dimension="Measures">
</CalculatedMember>
...
</VirtualCube>

So now combining both approaches all the day counting cases are covered (or so i think so…) 🙂 Have a better solution for this? I’m all eyes and ears!

EDIT: In step #3 the [Measures].[Number of Days #3] measure must be wrapped inside the ValidMeasure MDX function to work correctly.