Posts Tagged olap tools

[Office 2013] New OLAP Tools Part 2: Calculated Member

The first part has been published here and discussed the Calculated Measure case.

This second part is about Calculated Members.

2) MDX Calculated Member

Calculated Members should also be familiar to developers but if you’re not, just know that they can be seen as some kind of placeholder for sets of dimensions members.

A simple example should speak more than a thousand words :

In this example, I created a new member named [Australia & Canada], attached to the [Customer] dimension  [Customer Geography] hierarchy [All Customer] member.

In the Excel user interface it’s configured like this :

As a result, the displayed numbers (here the Sales Amount Measures) is the aggregation (here the sum) of those numbers affected to the respective members referenced in the formula.

Here Australia ($9 061 000) + Canada ($1 977 844) = Australia & Canada ($11 038 845)

The beauty of the thing is that it works whatever (Calculated) measure you may display in the Pivot Table.

Please note also that Calculated Members do not duplicate members but rather reference them. As such you will notice that the [Australia & Canada] sales amount does not impact the Grand Total. The first reflex of the few users I’ve shown this feature was to think that something was wrong as in their mind a grand total should be the sum of everything and not the sum of everything except this to that … At least, I would have like to have some way to visually distinguish calculated members from other members and/or and option in the Pivot Table options to have Visual Totals that would include the calculated members in the aggregation.

Now that we have presented the calculated members, lets play a little bit and see what we can do with them.

First, we used the + sign but we can also use any other arithmetical signs : minus sign (-), division sign (/), multiplication sign (*) and have the resulting numbers behaving accordingly.

Then, an alternative syntax to our first formula could have been the following :

AGGREGATE({[Customer].[Customer Geography].[Country].&[Australia],[Customer].[Customer Geography].[Country].&[Canada]})

In this formula, we use a set and aggregate it to define our calculated member. Starting from that, we see that we can use any formula or function that return a Set, wrap it in an aggregate function and display the result.

Doing so, we can build some kind of Dynamic Calculated Members. We can for example add a calculated member that will be used to display measures aggregations for our top 50 customers in term of sales :

Those of you familiar with the Adventure Works database sample may have noticed that Top 50 Customers is also the name of a named set in our Customer dimension : since named sets are … sets, we are allowed to use them in our calculated members definition :

To be exhaustive, I have to mention that it will work the same way if you define your name set in excel through the “Field, Items & Sets” menu.

We can add properties to our Calculated Members definition exactly the same way we do it for Calculated Measures. Though I have to say most of them are of limited use or no use at all : Properties like DISPLAY_FOLDER and ASSOCIATED_MEASURE_GROUP have no effect and even no sense for the second one, even if the syntax is valid.

Properties like FORMAT_STRING and FORE_COLOR have limited interest as they affect all measures displayed. Here’s an illustration of what to expect from those properties :

One last word on accessibility : As for Calculated Measures, a Calculated Member definition is stored in the context of the pivot table connection. So :

– what’s done in your Excel workbook cannot be seen  by your colleagues

– you can share Calculated Members definitions between different pivot tables/charts providing the fact they share the same Excel Connection

You can refer to the last paragraph of this post to understand the implications.

Advertisements

Leave a Comment

[Office 2013] New OLAP Tools Part 1: Calculated Measure

Microsoft has introduced for the first time in Excel 2010 a new OLAP Tools menu with a new What-If Analysis option.

With Excel 2013, Microsoft is introducing two new “tools” for OLAP Pivot Tables/Charts : Calculated Measures and Calculated Members.

Those are well-known from developers and they are now accessible to everyone through standard Excel UI, in the PivotTable Tool / Analyze ribbon, Calculations group, OLAP Tools menu.

In this two parts post, for both options, I’ll try to present a short example.

1) MDX Calculated Measure

The concept of calculated measure is not new for developers, though it is for the first time available to the end user through Excel. In fact, this has been around for relational pivot tables since the beginning (at least as far as I can remember :-)) but it has had no equivalent for the OLAP ones until now.

To be complete, with the introduction of session objects in Analysis Services 2005, one could have achieved such a thing through macros or add ins. Olap Pivot Table Extensions on Codeplex is using them to allow one to use calculated measures on previous versions of Excel.

Until then, end users had to have their new calculated measures implemented by the IT inside the cube (and wait until the result is available in production) or to make their own calculation into Excel cells (and take the risk to miss some calculations as the pivot table can be dynamically resized as its structure is changing). None of those solutions are completely satisfying.

A calculated measure has at least a name and a formula that you can enter through the dedicated Excel Form :

Let’s say that we want to calculate an “Internet Sales Amount Budget” which would be a  20% increase of the regular “Internet Sales Amount”. We will associate this measure to an existing measure group (“Internet Sales” the one we took the original measure from) and add it to a sub folder named “Budget”. The wizard should look like this …

… and the result should look like this …

That’s all for the basic usage of this feature.

You should be aware that when typing the formula, you are writing a MDX expression and as such you can enter anything that is a valid MDX expression.

A more elaborate scenario would be to have side by side actual Internet Sales Amount values and Budgeted Amount values from last period to compare them. To do this, you can add a new calculated measure named “Internet Sales Amount Budget P-1” with the following formula (The explanation of the formula itself is out of scope the present article) :

SUM

(

{ParallelPeriod([Date].[Calendar].CurrentMember.Level, 1)}

,[Measures].[Internet Sales Amount Budget]

)

The result should be the following :

We now have the Sales Amount and the Budgeted Sales amount from last period side by side.

At this level the we have a little problem with the format. It seems that with simple formulas like the first one, Excel can infer the formatting from the used measure. In our more elaborate example Excel seems to be lost so we will help it a little bit.

If you are an Analysis Services Multidimensional Cube developer, you know that calculated measures in your MDX script accept a whole set of options among which reside the FORMAT option. We will now enhance a little bit our formula to have a better rendering; replace the previous formula by the following :

Other options exist that can be used, you’ll find here the most common ones.

You can also play with the ones not listed here but available in BIDS in the cube editor Calculations tab like FORE_COLOR, BACK_COLOR, FONT_NAME, FONT_SIZE, etc… so you can have full control on calculated measures behavior.

One last thing to do with Calculated Measures is to use them to build new calculated measures. For this last example, we will calculate a budget variance and we will play with several options. First, the formula should be :

1 - ([Measures].[Internet Sales Amount Budget P-1 ] - [Measures].[Internet Sales Amount])
/[Measures].[Internet Sales Amount Budget P-1 ]

We want it to be shown as a percentage so we add the following

,FORMAT_STRING = "Percent"

And we want some conditional formatting to show where we are not on budget :

,FORE_COLOR = CASE
     WHEN [Date].[Calendar].CurrentMember < 1
         THEN 255 /*Red*/
     ELSE 65408 /*R=128, G=255, B=0*/ 
END

At the end, the result should look like this :

One last word on Calculated Measures accessibility : you have to realize that the Calculated Measure definition is stored in the context of the pivot table connection.

The consequences are the following :

  • what’s done in your Excel workbook cannot be seen  by your colleagues.
    • This prevent both being flood by others calculated measures as well as being impacted by poor performing MDX code.
    • To share your Calculated Measures, you have to share the workbooks.
  • you can share Calculated Measures definitions between different pivot tables/charts providing the fact they share the same Excel Connection.
    • You can even take advantage of this to simulate some kind of what-if analysis : imagine you have a big formula that can be parametrized; you have used Calculated Measures to store parameters values and have referenced them into the formula; by simply connecting to Pivot Table to the same database but with different connections, you can modify the parameters value of your formula in each context and yet see the result of your ‘simulation’ side by side in Excel.

Comments (5)