Here’s a SSAS Multidimensional MDX tip that I picked up at the PASS Summit back in 2008 at Mosha’s excellent “MDX Deep Dive” precon (incidentally the slides and supporting material are still available here, although a lot of the material is out of date). It’s regarding total-to-date calculations, ie calculations where you are doing a running total from the very first date you have data for up to the current date. The standard way of writing these calculations is something like this:
[sourcecode language='text' padlinenumbers='true' highlight='3,4,5'] WITH MEMBER MEASURES.[TTD Sales] AS SUM( NULL:[Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount]) SELECT [Customer].[Country].[Country].MEMBERS ON 0, NON EMPTY [Date].[Calendar].[Date].MEMBERS * [Product].[Product].[Product].MEMBERS ON 1 FROM [Adventure Works] WHERE(MEASURES.[TTD Sales]) [/sourcecode]
This query runs in around 19.2 seconds on my laptop on a cold cache. However if you rewrite it like this:
[sourcecode language='text' highlight='3,4,5,8,9,10,11,12,13'] WITH MEMBER MEASURES.[PTTD SALES] AS SUM( NULL:[Date].[Calendar].CURRENTMEMBER.PARENT.PREVMEMBER, [Measures].[Internet Sales Amount]) MEMBER MEASURES.[TTD Sales] AS MEASURES.[PTTD SALES] + SUM( [Date].[Calendar].CURRENTMEMBER.FIRSTSIBLING: [Date].[Calendar].CURRENTMEMBER, [Measures].[Internet Sales Amount]) SELECT [Customer].[Country].[Country].MEMBERS ON 0, NON EMPTY [Date].[Calendar].[Date].MEMBERS * [Product].[Product].[Product].MEMBERS ON 1 FROM [Adventure Works] WHERE(MEASURES.[TTD Sales]) [/sourcecode]
…it runs slightly faster: around 16.1 seconds on a cold cache on my laptop. Of course this is a very big query, and on most normal queries the difference in performance would be much less significant, but it could still be useful. In fact it’s very similar to the kind of tricks people used to optimise the performance of YTD calculations back in the days of SSAS 2000 – the subject of my second-ever blog post from December 2004! The idea here is that instead of summing up a large set of dates, the calculation sums up all the dates in the current month and then all the months from the beginning of time up to and including the previous full month. For YTD and most other something-to-date calculations trick like this are no longer needed, and indeed are counter-productive and will make your calculations slower. However it seems that for total-to-date calculations they can still help performance.
It’s a really small cube, usually customers have fat fact tables for their sales. I’ve given up MDXing TTD calculations and prefer to use the cumulative tables with last child aggregation in sake of performance. Also, it’s more accurate if we need last member for slow-changing dimensions (for example, show the debt balance for the last manager of the current customer).
My last attempt was the VisualTotals trick (it re-aggregates All member for limited set, something like a sub-cube). It works pretty fast, because yo don’t use SUM or any other heavy functions. Unfortunately, you are limited to use the set once per report. So, it won’t work for your example anyway:
(
VisualTotals(
{[Date].[Calendar].[All] + {NULL:[Date].[Calendar].CurrentMember}}
).item(0), –Get the re-aggregated ALL member
[Measures].[Internet Sales Amount]
)
I suspect you can write a C# stored procedure which can return a virtual member (instead of a set) like the VisualTotals function does, but I’m not sure if it’s possible at all.
I did try this method on my cube. It performed well with most of the dimensions, but it is still giving me performance issues when used with the lowest level dimension. I have premium information(Insurance data) in my cube and MDX calculated member won’t work with Policy dimension. It will keep spinning and freeze after a few seconds. I have around 1 billion records in my fact table and around 8 million policy records in the dimension. The lowest level granularity of the Fact table is at the Policy level, I have partitions and aggregations in place which are helping with the performance of the queries other than the policy dimension.
Please let me know if you have any tricks.
It’s hard to say what to do – you need to understand why the query is slow (Storage Engine or Formula Engine or both?) and then tune appropriately. The material here: https://blog.crossjoin.co.uk/2010/03/23/query-performance-tuning-chapter-from-%E2%80%9Cexpert-cube-development%E2%80%9D-available-online/ is a good place to start