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.
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.
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.
Lastly, select the measure.
And its really that easy.
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.
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’ ;
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';
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’ ;
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’ ;
6. Count Leaf Members of a Hierarchy
I’ve seen this kind of calculation used a lot in combinations with other measures and/or calculation. This calculation counts the Leaves, which are products, of the Product Categories hierarchy.
CREATE
MEMBER CurrentCube.[Measures].[Product Count] AS
Count
(
Descendants
(
[Product].[Product Categories].CurrentMember,
,LEAVES
)
)
,FORMAT_STRING = “#,##0.00;-#,##0.00″
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales';
There’s not much to this calculation. First we use the Descendants function with the Leaves function as the third argument to get all the Leaf members of the Product Categories hierarchy. Then we use the Count function to count those members.
While these calculations are not extremely complex, MDX can be a bit overwhelming. A very helpful tool that I’ve found useful, even as someone experienced with MDX, is the MDX Calculation Builder included with BI xPress. Adding calculations, like those seen above, is very easy and only take a few click with the wizard.
To open the MDX Calculation Builder, click the icon seen on the Calculations tab of your Cube Designer in BIDS.
After you click the Calculation Builder icon, the Calculation Builder wizard will open. The first thing you’ll need to do is select a calculation. At these step, we can also choose to create a named set, which the wizard will help us do.
After selecting the calculation, you’ll be taken through a couple more steps, such as selecting the Measure you wish to perform the calculation with or specifying which attribute to use as the Year attribute for the calculation.
You’ll also have the opportunity to apply conditional formatting to your measure values, which is a nice touch the end users like to see.
Finally, we’ll give our measure a name, select the measure group we’d like the measure to be a part of, select the formatting, and preview the calculation that was written by the tool.
Then we’ll click Finish. The calculation has been added to our calculation script within our cube.
BI xPress has made adding calculations to your cube simply and fast and I take advantage of the tool anytime I’m implementing calculations into my client’s cube. Head to PragmaticWorks.com to check out BI xPress and to check out some of the great training videos hosted there covering the in’s and out’s of MDX.
7. Percent of Total
More often then not, my clients want to be able to calculate the percent of a total amount for a hierarchy. In this example, I’m once again leveraging the Case statement.
CREATE
MEMBER CurrentCube.[Measures].[Percent of Internet Product Sales] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
(
[Product].[Product Categories]
,[Measures].[Internet Sales Amount]
)
/
(
[Product].[Product Categories].[All]
,[Measures].[Internet Sales Amount]
)
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales';
To adapt this calculation to your cube, just replace [Product].[Product Categories] with your dimension and hierarchy.
8. Profit Margin
Another popular calculation for obvious reasons is the calculation for profit margin. Basically all you need to understand for this calculation is the logic of a Case statement. We use the Case statement to check for a zero value in the denominator.
CREATE
MEMBER CurrentCube.[Measures].[Internet Profit Margin] AS
CASE
WHEN
IsEmpty([Measures].[Internet Sales Amount])
THEN NULL
ELSE
([Measures].[Internet Sales Amount]
-
[Measures].[Internet Total Product Cost])
/
[Measures].[Internet Sales Amount]
END
,FORMAT_STRING = “Percent”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales';
9. Period to Period Growth
This is also a pretty common calculation I find and implement for my clients. It’s also pretty straight forward. In this example, we’re comparing this year’s Internet Sales Amount to the previous year’s Internet Sales Amount.
Create Member CurrentCube.[Measures].[Yearly Growth Internet Sales Amount]
As
([Date].[Calendar Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Calendar Year].PrevMember,[Measures].[Internet Sales Amount]),
FORMAT_STRING = “Currency”,
VISIBLE = 1 , DISPLAY_FOLDER = ’10 MDX Calculations’ , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;
The function here to pay attention to is the PrevMember function. The PrevMember function returns the previous member at a given level based on the supplied member. For example, if the supplied member was 2011, the previous member would be 2010.
Create Member CurrentCube.[Measures].[Monthly Growth Internet Sales Amount]
As
([Date].[Month of Year].CurrentMember,[Measures].[Internet Sales Amount])-
([Date].[Month of Year].PrevMember,[Measures].[Internet Sales Amount]),
FORMAT_STRING = “Currency”,
VISIBLE = 1 , DISPLAY_FOLDER = ’10 MDX Calculations’ , ASSOCIATED_MEASURE_GROUP = ‘Internet Sales’ ;
Similar to our last calculation, if we wish to calculate the growth from month to month, we should reference the Month attribute of our Date dimension.
10. YTD, QTD, and MTD Calculations
These kinds of calculations are pretty common and I see these in a lot of cubes. They’re pretty easy to wire up since there are only a couple simple MDX functions necessary to make this work.
CREATE
MEMBER CurrentCube.[Measures].[YTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Calendar Year]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING = “Currency”
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales';
Let’s start with the PeriodsToDate function. The PeriodsToDate function is going to return all the sibling members at the specified level up to the current member. The outer function, the Aggregate function, calculates the value based on the aggregation type specified in the cube for the measure. In our case, the Internet Sales Amount aggregation type is Sum, so the Aggregate function could be exchanged for the Sum function.
If I wanted to calculate the Month to Date for Internet Sales, I would simply exchange the reference for the Calendar Year level of the Calendar hierarchy with the Month level, as seen here:
CREATE
MEMBER CurrentCube.[Measures].[MTD Internet Sales Amount] AS
Aggregate
(
PeriodsToDate
(
[Date].[Calendar].[Month]
,[Date].[Calendar].CurrentMember
)
,[Measures].[Internet Sales Amount]
)
,FORMAT_STRING =
Currency
,VISIBLE = 1
,DISPLAY_FOLDER = ’10 MDX Calculations’
,ASSOCIATED_MEASURE_GROUP = ‘Internet Sales';
No comments:
Post a Comment