Archive for Excel

Microsoft is announcing Power Bi for Office 365

Today, during the Worldwide Partner Conference, Microsoft announced “Power BI for Office 365”.

Following the “Empower the end user” trend, Microsoft will provide to Office 365 users it’s self-service BI Tools that has until now been shipped into Excel : Power Pivot, Power View, Data Explorer renamed Power Data and Geo Flow renamed Power Map.

Doing so, Microsoft is also giving a final name to the two self-service bi Tools that are still in preview in Excel 2013: Data Explorer and Geo Flow.

What’s also important to note here, is that through this release, Microsoft will start to address the mobility since the rendering will be done in html5 (was previously done in Silverlight).

You can read the official blog post here : Introducing Power BI for Office 365

… and register for the preview here : Power BI for Office 365

Update : Chris Webb shares his thoughts on this there.


Leave a Comment

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

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



{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 :

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

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

Analysis Services : How to add subfolders to your Measures Groups (Display folders tips and tricks)?

I’ve already blogged three times on measure groups matters and never thought I would have enough material for a fourth blog post. Fact is I stumble across an old post from Mosha Pasumansky on his sqlblog blog on the topic and rediscovered something I almost forgot with the years.

First you can find the three first posts here, although you’re not required to read them first to get the point of this post :

Analysis Services: What are the best practices for grouping measures ?

Analysis Services Feature Suggestion : Managing Measure Group visibility

Analysis Services : How to break distinct count measures into separate measure groups ?

To sum up Mosha’s blog post, you can add some extra flavour to your measures organization into folders with the following techniques :

– You already know that you can organize your measures into folders. But it is less known that you can have also sub folders. For doing this, as the UI in BIDS does not seem to allow you to do it, you jus have to enter the folders located on the path to the indicator separated by a “\” or “/” sign depending on your client tool. (ie : “folder 1\folder 2\…\folder x”)

– Even better, you can specify multiple folders to host the same measure by separating them with a “;” sign. (ie : “folder 1;folder 2;…;folder x”). In this case the measure will appear as much as the number of folders but it will act as one and single measure as selecting or de-selecting one of the clone will automatically impact the other instances.

Note : All those behaviors are client tool dependant as the information are only metadata attached to each measure and as nothing requires the client tool to make use of it. So depending on which tool you’re using, you may have to test its behavior before proposing those mechanism to you end users. I have tested them with success in Excel 2010.

Leave a Comment

Getting help choosing the right chart type in Excel

As a follow-up to my previous post depicting a flow chart for choosing which type of chart can better emphasize our reporting needs, I dig up an Excel add in intending to help you in this task.

Published by Microsoft Office labs, the Chart Advisor add in for Excel “identifies, ranks, and displays an array of charts most relevant to you so you
can make the most out of your presentation.”

Though not very new (it was first published in may 2010), it’s worse mentioning it for those of you who may not have been aware of its existence. One last word to say that there is no Office 2010 version, it only works with Office 2007.


Leave a Comment

Older Posts »