Archive for July, 2012

[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)

[Office 2013] Excel Filters : Slicer and Timeline

With Excel 2013, Slicers are now categorized as Filters and a new kind of Filter is introduced, the Timeline. They are both available in a new group in the Insert ribbon.

1) What’s new for the Slicer Filter object

First introduced with Excel 2010, Slicers filters have been widely used to circumvent the classic Pivot Table Filter landing zone limitations :

 – Slicers can be shared between Pivot Tables/Charts, meaning that they can influence more than one object a the same time making them great for dashboarding scenarios.

Slicers can be contextual meaning that they can underline the information that need your attention avoiding to spend time on items with no data for example by shadowing them.

Starting with Excel 2013, Slicers can even completely hide items with no data as shown in the following screenshot :

2) The new Timeline Filter

The (real) new thing about Filters is the Timeline. This new Filter lets you choose any dimension for which the dimension usage is set to “Date” (in case of a multidimensional model, aka cubes) or any table marked as a date table (in case of a tabular model whether it’s taken from Excel PowerPivot, or Analysis Services).

The date dimension/table selector can be filtered by measure groups in case of a multidimensional model.

A Timeline is a graphical date range selector with which you can choose on which level (Year, Quarter, Month, Date) you want to operate and then click on your selection and slide its boundaries with the little grips on both sides.

They are really handy and benefits of all the common features from the Slicers like their ability of being shared by several pivot tables.

The only two  one thing I think it lacks to the Timeline are :

– the ability to drag and drop the range along the time line once  it’s defined : select a range of two months for example, if you want to move the range you have to reselect the new first and last months. The ability to move/slide the range along the time line would be a great time saver.

Update : Thanks to Larry’s comment, it appears that we can actually drag and drop time ranges by just holding the Left Mouse key + Ctrl key! Thanks Larry ! 🙂

– the ability to use a custom time hierarchy : for now, the Timeline object takes the date and show always the same 4 levels(Year, Quarter, Month, Date). What if I want to filter on a week level ? or if I want to switch to a fiscal calendar ?

Comments (8)

[Office 2013] New Office aka Office 2013 has been unveiled by Microsoft

Yesterday Microsoft has made available for download a Consumer Preview of the new version of Office aka New Office aka Office 2013 that will be RTM’d early next year.

It’s always a big time for us Microsoft BI professionals as Microsoft Office has always been one of the pillar of Microsoft BI strategy along with SQL Server and SharePoint.

You can go there to try it by yourself…

There’s a long list of new things for us in business intelligence among the following :

– A new Data Model feature deeply integrated into Excel and that rely on PowerPivot : One can now link Excel tables together and query them with pivot tables as if they were one.

Power Pivot has been enhanced and integrated into Excel to support the new Data Model feature

Power View has been integrated into Excel and produce a new Excel sheet full of new reporting opportunities.

Slicers has been enhanced and a new “Time Line Slicer” has been added. You can read about them there.

– New Olap Tools have been added to create “Calculated Measures“, “Calculated Members” and to manage them… You can read about them there.

I’ll try to cover them all in the next few days … Chris Webb has already published two posts on the first three topics here and here.

 

Leave a Comment