Speed up Running Total MDX calculated measure?

Question

I'm using the follow mdx to keep a running total of the Period Balance measure in my cube:

SUM({[Due Date].[Date].CurrentMember.Level.Item(0):[Due Date].[Date].CurrentMember}, [Measures].[Period Balance])

It works great, however it's really slow as the amount of data displayed increases. I can't use a MTD or YTD because the users may be analyzing data that overlaps years. Any way I can speed this up?

Thanks in advance.

Meff Meff · Accepted Answer · 2010-11-11T15:09:17

I take it you've seen this? http://sqlblog.com/blogs/mosha/archive/2006/11/17/performance-of-running-sum-calculations-in-sp2.aspx

Failing that, there is another sample which uses the technique of taking the parent's prior totals and the parent's current child from first sibling to current - So you'd sum the prior months and then this month's days - That'll only work if you have a date hierarchy though:

http://www.ssas-info.com/analysis-services-articles/62-design/367-inventory-management-calculations-in-sql-server-analysis-services-2005-by-richard-tkachuk

I think the pictures there explain it better, its the "Summing Increments" section.

Are you query-logging and doing usage-based aggregations?

Speed up Running Total MDX calculated measure?

1 Answers