Category: PowerPivot

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

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://blog.crossjoin.co.uk/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.

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

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.

Introduction to MDX for PowerPivot Users, Part 1

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.

Access 2013 and Self-Service BI

Wait, I know what you’re thinking: Access, isn’t that dead yet? Well, no – and if you’ve been reading the blogs about Access 2013 that it’s undergone something of a transformation, one that’s very interesting from a BI point of view. The key change is mentioned here:

http://blogs.office.com/b/microsoft-access/archive/2012/07/20/introducing-access-2013-.aspx

One of the biggest improvements in Access 2013 is one you may not even notice—except that you’re whole app will be faster, more reliable, and work great with large amounts of data. When Access databases are published to SharePoint—whether on-premise or through Office 365—a full-fledged SQL Server database is automatically created to store the data. Advanced users who are already familiar with SQL Server will be able to directly connect to this database for advanced reporting and analysis with familiar tools such as Excel, Power View, and Crystal Reports. Everyday users can rest assured that their apps are ready for the future if they ever need to enhance them with advanced integrations or migrations.

So while Access 2013 is still a desktop database, the Access Web App is essentially a power-user-friendly tool for creating SQL Server/Azure SQL database applications. As Tim Anderson points out here (in a post that’s also worth a read) there seems to be a bit of an overlap with LightSwitch; but that’s incidental here. The real point I wanted to make is that this is another key piece in the Microsoft self-service BI stack in place. By the time users are working with Office 2013 for real, I can imagine some quite sophisticated self-service BI solutions being built where data is loaded into a SQL Server database designed in Access (maybe using Data Explorer?) before it gets to Excel/PowerPivot, a much more robust approach than loading data direct from the original source into Excel/PowerPivot. I’m sure there’ll still be plenty of opportunity for SQL Server DBAs to look down on the work of Access developers, but it looks like this will give Access a new lease of life.

Unfortunately it looks like Access 2013 Web Apps won’t support OData just yet. Here’s a comment from Todd Haugen, a program manager on the Access team, on the first blog post referenced above:

Sorry to say we did not get to enable support for OData at RTM. This is a key area we are looking at for the next release. In the near-term SQL Azure will be turning on ODBC access which will allow you to hook Excel and PowerPivot together with Access. This feature will be available by RTM.

I had hoped to be able to write up a demo of PowerPivot connecting to a database created with the Access Web App, but this comment (and my inability to get it working, even though I can see the server name and database name I’d need to connect to in Access) means you’ll just have to imagine what might be possible…

Further reading:
http://msdn.microsoft.com/en-us/library/office/jj250134(v=office.15)
http://blogs.office.com/b/microsoft-access/archive/2012/07/30/get-started-with-access-2013.aspx

Consuming OData feeds from Excel Services 2013 in PowerPivot

In yesterday’s post I showed how you could create surveys in the Excel 2013 Web App, and mentioned that I would have liked to consume the data generated by a survey via the new Excel Services OData API but couldn’t get it working. Well, after a good night’s sleep and a bit more tinkering I’ve been successful so here’s the blog post I promised!

First of all, what did I need to do to get this working? Well, enable Excel Services for a start, duh. This can be done by going to Settings, then Site Collections features, and activating Sharepoint Server Enterprise Site Collection features:

image

With that done, and making sure that my permissions are all in order, I can go into Excel, start the OData feed import wizard (weirdly, the PowerPivot equivalent didn’t work) and enter the URL for the table in my worksheet (called Table1, helpfully):

image

Here’s what the URL for the Survey worksheet I created in yesterday’s post looks like:
https://mydomain.sharepoint.com/_vti_bin/ExcelRest.aspx/Shared%20Documents/SurveyTest.xlsx/OData/Table1

(there’s much more detail on how OData requests for Excel Services can be constructed here).

And bingo, the data from my survey is loaded into Excel/PowerPivot and I can query it quite happily. Nothing to it.

image

In a way it’s a good thing I’m writing about this as a separate post because I’m a big fan of OData and I believe that the Excel Services OData API is a big deal. It’s going to be useful for a lot more than consuming data from surveys: I can imagine it could be used for simple budgeting solutions where managers input values on a number of spreadsheets, which are then pulled together into a PowerPivot model for reporting and analysis; I can also imagine it being used for simple MDM scenarios where dimension tables are held in Excel so users can edit them easily. There are some obvious dangers with using Excel as a kind of database in this way, but there are also many advantages too, most of which I outlined in my earlier discussions of data stores that are simultaneously human readable and machine readable (see here and here). I can see it as being the glue for elaborate multi-spreadsheet-based solutions, although it’s still fairly clunky and some of the ideas I saw in Project Dirigible last year are far in advance of what Excel 2013 offers now. It’s good to see Microsoft giving us an API like this though and I’m sure we’ll see some very imaginative uses for it in the future.

Creating Surveys using Excel 2013 Forms

Jamie Thomson and I share a number of… obscure enthusiasms. For instance, last week when he spotted the new forms/surveys feature in the Excel 2013 Web App (see here for a mention) he knew I’d be excited. And I was. Excited enough to devote a whole blog post to them.

What is this feature? Basically a rip-off of homage to the Google docs functionality I mentioned here that allows you to create simple questionnaires and save the data back to a spreadsheet. To use it you need to create a new Excel spreadsheet in the Excel Web App (I can’t seem to find it in desktop Excel and it may not even exist there), then click on Form/New Form in the ribbon:

image

This opens a new dialog where you can create your form/survey:

image

It’s all pretty self-explanatory from there, you just enter a title and description and then some questions, which can be various types (returning text, numbers, multiple choices etc):

image

You can then answer the questions yourself or send a link out to other people so they can too:

image

If you’d like to take the survey you can do so here btw.

The data then lands in a table in the original Excel spreadsheet, ready for you to do something useful with it:

image

For my next trick, and to go back to another issue that Jamie and I have been moaning about for years, I would have liked to consume the data in this table via an OData feed as detailed here:
http://msdn.microsoft.com/en-us/library/sharepoint/jj163874(v=office.15)

Unfortunately I couldn’t get it to work. Whether this is a temporary problem or a limitation with Office 365 (as opposed to on-prem Sharepoint) I don’t know… if someone knows how to make it work, though, I’d be much obliged if you could leave a comment.

UPDATE: First of all, if you can’t see the survey don’t worry – the service seems to be very unreliable. Secondly I’ve got the OData feed working now and will blog about it later.

Building a Simple BI Solution in Excel 2013, Part 2

In part 1 of this series I showed how to build a BI dashboard with UK weather data in Excel 2013. What I’m going to do now is show, very quickly, how this dashboard can be shared with other people via a web browser.

Before I do that, though, an aside: I found a bug in the preview version of Excel 2013 which means that the dashboard I built in the previous post won’t work with what I’m going to describe here. It’s related to how I imported the data – in my last post I imported data from the Azure DataMarket from the Data tab, and what I’ve had to do for this post is import data from inside the PowerPivot window instead. In a way it’s a good thing I did this because the UI for importing data from the Azure DataMarket in PowerPivot is much better than what I showed before (although it too is quite buggy at the moment). No more fumbling around for account keys and OData queries in the way that Rob Collie complains about here, it’s all handled from within the wizard. All you need to do is pick a data set:

image

And then check the tables you want and optionally apply filters to certain columns that support it:

image

Anyway, with my workbook rebuilt, the next thing I’m going to do is save it. There’s actually a lot more to saving in Office 2013 than you might expect, and certainly lots of different options for places to save stuff too, so I recommend you read this post to get a feeling of what’s possible. I’ve signed up for the Office 365 Professional Plus Preview which means I have access to all kinds of cloud-based services including Sharepoint in the cloud, so I can save my workbook up to Sharepoint:

image

I can then go to my Sharepoint site on another computer and view and interact with my dashboard using Office Web Apps in the browser:

image

image

Yes, that’s my Power View dashboard in a web browser – and I can change filters and it all works! Here’s my PivotTable:

image

I can not only drill down, but I can even change what’s on rows and columns by dragging and dropping fields in the Field list in the usual way. It’s a bit slow and (again) a bit buggy at the moment, but by RTM I can see this being a big selling point: anyone with Office 365 will have in place everything they need not only for BI on the desktop, but for sharing their reports over the web (though data refresh may be a problem here). I’m very impressed, and it’s good to see such a strong answer to the question I asked here last year.

Finally, the question I know you’re all dying to ask. Does this work on an iPad? Well, yes and no. The PivotTable works properly and there’s no loss of interactivity, although I’d say that the touch interface doesn’t work all that smoothly yet:

iPad1

Unfortunately the Power View sheet doesn’t work at all – no Silverlight!

iPad2

Oh well, maybe that was too much to ask for… this is clearly not the Microsoft mobile BI solution that was promised at PASS last year. That’s not to say it isn’t useful though – there’s still some good stuff you can do with PivotTables and regular Excel sheets. I’m still very happy with what’s been delivered so far!

Building a Simple BI Solution in Excel 2013, Part 1

nsurprisingly there’s been a lot of interest in Office 2013 since it was announced yesterday, and I’m certainly very excited by all of the new BI features in it. Luckily I wasn’t working today so I had the chance to have a proper play with it, and I thought it would be useful to walk through the process of building a simple BI solution in Excel 2013 to show off some of the new features.

Let’s start with a blank workbook:

image

Some people don’t like the new look for Office, but I quite like it. You can even set custom backgrounds: you may just be able to see some wispy clouds in the top right-hand corner of the screenshot above. But anyway, to business. To import some data, I first need to go to the Data tab as normal:

image

As you can see there are some new options available here, and I’m going to go to the Windows Azure Marketplace. Actually, I’m going to cheat a little and just say that I’m going to import the UK weather forecast from 12:00am today (July 17th) along with some related tables as described in this blog post. The UI for the import wizard is plain but functional:

image

Interestingly, the data is always imported at the end of the wizard even if I check the ‘Only Create Connection’ option on the last step of the wizard.

Once the data from all the tables has been imported, I need to specify some relationships. I can either do this by clicking on the Relationships button in the Data tab or (better still) going to the PowerPivot window and using the Diagram View. Now as I said yesterday, xVelocity in-memory database is now integrated into Excel but I still need to use the PowerPivot addin in some cases; The PowerPivot addin comes bundled with Excel 2013 but isn’t enabled by default, so in order to use it I first need to enable it; the steps to do this are detailed here. I can then click on the Manage button in the PowerPivot tab to open the PowerPivot window:

image

And then build some relationships between my tables in Diagram View, which can be reached by clicking on the small Diagram button in the very bottom right hand corner of the PowerPivot window. Once in the Diagram View, building relationships is simply a matter of dragging one column onto another:

image

I can also build hierarchies here; Duncan Sutcliffe shows how to do this here, and also how to use hierarchies in Power View which is something new. I’ve created a hierarchy going from Region to Weather Station.

With that done I can of course build a PivotTable. The first thing I’ve noticed is that there isn’t now a separate type of PivotTable for PowerPivot, which is kind of a shame because this means that you don’t seem to be able to create Slicers in the Field List any more, and have to go up to the Ribbon. It’s good for consistency though I suppose. Measure aggregation and number formats are all handled in the Value Field Settings dialog:

image

There’s a new type of Slicer available too for dates called the TimeLine which Raphael has a lot of good detail on here.

Here’s what the new Quick Explore option, which provides some options on where to drill to and for charts to create, looks like when I hover over a Region on Rows:

image

Really, though, the thing to do is to create a Power View sheet. This can be accomplished by going to the Insert tab and clicking the Power View button:

image

This is where things get exciting. Power View in Excel now supports maps and because my weather data contains the latitude and longitude of all of the weather stations in the UK it’s very easy to plot each weather station on a map and visualise the temperature and predicted weather for each station. To do this I just need to drop the Region Name column onto the Filters selection (choosing a region reduces the number of Weather Stations displayed down to a manageable number for the purposes of this demo), then drop the Latitude and Longitude columns onto the main canvas, turn the resulting table into a map, and then set the size of the markers to show temperature and the colour to show the type of weather:

image

I’ll be speaking at SQL South West this week so what’s the weather like down there at the moment?

image

From this I can see that today there’s fog in the Scilly Islands (shown by the green marker in the bottom left hand corner) and that it’s very cold and foggy in Liscombe (shown by the small red marker in the middle of the map). This dashboard was ridiculously easy to create, by the way, and I am already completely in love with Excel 2013 simply because of Power View – it’s a killer feature in my opinion.

At this point we’ve got a working self-service BI solution, made very quickly in Excel 2013 and it’s something that every competent Excel user would be able to achieve.

In Part 2, you’ll see how you can share this solution with your co-workers in the Office Web App.

What Are The Big Changes In Excel 2013 For BI?

As you may already have read, the first public preview for Office 2013 is now available and there’s lots of new BI functionality in there – see here for an overview. Here’s a quick summary of what the two really important changes are:

  • PowerPivot has been integrated into Excel, kind of. This means that the xVelocity (aka Vertipaq) engine is now native to Excel, and you can do all the basic PowerPivot stuff like loading vast amounts of data from multiple data sources and querying it via PivotTables directly in Excel, without installing any extra addins. PowerPivot does still exist as an optional extra however: you need it if you want to use the more advanced functionality that exists in PowerPivot today, such as filtering data before import, using diagram view, defining hierarchies and perspectives and so on.
  • Power View has also been integrated into Excel: Power View reports become a new type of sheet inside a workbook, and you can use it against data held in the integrated xVelocity/PowerPivot engine; I’m not clear yet whether it will work on a SSAS 2012 Tabular model (and at some point a SSAS Multidimensional model, once support for DAX on Multidimensional models arrives) but I hope it does. No more need to moan about Power View being tied to Sharepoint!

There are a whole bunch of other BI-related changes in Excel which I’ll try to summarise in another post soon (stuff like the suggestions for charts and PivotTables, flash fill, timeline slicer). However I think that the two changes above represent a master-stroke on the part of Microsoft: they make Excel 2013 a serious contender in the self-service BI tool stakes. Certainly, other vendors will be quick to point out the features they have and that Excel doesn’t, and dedicated BI vendors will always be able to add new features faster and more frequently than Excel, but that’s not the point. It won’t happen overnight but at some point every company will upgrade to Office 2013 and when they do, all users will have a BI tool on their desktops which is vastly more capable than Excel today and will be good enough for the majority of BI scenarios – which means that the need to even look at third party tools will disappear.

UPDATE A few clarifications:

  • The PowerPivot addin, while still an addin, comes bundled with Excel – there’s no separate download
  • As the comments below show, and I can confirm, Power View does work with SSAS 2012 Tabular models

I’ll be posting more details throughout the day on Twitter as I play with the new preview, and will post something more substantial here later

PowerPivot Course In London This October

I’m pleased to announce a new addition to the Technitrain course catalogue: along with Bob Phillips (a UK-based Excel MVP) I’ll be teaching a PowerPivot course in London this October. Full details and registration can be found here:
http://www.technitrain.com/coursedetail.php?c=18&trackingcode=CWB

The course is the one written by Marco and Alberto to tie in with their excellent book “PowerPivot for Excel 2010: Give Your Data Meaning”, and is suitable for BI professionals and Excel professionals. It costs £499 + VAT if you book before August 31st, and £549 + VAT after that.

Other courses coming up this autumn include: