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

July 18, 2012 at 4:15 pm · Filed under Excel, Uncategorized ·Tagged Office 2013, olap tools, pivot tables, table extensions

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.

Permalink