MDX Variables to Boost OLAP reports performance significantly

Recently I figured out a way to make the OLAP reports significantly faster by avoiding MDX calculations at run time. I got tons of appreciation from my customers on OLAP reports performance.

The trick is using MDX Variables. Please do not try to Google it  this is what I named it.

Business Cases

It is very common to see OLAP reports with tons of Time MDX functions in multiple scenarios. Some of those scenarios include

1. Providing Out of the box OLAP reports offered by using SSRS or any other reporting client by utilizing Time calculations.
   
2. Custom OLAP client allowing report designers to provide the MDX to get a Financial Report
   

Typically MDX structure would be as mentioned below

WITH

MEMBER [Measures].[CurrentMonth] AS ([Date].[Calendar].Currentmember,[Measures].[Amount])

MEMBER [Measures].[CurrentMonthYTD] AS (Sum(YTD(),([Date].[Calendar].Currentmember,[Measures].[Amount])))

MEMBER [Measures].[PrevYearSameMonth] AS (ParallelPeriod ([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].Currentmember ),[Measures].[Amount])

MEMBER [Measures].[PrevYearSameMonthYTD] AS (Sum(YTD(),ParallelPeriod ([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].Currentmember ),[Measures].[Amount]))

SELECT

{ [Measures].[CurrentMonth],[Measures].[CurrentMonthYTD],[Measures].[PrevYearSameMonth],[Measures].[PrevYearSameMonthYTD]} ON COLUMNS,

{Descendants({[Account].[Accounts].&[47]},[Account].[Accounts].[Account Level 02],SELF_AND_AFTER)} ON ROWS

FROM [Adventure Works]

WHERE ([Date].[Calendar].[Month].[February 2004],[Scenario].[Scenario].&[1])

If you have observed Time functions in WITH MEMBER clause, It requires multiple evaluation to get the correct Time period based where Slicer coordinate. This can significantly degrade the performance. Especially if you are working in a environment where 0% aggregate exist, then it is going to impact the data retrieval time quite significantly.

Majority of the cases we know what the value is going to be evaluated for each measure here.

Solution

1. Manage the MDX variables at database level and provide a User interface for administrator to set the default values
   

Example:

Name : @CURRENT_MONTH

Value : [Date].[Calendar].[Month].[February 2004]

1. Construct your MDX in your report by utilizing MDX variables.
   

   
    

Of course you will have do this by utilizing SSRS reports or any other way depending on your OLAP client environment. ( I am not going to detail level of explanation about how exactly MDX is going to be in SSRS.)

WITH
MEMBER [Measures].[CurrentMonth] AS (@CURRENT_MONTH,[Measures].[Amount])

MEMBER [Measures].[CurrentMonthYTD] AS (Sum(YTD(),(@CURRENT_MONTH,[Measures].[Amount])))

MEMBER [Measures].[PrevYearSameMonth] AS (ParallelPeriod ([Date].[Calendar].[Calendar Year],1, @CURRENT_MONTH),[Measures].[Amount])

MEMBER [Measures].[PrevYearSameMonthYTD] AS (Sum(YTD(),ParallelPeriod ([Date].[Calendar].[Calendar Year],1, @CURRENT_MONTH),[Measures].[Amount]))

SELECT

{ [Measures].[CurrentMonth],[Measures].[CurrentMonthYTD],[Measures].[PrevYearSameMonth],[Measures].[PrevYearSameMonthYTD]} ON
COLUMNS,

{Descendants({[Account].[Accounts].&[47]},[Account].[Accounts].[Account Level 02],SELF_AND_AFTER)} ON
ROWS

FROM [Adventure Works] WHERE ([Date].[Calendar].[Month].[February 2004],[Scenario].[Scenario].&[1])

Try to run the report, you would notice that report runs faster. While evaluating this approach, please do not limit your thinking to just above example, try to think for thousands of report lines with so many columns in the above structure.

In my environment I am keeping a separate type of MDX variables and replacing the Parallel Periods as well. This separate type of variables first run independently (simple MDX), get the correct variables before running the report.

So MDX would look like:

WITH MEMBER [Measures].[CurrentMonth] AS (@CURRENT_MONTH,[Measures].[Amount])

MEMBER [Measures].[CurrentMonthYTD] AS (Sum(YTD(),(@CURRENT_MONTH,[Measures].[Amount])))

MEMBER [Measures].[PrevYearSameMonth] AS (@PrevYear_Of_CurMonth,[Measures].[Amount])

MEMBER [Measures].[PrevYearSameMonthYTD] AS (Sum(YTD(),(@PrevYear_Of_CurMonth,[Measures].[Amount]))

SELECT

{ [Measures].[CurrentMonth],[Measures].[CurrentMonthYTD],[Measures].[PrevYearSameMonth],[Measures].[PrevYearSameMonthYTD]} ON COLUMNS,

{Descendants({[Account].[Accounts].&[47]},[Account].[Accounts].[Account Level 02],SELF_AND_AFTER)} ON ROWS

FROM [Adventure Works] WHERE ([Date].[Calendar].[Month].[February 2004],[Scenario].[Scenario].&[1])

I applied this trick and trust me this is a GAME CHANGER in my environment. 

I was not patient enough to provide give details. But if you think this is going to be helpful, please email me at ashok_d_in@yahoo.com. I can explain you.