locked
MDX - Calculate at lowest grain before rollup Problem RRS feed

  • Question

  • I am using MDX for some reporting. The idea is that we want all the customer ship to and SKU combinations that were sold in a particular period that were also sold in the same period the previous year. The period will usually be quarter but it could be month or week. There are also some parameters that show up in the WHERE statements. For each customer ship to and SKU combination there are calculations that we want to be performed at that level before they roll up to a taxonomy level. A SKU rolls up to a Taxonomy 4 which rolls up to a Taxonomy 3 which rolls up to a Taxonomy 2 which rolls up to a taxonomy 1. 

    We are using a subcube to get the part of the cube that meet the parameter requirements and for customer ship to - SKU combinations that have sales in the same quarter this year and last year. Using that subcube I then roll up to Taxonomy 1 & 2. Here is the MDX:

    With member measures.[LY Shipped Qty] as
    (ParallelPeriod([Date - Reporting].[Reporting].[Year],1,[Date - Reporting].[Reporting].currentmember),[Measures].[Shipped Qty])

    member measures.[LY_GP_Dev_and_Shltr] as [Measures].[LY GP Dev Amt]+[Measures].[LY GP Shelter Amt]
    member measures.[GP_Dev_and_Shltr] as [Measures].[GP Dev Amt]+[Measures].[GP Shelter Amt]
    member measures.[Cost_Change] as [Measures].[Shipped Qty]*(([Measures].[LY Sales Amt]-[Measures].[LY_GP_Dev_and_Shltr])/measures.[LY Shipped Qty])
    member measures.[Price_Change] as [Measures].[Shipped Qty]*([Measures].[LY Sales Amt]/measures.[LY Shipped Qty])
    member measures.[Cost_Vol_Var] as (measures.[LY Shipped Qty]-[Measures].[Shipped Qty])*(([Measures].[LY Sales Amt]-Measures.[LY_GP_Dev_and_Shltr])/measures.[LY Shipped Qty])
    member measures.[Cost_rate_var] as ((([Measures].[LY Sales Amt]-measures.[LY_GP_Dev_and_Shltr])/measures.[LY Shipped Qty])-(([Measures].[Sales Amt]-measures.[GP_Dev_and_Shltr])/[Measures].[Shipped Qty]))*[Measures].[Shipped Qty]
    member measures.[Price_Vol_Var] as ([Measures].[Shipped Qty]-measures.[LY Shipped Qty])*([Measures].[LY Sales Amt]/[measures].[LY Shipped Qty])
    member measures.[Price_Rate_Var] as (([Measures].[Sales Amt]/measures.[LY Shipped Qty]) - ([Measures].[LY Sales Amt]/[measures].[LY Shipped Qty])) * [Measures].[Shipped Qty]

    select {[Shipped Qty],[Measures].[Sales Amt],measures.[GP_Dev_and_Shltr],[Measures].[LY Sales Amt],measures.[LY Shipped Qty],measures.[LY_GP_Dev_and_Shltr]
    ,measures.[Cost_Change],measures.[Price_Change],measures.[Cost_Vol_Var],measures.[Cost_rate_var]
    ,measures.[Price_Vol_Var],measures.[Price_Rate_Var]}
    on 0,
    [Taxonomy].[Level 1].children*[Taxonomy].[Level 2].children on 1
    from (
    select
    NONEMPTY(
    NONEMPTY([Current Customer].[Ship To Name].Children*[SKU - Known].[USN].children*[Taxonomy].[Taxonomy].[Level 1].&[1], [Measures].[Sales Amt]),[Measures].[LY Sales Amt]
    )
    on 0
    from [MY CUBE]
    Where ([Date - Reporting].[Reporting].[Week].&[FY2012]&[12]&[51],[Brand Partner].[Brand Partner].&[15],[SKU - Known].[Exclusive Label].[All]
    ,[SKU - Known].[Import SKU].[All],[SKU - Brand - Known].[In Catalog].[All],[SKU - Brand - Known].[Call For Price].[All]
    ,[Current Customer].[National Account].[All],[Current Customer].[Group Name].[All])
    )
    Where ([Date - Reporting].[Reporting].[Week].&[FY2012]&[12]&[51],[Brand Partner].[Brand Partner].&[15],[SKU - Known].[Exclusive Label].[All]
    ,[SKU - Known].[Import SKU].[All],[SKU - Brand - Known].[In Catalog].[All],[SKU - Brand - Known].[Call For Price].[All]
    ,[Current Customer].[National Account].[All],[Current Customer].[Group Name].[All])
    ;

    You can see in the calculated members that some include division and multiplication. The problem we are facing is we want the calculation to happen at each Customer Ship To - SKU combination and then aggregate up. Instead it is summing up each part of the calculation then doing the calculation. So for the price change, instead of calculating price change for each Customer Ship To - SKU combination then aggregating up it is summing the shipped quantity, summing the LY sales amount and the LY shipped quantity then doing the multiplication and division. 

    We tried opening up the SSAS database "hot" and tried scripting several iterations of SCOPE in the cube such as:

    CREATE MEMBER CURRENTCUBE.[Measures].[Price_Change]
    AS [Measures].[Shipped Qty]*([Measures].[LY Sales Amt]/measures.[LY Shipped Qty]),
    FORMAT_STRING = "$#,##0.00;$-#,##0.00",
    NON_EMPTY_BEHAVIOR = { [Shipped Qty] },
    VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Sales' ;

    SCOPE ([Measures].[Price_Change],[Taxonomy].[Level 1].children,[Taxonomy].[Level 2].children);
    [Measures].[Price_Change]= sum(
    [Current Customer].[Ship To Name].Children*[SKU - Known].[USN].children*[Taxonomy].[Level 1].children*[Taxonomy].[Level 2].children
    , [Measures].[Price_Change]
    );
    End Scope;

    After each we would Save All and then try the MDX at the top with the calculated member for price change commented out so we could use the new measure we added to the cube. In all cases where we got something other than Error or null the calculation was still summing up the parts then doing the calculation.

    Tuesday, September 10, 2013 2:48 PM

Answers

  • I found the solution. It wasn't coming and I had done a lot of Googling. With one day until my deadline I went for a walk and prayed "God you are gonna have to do this because I cannot figure it out." I came back to my desk, checked this entry and one on MSDN. Nearly 100 people had reviewed them but noone responded. I Googled again and the first entry that came up was the answer. Thanks Jesus!

    Chris Webb addresses this sort of problem very clearly. If you Google "aggregating the result of an mdx calculation using scoped assignments Chriss Webb" you should see his blog.

    The key involves using a real measure and setting the scope in the cube. 

    • Marked as answer by Elvis Long Wednesday, September 18, 2013 1:42 AM
    Thursday, September 12, 2013 2:08 PM

All replies

  • I found the solution. It wasn't coming and I had done a lot of Googling. With one day until my deadline I went for a walk and prayed "God you are gonna have to do this because I cannot figure it out." I came back to my desk, checked this entry and one on MSDN. Nearly 100 people had reviewed them but noone responded. I Googled again and the first entry that came up was the answer. Thanks Jesus!

    Chris Webb addresses this sort of problem very clearly. If you Google "aggregating the result of an mdx calculation using scoped assignments Chriss Webb" you should see his blog.

    The key involves using a real measure and setting the scope in the cube. 

    • Marked as answer by Elvis Long Wednesday, September 18, 2013 1:42 AM
    Thursday, September 12, 2013 2:08 PM
  • Hi Michael,

    Thanks for sharing this solution to solve this issue. It's benefit for other community members who have similar question.

    I attached Chriss's blog for reference, please see:
    Aggregating the Result of an MDX Calculation Using Scoped Assignments:
    http://cwebbbi.wordpress.com/2013/05/29/aggregating-the-result-of-an-mdx-calculation-using-scoped-assignments/

    Regards,


    Elvis Long
    TechNet Community Support

    Friday, September 13, 2013 6:38 AM