What if Analysis with Microsoft Analysis Services

In this post we will perform a what if analysis on a Bill of Material (BOM) using Analysis Services 2008. Consider the following BOM:

We would like to calculate the price of the Bike considering the quantity and price of the components. Lets say we use the following prices; 

- The Rim 3 euro

- Bolt, Nut and Cap 2 euro each

- Spokes, 1 euro. 

- Color, 2 euro. 

- Frame, 2 euro, and 2 pieces (made mistake entering data.. we can correct using what if analysis).

Lets go bottom up; 

Level 3; Tire Valve, would be Quantity 24 (1 x 2 x 2 x 2 x 3) x 2 euro = 48 euro. 

Level 2; 

• Rim; Quantity 4 (1 x 2 x 2) x 3 euro = 12 euro.
• Tire; Quantity 2 x 24 euro = 48 euro. 
• Spokes; Quantity 144 (1 x 2 x 72) x 1 euro = 144 euro.

Level 1; Frame Quantity 2 x 2 euro  = 4 euro + Wheel 204 euro (12 + 48 + 144) = 208 euro. 

I entered this data, incl. the mistake of the quantity of frames in the following spreadsheet:

In the video below I show how to build a cube that calculates the finished good price. It will also be possible to perform what if analysis. 

In the video below I correct the mistake of 2 frames, which should be one frame of course. 

The following MDX code is used in the solution. You can download the solution file here. 

    
        /*
        The CALCULATE command controls the aggregation of leaf cells in the cube.
        If the CALCULATE command is deleted or modified, the data within the cube is affected.
        You should edit this command only if you manually specify how the cube is aggregated.
        */
        CALCULATE;

        SCOPE([Measures].[Quantity]);  
         THIS=[Item].[Parent].CURRENTMEMBER.DATAMEMBER;  
        END SCOPE;  

        SCOPE([Measures].[Price]);  
         THIS=[Item].[Parent].CURRENTMEMBER.DATAMEMBER;  
        END SCOPE;  


        CREATE MEMBER CURRENTCUBE.[Measures].[Quantity2]
         AS 
        ( [Measures].[Quantity]);   


        CREATE MEMBER CURRENTCUBE.[Measures].[lvl]
         AS 
        [Parent].currentmember.level.name , 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   


        CREATE MEMBER CURRENTCUBE.[Measures].[Q1]
         AS 
        (ancestor([Parent].currentmember.parent.datamember,1), [Quantity2] ) , 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   

        CREATE MEMBER CURRENTCUBE.[Measures].[Q2]
         AS 
        (ancestor([Parent].currentmember.parent.datamember,2), [Quantity2] ) , 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   

        CREATE MEMBER CURRENTCUBE.[Measures].[Q3]
         AS 
        (ancestor([Parent].currentmember.parent.datamember,3), [Quantity2] ) , 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   

        CREATE MEMBER CURRENTCUBE.[Measures].[Q4]
         AS 
        (ancestor([Parent].currentmember.parent.datamember,4), [Quantity2] ) , 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   

        CREATE MEMBER CURRENTCUBE.[Measures].[Q5]
         AS 
        (ancestor([Parent].currentmember.parent.datamember,5), [Quantity2] ) , 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   

        CREATE MEMBER CURRENTCUBE.[Measures].[Q6]
         AS 
        (ancestor([Parent].currentmember.parent.datamember,6), [Quantity2] ) , 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   


        CREATE MEMBER CURRENTCUBE.[Measures].[RQ]
         AS 
        case    when lvl = '02' then [Quantity2]     
                when lvl = '03' then [Quantity2] * Q1    
                when lvl = '04' then [Quantity2] * Q1 * Q2    
                when lvl = '05' then [Quantity2] * Q1 * Q2 * Q3     
                when lvl = '06' then [Quantity2] * Q1 * Q2 * Q3 * Q4     
                when lvl = '07' then [Quantity2] * Q1 * Q2 * Q3 * Q4 * Q5 
            
        else 0
        end,
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';   

        CREATE MEMBER CURRENTCUBE.[Measures].[Item_Usage_Price]
         AS null, 
        VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = 'BOM';  

        Scope ([Measures].[Item_Usage_Price]);   
            Scope (Descendants([Parent].CurrentMember,,LEAVES));   
                this =  iif([Parent].CurrentMember.parent.Properties('Key') <> [Parent].CurrentMember.Properties('Key'), [Measures].[Price] * [RQ],0);   
            end scope;   
            scope([Parent].members); 
                this = sum(
                            Filter(
                            Descendants([Parent].CurrentMember,,LEAVES)
                            , [Parent].CurrentMember.parent.Properties('Key') <> [Parent].CurrentMember.Properties('Key')
                                   )
                            ,
                           ( [Measures].[Price] * [Measures].[RQ] )  
                        );   
            end scope;  
        end scope;