Ten MDX Calculations For Your Cube (part 2)

In this post I’ll conclude what I began in an earlier blog post covering ten of the most common cube calculations implemented for some of my clients. You can read the first blog post in this 2 part series here, which covers the first five calculations (in no particular order). As before, these calculations will be written against the Adventure Works 2008 R2 sample AS project.

5. Percent of a Parent

This is very common calculation that I’ve seen used countless times to determine what percentage of a measurement makes up the total measurement for a larger body. In this example, I’m calculating what percent of Internet Sales for a product make up the total Internet Sales for the product’s Sub Category.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percent of Parent]
AS
Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Internet Sales Amount]
)
Then Null

// Test for current coordinate being on the (All) member.
When [Product].[Product Categories].CurrentMember.Level Is
[Product].[Product Categories].[(All)]
Then 1

Else ( [Product].[Product Categories].CurrentMember,
[Measures].[Internet Sales Amount]  )
/
( [Product].[Product Categories].CurrentMember.Parent,
[Measures].[Internet Sales Amount] )

End
,
FORMAT_STRING = “Percent”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

4. Rolling Average Calculation

Another common calculation used is one that calculates an average over the period of time, such as the monthly average of the past 12 months.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Monthly Average Over Year]
AS

    Avg
(
[Date].[Month Name].CurrentMember.Lag(11) :
[Date].[Month Name].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

3. Percentage Growth From Previous Period

If you’re wanting to measure the percentage of growth of a measure from a previous period, this is the calculation to use. A couple things to keep in mind: This calculation is hierarchy specific and is currently set up to measure the percentage of growth from the previous calendar year.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Percentage Growth From Previous Period]
AS Case
// Test for current coordinate being on (All) member.
When [Date].[Calendar].CurrentMember.Level Is
[Date].[Calendar].[(All)]

Then “NA”

// Test to avoid division by zero.
When IsEmpty
(
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
Then Null

Else (
( [Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount])

(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
)
/
(
ParallelPeriod
(
[Date].[Calendar].[Calendar Year],
1,
[Date].[Calendar].CurrentMember
),
[Measures].[Internet Sales Amount]
)
End,
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

2. Period to Date Average

This calculation is very similar to #4 up above but instead of giving you the average Internet Sales for the past 12 months, the calculation will return the average Internet Sales for the current period. For example, if you view this calculation at the day level, you will see the average daily Internet Sales for the current month.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Average Over Current Period]
AS

Avg
(
[Date].[Calendar].CurrentMember.FirstSibling :
[Date].[Calendar].CurrentMember,
[Measures].[Internet Sales Amount]
)
,
FORMAT_STRING = “Currency”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;

1. Top Products Percent of Sales

The last calculation I have to show you utilizes a named set to return to the top 10 best selling products. Many clients like to monitor the sales of their best selling products this way.

CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Internet Products]
AS TopCount
(
(

         [Product].[Product].Children
)
,10
,[Measures].[Internet sales amount]
), DISPLAY_FOLDER = ‘Sets’  ;
   

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount]),
FORMAT_STRING = “CURRENCY”,
VISIBLE = 1 ,  ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’  ;

You can also add a calculated measure to show the percentage of sales of those 10 best selling products.

CREATE MEMBER CURRENTCUBE.[Measures].[Top 10 Products Percent of Internet Sales]
AS SUM([Top 10 Internet Products],[Measures].[Internet Sales Amount])/
[Measures].[Internet Sales Amount],
FORMAT_STRING = “#,##0.00 %;-#,##0.00 %”,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’;

If you have any other calculations which you think should be on this list and would love to share them, please send them my way. I’d love to see ‘em!

If you don’t consider yourself an MDX expert or are looking to eliminate some of the calculation development time, I’d highly suggest checking out BI xPress. BI xPress has a great feature built in that allows you to quickly and efficiently add calculations and named sets to your cube, all without the knowledge of MDX. Heck, most of the calculations in this blog post were written in about 10 seconds with the BI xPress Calculation Builder.

To open the BI xPress Calculation Builder, open your SSAS project in BIDS, navigate to the Calculations tab and click the BI xPress Calculation Builder icon.

2-Click-BI-xPress-icon_thumb

This will open the MDX Calculation Builder Wizard. Select the Set template you’d like to use. I’m selecting the Top 10 Count template.

image

The first step is to select the attribute that you would like returned by the set. As before, I’m selecting the Product attribute of the Product dimension.

image

Lastly, select the measure.

image

And its really that easy.

image

To download the free trial of BI xPress, head over to PragmaticWorks.com and check out BI xPress. You won’t regret it after you see how much time it will save you with your SSAS and SSIS development.

Advertisements

4 thoughts on “Ten MDX Calculations For Your Cube (part 2)”

  1. Hi Rayan,

    I have problem in my calculated member. Whenever this member involve in calculation or query it take large time to execute. I am trying to narrow down execution time.
    I have to remove IIF condition from the members and start using scope instead.
    CREATE Member CurrentCube.[Measures].[AvgAmount]
    as IIF( ISLeaf([Customer].[ParentCustomer].currentmember),
    [Measures].[Value],
    (SUM([CCube^Customer].[ParentCustomer].CURRENTMEMBER.CHILDREN) /
    COUNT([Customer].[ParentCustomer].CURRENTMEMBER.CHILDREN))) ,
    Format_String = “#.0000000;-#.0000000;0;0”,
    Non_Empty_Behavior =[Measures].[Amout];

    I have created hierarchy of customer which is [ParentCustomer] here. I want to see avg amount of all the children under the parent customer but when I am looking child level which does not have any children in it should only show the [Measures].[Amout].

    Thanks in advance

    Regards,
    Sam

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s