Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Archive for October 2012

Free MDX Training Video Now Live

with 4 comments

I’m sure a lot of you have seen Rafal Lukawiecki speak at BI events around the world; well, a few months ago he launched a new online training business at http://projectbotticelli.com/ and I’m honoured to announce that the first video I’ve recorded for him is now live. It’s a 30 minute session on some of the basic theory of MDX (the concepts of unique names, tuples and sets), it’s free to view and it’s available here:
http://projectbotticelli.com/knowledge/mdx-basic-concepts-unique-names-tuples-sets-video-tutorial?pk_campaign=cw2012cwb

I’ll be recording some more MDX videos with him next year, and I’ll post again when they’re done. There are also many other great videos from Rafal, Marco and Alberto on DAX, SSAS 2012 Tabular, PowerPivot, Data Mining and other MS BI topics available which you can find here:
http://projectbotticelli.com/video?tid=All&pk_campaign=cw2012cwb

Hope you enjoy them! And if you’re looking for some classroom-based MDX training, the course I’m teaching in London in December still has some spaces left:
http://www.technitrain.com/coursedetail.php?c=12&trackingcode=CWB

Written by Chris Webb

October 28, 2012 at 9:19 pm

Posted in MDX, Video Training

Tagged with

Returning Selected Items in an Excel Slicer Using MDX in PowerPivot and SSAS

with 11 comments

One problem I came up against recently is how to find out what has been selected on an Excel slicer connected to SSAS or a PowerPivot model. There are a number of blog posts showing solutions to this problem, both for scenarios where only one item has been selected and when multiple items have been selected, for example (look at the comments as well as the posts themselves):
http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/
http://www.powerpivotpro.com/2011/09/catching-multiple-slicer-selections-in-a-formula/

…but I’ve come up with a new, MDX-based approach that handles the multiple selection scenario no matter how many items there are in the slicer, and which I thought was worth blogging about. I was tempted to include this in my series on MDX for PowerPivot but since it’s equally relevant for SSAS users, and the MDX is a bit complicated for an introductory series, I thought I’d make it into a standalone post.

To describe this technique I’m going to use same PowerPivot model I describe here, which is based on the AdventureWorks DW sample database. First of all, create a new PivotTable with CalendarYear in a slicer and CalendarYear on rows and any relevant measure on columns:

image

Selecting a Year in the slicer means that that Year appears on rows in the PivotTable, and selecting multiple years means multiple rows are displayed, as you would expect. Next you need to add a new MDX calculated measure to the PivotTable. If you’re using SSAS you can simply add the calculation onto your cube or you can use the OLAP PivotTable Extensions addin; for PowerPivot users the OLAP PivotTable Extensions approach is the only option, so that’s what I’ll demonstrate. Right-click inside the PivotTable and choose OLAP PivotTable Extensions from the right-click menu, and then in the Calculations tab create a new calculation called SelectedYears with the following definition:

Generate(
Except(
  Axis(1)
  , {[DimDate].[CalendarYear].Defaultmember}
)
, [DimDate].[CalendarYear].Currentmember.Name
, ", "
)

image

…and click Add to PivotTable. The result will be a new measure that returns a comma-delimited list of the names of everything selected on the rows axis of the query, and therefore everything selected in the slicer:

image

How does this work? Well, first of all Axis(1) is used to return the set used on the rows axis of the query used to populate the PivotTable (incidentally, this is why it’s important to have another measure in the PivotTable – if it’s not there, the structure of the query generated by Excel will be different and while the MDX can be altered to handle this, some of the items in the slicer will be partially greyed-out). The set returned by Axis(1) will include the All Member of the CalendarYear hierarchy, so the Except() function is used to remove it; finally, the Generate() function is used to iterate over this set and return the comma-delimited list of member names. In this example the CalendarYear field on the DimDate table in my PowerPivot model has become the MDX hierarchy with the unique name [DimDate].[CalendarYear]; please read this post for some background on how PowerPivot objects map to MDX objects.

Now you have the PivotTable you can refer to the top-right cell in it (in this example it’s cell F3)  in it to return the list of years and hide the PivotTable itself; this allows you to create dynamic titles like the following:

="Sales For Years: " & F3

You can then also create other, new PivotTables and hook them up to the original slicer and they will all work as normal:

 image

Written by Chris Webb

October 22, 2012 at 7:44 pm

Introduction to MDX for PowerPivot Users, Part 3: The Members() and Crossjoin() functions

with 2 comments

In the previous post in this series I looked at how MDX set expressions could be used inside Excel to give you total control over what appears on the rows and columns axis of your PowerPivot PivotTables. However, I only showed how to construct basic MDX set expressions using members and tuples; in this post I’ll show you how to use two of the commonest MDX set functions: Members() and Crossjoin().

MEMBERS()

The Members() function returns the set of members from either an entire hierarchy or a single level from that hierarchy. I’d say it is by far the most widely-used of all MDX functions, even if a lot of the time people don’t realise they are using it (see here for why that is). As far as PowerPivot goes it’s not all that useful on its own – if you want to see all the members on a level or a hierarchy, it’s easy to do that without using named sets – but it is frequently used in conjunction with other set functions. Some examples:
[DimDate].[EnglishDayNameOfWeek].MEMBERS
…returns the set of all members on the EnglishDayNameOfWeek hierarchy, whose unique name is [DimDate].[EnglishDayNameOfWeek]. Remember that in PowerPivot, as I said in the first post in this series, a column in a table becomes a hierarchy in MDX and a hierarchy in PowerPivot also becomes a hierarchy in MDX; also that this expression will also return the All Member from the hierarchy, which means that this expression will return a Grand Total row:

image

Compare this with the results returned by the expression:
[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
Here I’m using the Members() function with the unique name of the EnglishDayNameOfWeek level on the EnglishDayNameOfWeek hierarchy, whose unique name is [DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek]. I don’t want to go into too much detail about how this is different from the previous expression; for a PowerPivot user the thing to note is that the All Member is now not returned in the set, and there is no Grand Total row returned:

image

CROSSJOIN()

The Crossjoin() function takes two or more sets and returns a set of tuples representing all possible combinations of items in these sets. So, for example, the crossjoin of the two sets {A, B} and {X, Y} is the set of tuples {(A,X), (A,Y), (B,X), (B,Y)}. There are in fact several ways to write a crossjoin in MDX as I showed in this post, and I prefer to use the * operator over the Crossjoin() function because it’s less verbose. Here’s an example of two set expressions that return the same result using the Crossjoin() function and the * operator:

[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
*
[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].MEMBERS

…and…

CROSSJOIN(
[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
,
[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].MEMBERS
)

…and here’s the output:

image

Using Members() And Crossjoin() To Optimize PivotTables With Many Hierarchies On Rows And Columns

In this series I want to balance out each dose of theory with some useful practical tips, and here’s the first practical tip: when you have a PivotTable with many hierarchies on rows or columns, you may find that it takes a long time to refresh and that using a named set instead may help improve performance. This is because of a design flaw in the way Excel generates the MDX for PivotTables which means that even when you opt not to display subtotals and grand totals, Excel still requests some of them in the queries it runs against your PowerPivot model. This issue has been blogged about in detail several times by Rui Quintino, Richard Lees and me:
http://rquintino.wordpress.com/2010/10/25/excel-20072010-pivot-tables-getting-detailedgranular-table-reports-from-olap-in-seconds/
http://richardlees.blogspot.ch/2010/04/improving-excels-cube-performance.html
http://cwebbbi.wordpress.com/2011/10/07/excel-subtotals-when-querying-multidimensional-and-tabular-models/

So, for example, if you have put CalendarYear, EnglishDayNameOfWeek and EnglishProductCategoryName on rows in your PivotTable like so:

image

You can replace this with a named set with the following definition:

[DimDate].[CalendarYear].[CalendarYear].MEMBERS
*
[DimDate].[EnglishDayNameOfWeek].[EnglishDayNameOfWeek].MEMBERS
*
[DimProductCategory].[EnglishProductCategoryName].[EnglishProductCategoryName].MEMBERS

Here, I’m asking for the crossjoin of all the members (except the All Members) on the CalendarYear, EnglishDayNameOfWeek and EnglishProductCategoryName hierarchies and not requesting any subtotals or grand totals at all in the query. The larger the number of hierarchies on rows or columns in your PivotTable the more noticeable the improvement in performance will be when using this MDX pattern. The penalty for doing this, though, is that end users lose the ability to drill up or down or to alter the selection made without editing the MDX.

In part 4, I’ll take a closer look at how to filter sets.

Written by Chris Webb

October 21, 2012 at 10:42 pm

Posted in Excel, MDX, PowerPivot

Some thoughts on what Office 2013 means for Microsoft BI

with 21 comments

You may have seen the news late last week that Office 2013 has RTMed, which in itself isn’t that significant – it’s not going to be until mid-November that the likes of you or I can download it. But it’s a milestone and therefore a good time to think about what Office 2013 means for Microsoft BI as a whole.

Let me start by saying that I’ve spent a lot of time playing with Office 2013, especially Excel 2013, over the last few months and I’ve been very impressed with it. I think it’s a great product and also that it represents a significant turning point for Microsoft BI. I won’t summarise everything I’ve said in previous blog posts about new functionality (you can read those yourself!), but here are what I consider some of the important points to consider when assessing its impact:

  • Number 1 on the list of new features for BI has to be the way PowerPivot has been integrated into Excel. Indeed, although PowerPivot still exists as a separate addin, I’m not sure it’s particularly helpful to think of PowerPivot and DAX as something distinct from Excel any more – we should think of them as the native Excel functionality that they are. Maybe we shouldn’t even use the names PowerPivot and DAX at all any more? And of course, now that users will get it by default, it will open the way to much, much wider adoption. I’m working on a PowerPivot/Excel 2010 project at the moment where the customer’s desktops are locked down and it took several weeks to get PowerPivot installed on even a few desktops; with Excel 2013 those problems won’t occur.
  • The integration of Power View into Excel comes a close second in terms of significant new functionality. Like a lot of people I was impressed by the technology when I saw first saw the Power View in Sharepoint last year, but frankly the Sharepoint dependency meant none of my customers were even vaguely interested in using it and I thought it was stillborn. Putting Power View into Excel changes all this – it’s effectively giving it away to all corporate customers and, as with PowerPivot, this will remove a lot of barriers to adoption. It might not be as good at data visualisation as something like Tableau, but it doesn’t need to be – you’re going to get it anyway, it will do most of what these other tools do, so why bother looking at anything else?
  • The way PivotTables and Power View reports now work so well in the browser with Excel Services and the Excel Web app means that Excel should now be considered the premier web reporting and dashboarding solution in the Microsoft BI stack, and not just as something for the desktop. I’ve never been fond of PerformancePoint (and again I never saw significant uptake amongst my customers – indeed, over the years, I’ve seen it used only very rarely) and I see less and less reason to use it now when Power View does something similar. SSRS still has its own niche but even it will start to decline slowly because it will be so much easier for BI pros and end-users to build reports in Excel. This in turn will make the whole Microsoft BI stack much more comprehensible to customers and a much easier sell – Excel will be the answer to every question about reporting, data analysis, data visualisation and dashboards. 
  • Office 365 will help overcome the problems customers have with the Sharepoint dependency in the Microsoft BI stack. I discussed this problem at length here; having now used Office 365 on the Office Preview myself, I’m a convert to it. I’ve had Sharepoint installed on various VMs for years but it’s only now with Office 365 and freedom from the pain of installation and maintenance that I can start to appreciate the benefits of Sharepoint. For small companies it’s the only way Sharepoint can be feasible. More important than anything else, though, is the subscription pricing that has just been announced: Office 365 is a no-brainer from a cost point of view.  I saw recently that Toyota Motor Sales in the US have just decided to go to Office 365 and I wouldn’t be surprised if other, larger enterprises to do the same; this isn’t just something for SMEs.
  • The ability to stream Excel 2013 to desktops means that yet more barriers to deployment will be removed.
  • We’re still waiting for Microsoft’s mobile BI solution, of course. I hope it’s coming soon! Whatever form it takes, I would expect it to be very closely linked to Office 2013.

What do you think, though? I’m interested in hearing your comments – have I drunk too much Microsoft Kool-Aid?

Written by Chris Webb

October 14, 2012 at 11:07 pm

Posted in BI, Excel, Office 2013

Introduction to MDX for PowerPivot Users, Part 2: Basic Sets

with 10 comments

In the first post in this series I looked at how the objects in a PowerPivot model mapped onto MDX objects; now, I’m going to start looking at some practical uses for MDX with Excel’s named sets functionality. Once you’ve created a PivotTable on top of your PowerPivot model, you can find this functionality on the PivotTable Tools/Options tab on the ribbon by clicking on the Fields, Items and Sets button:

image

What it does is allow you fine control over what appears on the rows and columns axes of your PivotTable. Now you don’t need to know any MDX to use this functionality to do fairly basic things like delete rows or to change the order, but with MDX you can do some pretty amazing things!

Consider the following simple PowerPivot model built from the Adventure Works DW database:

image

From this you can create a PivotTable with Calendar Years on rows and a measure that sums up the SalesAmount column:

image

With this PivotTable created, you can now go to the ribbon and you’ll see that there is the option to create a named set based on the selection you’ve made on rows:

image

Clicking on the Create Set Based on Row Items menu item opens the New Set dialog:

image

It’s here you can add, delete, copy and move rows; if you click the Edit MDX button then you can see the MDX representation of the selection you’ve made:

image

Here’s the actual MDX from this screenshot:

{([DimDate].[CalendarYear].&[2005]),([DimDate].[CalendarYear].&[2006]),([DimDate].[CalendarYear].&[2007]),([DimDate].[CalendarYear].&[2008]),([DimDate].[CalendarYear].[All])}

This is an MDX set expression: a set is just an ordered list of things, and there’s no equivalent object in DAX or SQL. Sets are written as comma delimited lists of members or tuples (we’ll come to what a tuple is later!) surrounded by curly brackets or braces, ie {}. This example is a set of Calendar Years in the following order: 2005, 2006, 2007, 2008 and a member called “All” which returns the Grand Total value. Sets can be given names – and so become named sets – and this means that anywhere MDX expects a set expression you can use the name of the set you’ve defined to return that set.

If, at this point, you click OK, a new named set called “Set1” will be created and that will be used as the selection on the rows axis of the PivotTable; since you haven’t changed any of the MDX, though, the PivotTable itself will look identical. The fact that the set is now used to control what’s on rows can be seen in the field list:

image

You can now go back and edit the set by clicking on the Manage Sets menu option under Fields, Items and Sets, selecting Set1 from the list of named sets and clicking Edit.

Rearranging and deleting items in the set expression in the Modify Set dialog that then appears (which looks the same as the New Set dialog above) changes the contents of the named set and so changes what is selected on the rows axis of the PivotTable. You can find the unique names of members and other objects by dragging them from the Fields, Items and Sets pane on the left hand side into the Set Definition text box on the right hand side.

It’s important, if you want the order of items in your set to be maintained (and you almost always do when writing your own MDX), that you also uncheck the Automatically Order and Remove Duplicates From the Set option at the bottom of the dialog:

image

For example, using the following set expression with automatic ordering turned on:

{([DimDate].[CalendarYear].&[2008]),
([DimDate].[CalendarYear].&[2005]),
([DimDate].[CalendarYear].&[2007])}

Gives you the set of years 2008, 2005 and 2007 in the order 2005, 2007 and 2008 and results in a PivotTable looking like this:

image

With automatic ordering turned off you get the set of years 2008, 2005, 2007 in that order, which is of course the order they are listed in the set:

image

MDX is a language plagued with brackets and commas and it’s very easy to make syntax errors when writing it. To check that your MDX is syntactically correct you can click the Test MDX button in the Modify Set dialog.

Now let’s talk about tuples. A tuple is another MDX concept that doesn’t have an equivalent in DAX or SQL; you can think of it as a kind of co-ordinate. Tuples are written as comma delimited lists of members surrounded by round brackets, ie (). As I said before, you can have sets of members or sets of tuples (well, strictly speaking all sets are sets of tuples but I won’t go there…) and the two sets you’ve seen above are sets of tuples. So the set:

{([DimDate].[CalendarYear].&[2008]),
([DimDate].[CalendarYear].&[2005]),
([DimDate].[CalendarYear].&[2007])}

…contains three tuples, and this:
([DimDate].[CalendarYear].&[2008])

…is a single tuple containing one member, the member for the year 2008 which has the unique name:
[DimDate].[CalendarYear].&[2008]

The set:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarYear].&[2007]}

…is a set of three members – notice that the round brackets have disappeared – but will return the same three rows in a PivotTable as the previous set. In these two examples, each tuple or member (ie each item) in the set becomes a single row in the PivotTable.

Tuples can have more than one member in them though. Consider the following set:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[EnglishDayNameOfWeek].&[Tuesday])}

It still contains three tuples, but this time each tuple is composed of a year and a day name. Each tuple still becomes a row in the PivotTable (something which is best observed if you choose Show in Tabular Form on the PivotTable Design tab of the ribbon), but now each row has two levels of nesting, a year followed by a day name:

image

You can have as many members as you like in a tuple, so for example you could add some quarters too:

{([DimDate].[CalendarYear].&[2008],
[DimDate].[EnglishDayNameOfWeek].&[Monday],
[DimDate].[CalendarQuarter].&[1]),
([DimDate].[CalendarYear].&[2005],
[DimDate].[EnglishDayNameOfWeek].&[Friday],
[DimDate].[CalendarQuarter].&[4]),
([DimDate].[CalendarYear].&[2007],
[DimDate].[EnglishDayNameOfWeek].&[Tuesday],
[DimDate].[CalendarQuarter].&[1])}

image

I’ll finish off this post by mentioning the two important rules that you have to remember when defining sets and tuples, namely:

  • Each item in a set has to be the same type of thing. So, if you have a set of members, each member has to come from the same hierarchy; if you have a set of tuples, each tuple has to contain the same number of members and each tuple has to contain members from the same hierarchy in the same position.
  • Each item in a tuple has to be a member from a different hierarchy.

For example:

This is a valid set containing three members, because each member comes from the CalendarYear hierarchy:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarYear].&[2007]}

This is not a valid set however, because it consists of two members from the CalendarYear hierarchy and one member from the CalendarQuarter hierarchy:

{[DimDate].[CalendarYear].&[2008],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarQuarter].&[1]}

This is a valid tuple because it contains three members from three different hierarchies:

([DimDate].[CalendarYear].&[2007],
[DimDate].[EnglishDayNameOfWeek].&[Tuesday],
[DimDate].[CalendarQuarter].&[1])

This is not a valid tuple because it contains two members from the CalendarYear hierarchy:

([DimDate].[CalendarYear].&[2007],
[DimDate].[CalendarYear].&[2005],
[DimDate].[CalendarQuarter].&[1])

This is a valid set of tuples because each of the three tuples consists of a member from the CalendarYear hierarchy followed by a member from the EnglishDayNameOfWeek hierarchy:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[EnglishDayNameOfWeek].&[Tuesday])}

Whereas this is not a valid set of tuples, even though each tuple on its own is valid, because the final tuple in the set contains a member from the EnglishDayNameOfWeek hierarchy followed by a member from the CalendarYear hierarchy, rather than a CalendarYear followed by an EnglishDayName of week:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[EnglishDayNameOfWeek].&[Tuesday], [DimDate].[CalendarYear].&[2007])}

This is not a valid set of tuples either, because the final tuple (which again is valid in its own right) contains a CalendarYear followed by a CalendarQuarter:

{([DimDate].[CalendarYear].&[2008], [DimDate].[EnglishDayNameOfWeek].&[Monday]),
([DimDate].[CalendarYear].&[2005], [DimDate].[EnglishDayNameOfWeek].&[Friday]),
([DimDate].[CalendarYear].&[2007], [DimDate].[CalendarQuarter].&[1])}

Phew! I know the theory behind MDX can be very heavy going, but I promise you it’s important to learn it in order to be able to get the most out of the language. Next time, in part 3, I’ll look at some functions that returns sets and see how they can be used to construct more complex selections.

Written by Chris Webb

October 11, 2012 at 10:51 pm

Posted in MDX, PowerPivot

Introduction to MDX for PowerPivot Users, Part 1

with 5 comments

This is a series of posts I’ve wanted to write for a long time. The more I use PowerPivot, the more I realise how many more cool things you can do when building your Excel reports if you know a bit of MDX. Of course it seems a bit strange to say this, because (at least if you hear Marco and Alberto present at a conference!) MDX is a bit passé and DAX is the native language of PowerPivot, SSAS Tabular and, well the future. But Excel speaks MDX and Excel will continue to speak MDX for a long time to come, so it’s MDX that you need to know if you want to get the most out of PowerPivot.

So, before I start, when is it useful to know MDX with PowerPivot? Here’s the functionality that it’s relevant for:

  • The Excel cube functions, especially but by no means limited to, the CubeSet() function.
  • The Create Set Based on Rows/Columns options under the Fields, Items and Sets button on the PivotTable Options tab on the ribbon
  • When binding the results of an MDX query to a table in Excel, as described here
  • If you ever wanted to write MDX calculations using OLAPPivotTableExtensions, though I’m struggling to think of scenarios where you’d want to do this

I’ll come back to the practical uses of MDX in future posts; the next task is to understand how the objects in a PowerPivot model map onto the multidimensional objects that MDX understands. From now on I’ll be using the MDX terminology so it might be useful to refer back to the list below if you get confused!

  • A PowerPivot model is seen as a single cube in MDX; you can think of a cube and a PowerPivot model as being the thing that holds all the data, the thing you’re querying. The cube that a PowerPivot model is exposed as is called [Model].
  • In PowerPivot a model is made up of multiple tables; in MDX a cube is made up of multiple dimensions. Each table in a PowerPivot model becomes a dimension in MDX.
  • In PowerPivot a table is made up of multiple columns, each of which can be dragged onto the rows and columns of a PivotTable. Each of these columns becomes a hierarchy in MDX. Confusingly, hierarchies in PowerPivot also become hierarchies in MDX. Basically, anything that you can put on rows, columns, a filter or in a slicer is a hierarchy in MDX.
  • Each distinct value in a column becomes a member on a hierarchy in MDX. For example, the value ‘Bikes’ from a column called EnglishProductCategoryName on a table called DimProductCategory becomes an MDX member with the name:
    [DimProductCategory].[EnglishProductCategoryName].&[Bikes]
  • Each measure in a PowerPivot model becomes a member on a dimension called [Measures] (which only has one, invisible hierarchy) in MDX. For example a PowerPivot model called [Sum of SalesAmount] becomes an MDX member with the following name:
    [Measures].[Sum of SalesAmount]

In Part 2 I’ll introduce the concept of sets and how they can be used.

Written by Chris Webb

October 7, 2012 at 11:47 pm

Posted in MDX, PowerPivot, Tabular

Follow

Get every new post delivered to your Inbox.

Join 2,868 other followers