SQL Server Performance

Slow running MDX through OLE DB Data Source

Discussion in 'SQL Server 2005 Reporting Services' started by akramer, Jan 19, 2011.

  1. akramer New Member

    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 + ")"
  2. satya Moderator

    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 .

Share This Page