MDX Query Optimization for Calculated Measures using .Lag()

Question

I have the following MDX query that calculates members and then selects those members for a specified date range:

WITH
MEMBER [Measures].[Prior] as ([Date Post Transaction].[Calendar Month Period].CurrentMember.Lag(12),[Measures].[Unique Patients]) 
MEMBER [Measures].[Current] as [Measures].[Unique Patients] 
SELECT {[Measures].[Current],[Measures].[Prior]} 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME , HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(AddCalculatedMembers({DrilldownLevel({[Date Post Transaction].[Calendar Month Period].Children})})) 
DIMENSION PROPERTIES PARENT_UNIQUE_NAME , HIERARCHY_UNIQUE_NAME ON ROWS FROM (SELECT ({[Date Post Transaction].[Calendar Month Period].&[201207],[Date Post Transaction].[Calendar Month Period].&[201208],[Date Post Transaction].[Calendar Month Period].&[201209],[Date Post Transaction].[Calendar Month Period].&[201210],[Date Post Transaction].[Calendar Month Period].&[201211],[Date Post Transaction].[Calendar Month Period].&[201212],[Date Post Transaction].[Calendar Month Period].&[201301],[Date Post Transaction].[Calendar Month Period].&[201302],[Date Post Transaction].[Calendar Month Period].&[201303],[Date Post Transaction].[Calendar Month Period].&[201304],[Date Post Transaction].[Calendar Month Period].&[201305],[Date Post Transaction].[Calendar Month Period].&[201306]}) 
ON COLUMNS FROM [cube]) 
WHERE ([Provider Billing].[Specialty Mgma Pcps].&[Cardiology: Inv-Intvl])

Basically, this query is asking for all patients within Cardiology for the current date range and prior date range by month.

This query takes 8-9 seconds to run, which is crazy. I'm using Analysis Services to run and test query times and have not ran into other tools to help optimize MDX. So my first question is, does anyone know of such tools to help optimize MDX?

My main question is what method or structure should I be using in the query to help process the results faster? I originally was not using the .lag() function and instead running a query that included each month for current and prior. I did see improved result times from this switch.

I have several reports that use this same format, just different metrics (MEASURES) and so you can imagine the load times we're getting with queries that each run 5+ seconds.

FrankPl FrankPl · Accepted Answer · 2013-09-25T18:23:18

Generally, MDX optimization is not easy, there is not much information what causes which performance impact and few information like SQL plans, and what is there is not documented well. What is there, however:

Microsoft has a performance optimization guide: http://technet.microsoft.com/en-us/library/dd542635(v=sql.100).aspx
The former chief developer of MDX and the Analysis Services query engine had a blog: http://sqlblog.com/blogs/mosha/
He also developed a tool that you can use to run a query and see some more or less useful query statistics which can still be downloaded: http://www.sqlbi.com/tools/mdx-studio

Looking at your query, I would not be sure where the performance killer is. Maybe something in the calculation script which is not visible in the query itself. What I would try first would possibly be - depending on the structure of your [Date Post Transaction].[Calendar Month Period] hierarchy - just using something like

[Date Post Transaction].[Calendar Month Period].&[201207] : [Date Post Transaction].[Calendar Month Period].&[201306]

for the rows axis, and omitting the subselect altogether, i. e. use just

 FROM [cube]

as the whole FROM clause without any subselect. But I would doubt this will dramatically decrease run time.

MDX Query Optimization for Calculated Measures using .Lag()

1 Answers