Wednesday, February 26, 2014

Analysis Services (SSAS): Calculating running totals for a given period using MDX

Calculating running totals against a numeric is not an easy task with both relational databases using T-SQL and multidimensional databases using MDX. In a way, calculation on relational databases is little bit easier using new window functions than using multi-dimensional queries. But it does not mean that the calculation using MDX is much more complex and difficult to write. However it depends on the familiarity. Comparatively we do not write MDX statements as much as we write T-SQLs. That is the reason for tagging “complex” on MDX, that is the reason for spending much time even on a simple query than the time spend with T-SQL for similar implementations.

I had to write a similar query today for calculating running totals mixing with YTD and previous year values. As usual I searched for best practices, there were many posts on this based on either calendar year or financial year but could not find a specific one for calculating running totals for a given period. Wrote it, and thought to share it. Here is the MDX, it is written on AdventureWorksDW. You may find it interesting and helpful.

  1. WITH MEMBER [Measures].[Running Total]
  2. AS
  3. (
  4.     AGGREGATE([Date].[Calendar].[Month].&[2006]&[10]
  5.             :[Date].[Calendar].CurrentMember
  6.             , [Measures].[Sales Amount])
  7. )
  8. SELECT
  9.     {[Measures].[Sales Amount]
  10.     , [Measures].[Running Total]} ON 0
  11.     , [Date].[Calendar].[Month].Members ON 1
  12. FROM (SELECT {[Date].[Calendar].[Month].&[2006]&[10]
  13.             :[Date].[Calendar].[Month].&[2007]&[11]} ON 0
  14.         FROM [Adventure Works])

image

6 comments:

sreehari katageri said...

Hi ,
Why do we need to create partitions only on fact table. why not on the dimension tables. Could you please explain.

sreehari katageri said...

Hi ,
Why do we need to create partitions only on fact table. why not on the dimension tables. Could you please explain.

Iulia said...

Awesome post! Thanks a ton!

Iulia

Yogeshwar NR said...

hello Dinesh,
awesome your explanation.
without hardcoded year & month how we get start & end year running values.
could you please suggest me how to full fill these scenario.

Yogeshwar NR said...

Hello dinesh,
Awesome your post,
with hardcoded the year & month how we can achieve the scenario.
could you please explain to me.

Dinesh Priyankara said...

Hi Yogeshwar,

Thanks.

Since it is a user-defined range (unless it is something like financial year), it is not possible to get the first child to calculate the range. The way I have done this is, making the statement as a dynamic query. Example, if you need to get the this query executed by SSRS accepting parameters for date range, then you can construct the MDX statement as an expression using parameter values. Is that what you need? Let me know, if not, let's try to find another way.

Regards

Dinesh