Running Total With Multiple Hierarchies

1
votes

I have a date dimension that has multiple hierarchies (as most do). I have a number of measures that are calculated as a running total, as shown in the example below.

AGGREGATE([Date].[Calendar].[Date].Members.Item(0):ClosingPeriod([Date].[Calendar].[Date], [Date].[Calendar].CurrentMember), [Measures].[Number Of Contracts])

My problem is that the running total only works for the Calendar hierarchy (which breaks everything down by year, month, and then day). If I instead use the Weeks hiearchy (which breaks things down by individual week), the calculation doesn't work, it just shows the same number for every week.

Is there a performant way in MDX to make the running total work for multiple hierarchies? Everything I've attempted so far has been quite slow.

1
ssasmdx

1 Answers

2
votes

One possible solution is below. By using the EXISTING keyword, one can get a list of all the Date members that exist in the current context, and then use those dates to define a range over which to aggregate the data. Based on my initial testing, the performance is similar to the calculation in the question.

AGGREGATE(NULL:TAIL(EXISTING [Date].[Date].[Date].Members).Item(0), [Measures].[Number Of Contracts])

Another, possibly more performant, solution is described at http://www.purplefrogsystems.com/blog/2010/08/mdx-calculated-member-spanning-multiple-date-dimensions/. It solves the problem by calculating ranges for all applicable hierarchies and then crossjoining those hierarchies together.

AGGREGATE({ NULL:[Date].[Calendar].CurrentMember } * { NULL:[Date].[Week].CurrentMember })

The only drawback of this solution is that it can become verbose if you have many hierarchies that do not have attribute relationships between eachother.