Slow running MDX through OLE DB Data Source | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Slow running MDX through OLE DB Data Source

I have this MDX that is extremely slow running when ALL level is selected for the business group dimension and the region dimension at the same time. It’s pretty complicated. As shown in version 1 it runs in 22 seconds in mgt studio query window, longer in RS because many of the values are parameters that SSRS has to sort out. I tried running version 2 and I got impatient waiting for it to return in mgt studio…it’s just taking too long for my users. I am not an MDX guru, so there may be something obvious I am missing. Version 3 is what it looks like in SSRS.
Version 1:WITH Member [measures].[Month_Sales] as ‘([Measures].[Sales],[Date Calcs].&[60])’
Member [Measures].[Month_Sales_LY] as ‘([Measures].[Sales],[Date Calcs].&[62])’
Member [Measures].[Month_Sales_MonthVarPct] as ‘([Measures].[Sales],[Date Calcs].&[66])’
Member [Measures].[Year_Sales] as ‘([Measures].[Sales],[Date Calcs].&[40])’
Member [Measures].[Year_Sales_LY] as ‘([Measures].[Sales],[Date Calcs].&[42])’
Member [Measures].[Year_Sales_YearVarPct] as ‘([Measures].[Sales],[Date Calcs].&[46])’
MEMBER [Measures].[Monthly Pace] as ‘([Measures].[Monthly Pace Sales], [Date Calcs].&[60])’
MEMBER [Measures].[Yearly Pace] as ‘([Measures].[Yearly Pace Sales], [Date Calcs].&[60])’
Member [Measures].[Month_Budget] as ‘([Measures].[PM Budget Sales],[Date Calcs].&[70])’
Member [Measures].[Year_Budget] as ‘([Measures].[PM Budget Sales],[Date Calcs].&[74])’
Member [Measures].[Month_Var_Pct_Booked] as ‘iif(COALESCEEMPTY([Measures].[Month_Budget],0)=0,NULL,([Measures].[Monthly Pace Sales]-[Measures].[Month_Budget])/[Measures].[Month_Budget])’
Member [Measures].[Year_Var_Pct_Booked] as ‘iif(COALESCEEMPTY([Measures].[Year_Budget],0)=0,NULL,([Measures].[Yearly Pace Sales]-[Measures].[Year_Budget])/[Measures].[Year_Budget])’
Select {[Measures].[Month_Sales],[Measures].[Month_Sales_LY],[Measures].[Month_Sales_MonthVarPct],
[Measures].[Monthly Pace],[Measures].[Year_Sales],[Measures].[Year_Sales_LY],[Measures].[Year_Sales_YearVarPct],
[Measures].[Yearly Pace],[Measures].[Month_Budget],[Measures].[Year_Budget],[Measures].[Month_Var_Pct_Booked],[Measures].[Year_Var_Pct_Booked]}
on columns,
non empty ([Region].[Region].children,[Sales Rep].[Sales Rep].children,[SKU].[BusinessGroup].[BusinessGroup].members) on rows
FROM (SELECT [Customer Sold To].[Customer Group].[Customer Type].& ON COLUMNS
FROM (SELECT {[Region].[Region].[All]} ON COLUMNS
FROM (SELECT {[SKU].[BusinessGroup].&[10]} ON COLUMNS
FROM [Sales])))
WHERE ([Date].[Time Standard].[Day].&[20110118])
Version2:WITH Member [measures].[Month_Sales] as ‘([Measures].[Sales],[Date Calcs].&[60])’
Member [Measures].[Month_Sales_LY] as ‘([Measures].[Sales],[Date Calcs].&[62])’
Member [Measures].[Month_Sales_MonthVarPct] as ‘([Measures].[Sales],[Date Calcs].&[66])’
Member [Measures].[Year_Sales] as ‘([Measures].[Sales],[Date Calcs].&[40])’
Member [Measures].[Year_Sales_LY] as ‘([Measures].[Sales],[Date Calcs].&[42])’
Member [Measures].[Year_Sales_YearVarPct] as ‘([Measures].[Sales],[Date Calcs].&[46])’
MEMBER [Measures].[Monthly Pace] as ‘([Measures].[Monthly Pace Sales], [Date Calcs].&[60])’
MEMBER [Measures].[Yearly Pace] as ‘([Measures].[Yearly Pace Sales], [Date Calcs].&[60])’
Member [Measures].[Month_Budget] as ‘([Measures].[PM Budget Sales],[Date Calcs].&[70])’
Member [Measures].[Year_Budget] as ‘([Measures].[PM Budget Sales],[Date Calcs].&[74])’
Member [Measures].[Month_Var_Pct_Booked] as ‘iif(COALESCEEMPTY([Measures].[Month_Budget],0)=0,NULL,([Measures].[Monthly Pace Sales]-[Measures].[Month_Budget])/[Measures].[Month_Budget])’
Member [Measures].[Year_Var_Pct_Booked] as ‘iif(COALESCEEMPTY([Measures].[Year_Budget],0)=0,NULL,([Measures].[Yearly Pace Sales]-[Measures].[Year_Budget])/[Measures].[Year_Budget])’
Select {[Measures].[Month_Sales],[Measures].[Month_Sales_LY],[Measures].[Month_Sales_MonthVarPct],
[Measures].[Monthly Pace],[Measures].[Year_Sales],[Measures].[Year_Sales_LY],[Measures].[Year_Sales_YearVarPct],
[Measures].[Yearly Pace],[Measures].[Month_Budget],[Measures].[Year_Budget],[Measures].[Month_Var_Pct_Booked],[Measures].[Year_Var_Pct_Booked]}
on columns,
non empty ([Region].[Region].children,[Sales Rep].[Sales Rep].children,[SKU].[BusinessGroup].[BusinessGroup].members) on rows
FROM (SELECT [Customer Sold To].[Customer Group].[Customer Type].& ON COLUMNS
FROM (SELECT {[Region].[Region].[All]} ON COLUMNS
FROM (SELECT {[SKU].[BusinessGroup].&[ALL]} ON COLUMNS
FROM [Sales])))
WHERE ([Date].[Time Standard].[Day].&[20110118])
Version3: (As it is in SSRS)="WITH Member [measures].[Month_Sales] as ‘([Measures].[" + Parameters!SalesType.Value + "],[Date Calcs].&[60])’
Member [Measures].[Month_Sales_LY] as ‘([Measures].[" + Parameters!SalesType.
Value + "],[Date Calcs].&[62])’Member [Measures].[Month_Sales_MonthVarPct] as ‘([Measures].[" + Parameters!SalesType.Value + "],[Date Calcs].&[66])’
Member [Measures].[Year_Sales] as ‘([Measures].[" + Parameters!SalesType.
Value + "],[Date Calcs].&[40])’Member [Measures].[Year_Sales_LY] as ‘([Measures].[" + Parameters!SalesType.Value + "],[Date Calcs].&[42])’
Member [Measures].[Year_Sales_YearVarPct] as ‘([Measures].[" + Parameters!SalesType.
Value + "],[Date Calcs].&[46])’MEMBER [Measures].[Monthly Pace] as ‘([Measures].[Monthly Pace " + Parameters!SalesType.Value + "], [Date Calcs].&[60])’MEMBER [Measures].[Yearly Pace] as ‘([Measures].[Yearly Pace " + Parameters!SalesType.Value + "], [Date Calcs].&[60])’
Member [Measures].[Month_Budget] as ‘([Measures].[PM Budget Sales],[Date Calcs].&[70])’
Member [Measures].[Year_Budget] as ‘([Measures].[PM Budget Sales],[Date Calcs].&[74])’
Member [Measures].[Month_Var_Pct_Booked] as ‘iif(COALESCEEMPTY([Measures].[Month_Budget],0)=0,NULL,([Measures].[Monthly Pace " + Parameters!SalesType.Value + "]-[Measures].[Month_Budget])/[Measures].[Month_Budget])’Member [Measures].[Year_Var_Pct_Booked] as ‘iif(COALESCEEMPTY([Measures].[Year_Budget],0)=0,NULL,([Measures].[Yearly Pace " + Parameters!SalesType.Value + "]-[Measures].[Year_Budget])/[Measures].[Year_Budget])’
Select {[Measures].[Month_Sales],[Measures].[Month_Sales_LY],[Measures].[Month_Sales_MonthVarPct],
[Measures].[Monthly Pace],[Measures].[Year_Sales],[Measures].[Year_Sales_LY],[Measures].[Year_Sales_YearVarPct],
[Measures].[Yearly Pace],[Measures].[Month_Budget],[Measures].[Year_Budget],[Measures].[Month_Var_Pct_Booked],[Measures].[Year_Var_Pct_Booked]}
on columns,
non empty ([SKU].[BusinessGroup].[BusinessGroup].members,[Sales Rep].[Sales Rep].children,[Region].[Region].children) on rows
FROM (SELECT " + Parameters!pCustomerGroup.value + " ON COLUMNS FROM (SELECT {" +
JOIN(Parameters!pRegions.value,", ") + "} ON COLUMNS FROM (SELECT {" + JOIN(Parameters!pBusinessGroups.value,", ") + "} ON COLUMNS FROM [Sales])))
WHERE (" + Parameters!pFormatDate.
Value + ")"

Welcome to the forums.
It will be ideal to know the SQL server configuration too, also confirm what is FORMAT you are using inside the connection string.
Also if you are running this withinSSIS package then ensure to check DelayValidation property to TRUE on the data flow task that avoid any validation of query. Further make sure to set ValidateExternalMetadata property to false on the OLE DB Source component .

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |