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.

 

 

 

 

 

 

 

Attribute Vs Properties in Excel 2007 and OWC 11

Following post demonstrates about how an attribute can be used to display the data on same column or separate column(s). Especially those who are coming from RDBMS background would feel the Screenshot #1 shown below is wrong. So wanted to show how an attribute is differentiated with Property


When attributes are added to Rows or Column Axis : When attributes are added Row or Column Labels then it would cross join the attributes and display the data in one column.



When attributes are used as Properties:

Excel 2007 : When attributes are displayed properties, then for each leaf member it would display the corresponding attribute. This output is preferred in multiple situations.




Properties are enabled as shown in below image.



OWC 11 ( Bids Cube browser) OWC acts little bit differently in OWC 11. Properties are enabled using the similar command dialog box shown in above image but output is totally different. Following image would display the Account properties.

How to avoid Long running MDX Requests

I wanted to write a small post about ‘Timeout’, which really helped me practically to manage long running MDX requests.

Example of connection string with Timeout

Provider=MSOLAP.3; Data Source= MySSASServerName; Initial Catalog= MySSASDBName; User ID= windowsusername; Password= windowsuserpwd;Timeout=120;

In the above example, requested MDX would be allowed to run in 2 minutes. After 2 minutes request will be cancelled.

This is very important feature and every developer should consider using. Usually OLAP clients provide the flexibility to design the reports by authorized users and/or select the multiple dimension members on rows or columns or pages. So based on selection made by end user, sometimes prepared MDX could be big or complex and requires lot of resources & time to execute the same. So when it is a multi user environment (especially concurrency is high) and end user has got flexibility to design the reports & run the same, then there will be ‘Competency for the resources’ and one request could bring down the server, which will drop everyone’s requests, which nobody wants to happen.

Practically I encountered Production server (power full) going down and cancelling all the request because of Single MDX request. It was horrible situation to know that Server went down.

In order to not to bring down the Production server, I used ‘Timeout’ property in connection string and given flexibility to change this number by Department and by U. Since I’ve given flexibility (Managing Timeout time in a separate table) to specify in the connection string, Reporting Administrator can manage control the Timeout time depending the importance of User or Department usage.

Example: Finance department is working on ‘Month end closure’ or they got to present the Financial statements to their BOD, so obviously their work gets the highest priority. Similarly CEO is performing What-If Analysis and that is important step for defining ‘Strategy’ for entire company. In these type of situations, you do not want to drop their MDX request unless it is too long request to execute

The above mentioned situation is not unique. I would think it is very common requirement and you never want single MDX request bringing the server down. So it is always safer to use Timeout property and manage the Server uptime well.

Clearly there is no hard number of Timeout time that I can recommend because depending the user base, concurrency, complexity of the reports, flexibility that is given to the end user, one should determine how much this number (Time) should be.

Finally, do not miss to handle the error in your OLAP client when Timeout drops the request. Always it is good practice to display the message to end user about why the requesst is dropped. It is also very important to integrate some mechanism to communicate Reporting Administrator about how many requests were dropped. In my case, I generate an email to Reporting Administrator with user & report details when a request is cancelled.

Hope this helps!!

How to measure ROI on BI applications

I’m excited to see Boris Evelson’s excellent Blog post on measuring ROI on Business Intelligence application. He has listed out the key data points (Usage Tracking, Efficiency & Effectiveness) to collect/track in an organization and also mentioned about how these data points will be helpful for defining action items and planning future BI applications. I’ve seen some BI implementations with little or no insight on ROI and continuing the BI implementation endlessly. Though it is difficult to collect (automatically & painless) the data points that he mentioned are really informative.
In SaaS environment , Self service BI & Response time are really key items and measuring the same would really helpful in assessing the SaaS based BI applications.
Here is the URL of the post http://blogs.forrester.com/boris_evelson/10-04-05-bi_bi

Debugging MDX with MDX Studio

MDX Studio by Mosha Pasumansky is a gift to the SSAS developers.
MDX Studio is a tool which helps users of Microsoft Analysis Services to analyze complex MDX expressions, monitor performance characteristics of MDX queries and get insight into how MDX interacts with other UDM features such as attribute relationships. MDX Studio provides unique visualization representation of MDX expression and allows the user to interact with different stages of MDX execution.

Interesting feature is Debugging, Video is available on You tube (http://www.youtube.com/watch?v=MNUJkHYwOKI

In addition to this, many times it requires a tool to format lengthy MDX for better understanding. Fthis purpose, In addition to Desktop version of MDX studio, online MDX studio is also available with some basic functionality. This is available at http://mdx.mosha.com/default.aspx . I use this a lot when I get to analyze the MDX.

Host Analytics Occupied the Microsoft's spot in Forrester Wave report 2009

This is an interesting news on Microsoft PPS.

As we all know, Microsoft entered into Business Performance Solutions or Corporate Performance solutions or Enter prise solutions space by introducing PPS (Performance Point Server) a few years ago. In the last Forrester Wave report (2007 ) report, Microsoft was also considered as a leader in this space.

But in the latest report published, Microsoft is not listed in the report and Host Analytics (only SaaS CPM Vendor (www.hostanalytics.com) took Microsoft's spot on this report.

FYI - Forrester Wave report is produced once in 2 years and it profiles major software product vendors world wide in each solution vertical.