Category: Office 2013

Dynamic DAX Query Tables in Excel 2013

PivotTables are all well and good, but sometimes when you’re building reports you just want a plain old list of things. Excel tables are perfect for this, and in Excel 2013 you can bind a table to the results of a static DAX query against the Excel Data Model. Unfortunately it’s not possible to make this query dynamic without a bit of VBA – so in this post I’ll show you how to do it.

Before I start, though, you may be thinking “What’s the point of this?”. After all, if you have too much data for the native Excel table functionality to handle, you can always use the Excel Data Model and make a PivotTable look just like a table, and when you do that you can use filters, slicers and so on to control what gets displayed. This is certainly a valid approach but the big disadvantage of a PivotTable is that it doesn’t always give you the best possible performance because of the way it generates its MDX, and because DAX queries are anyway faster than MDX queries for this kind of detail-level reporting. For large tables with lots of columns then a hand-rolled DAX query might give you significantly better performance than a PivotTable, as well as more control over the filtering logic.

Let’s look at a worked example…

Step 1: Import some data into a table

For my example, I have imported the DimDate table from the Adventure Works DW database in SQL Server into a table in Excel.

image

The key thing to remember at this point is to make sure you check the box to add the data to the Excel Data Model:

image

Step 2: Define a DAX query for this table

Kasper shows here how to use a static DAX query to populate a table in Excel, so I won’t repeat what he says. All I’ve done in my example is to change the table to use the following DAX query:

evaluate DimDate

…which returns the whole contents of the DimDate table, so in fact at this point the table looks exactly the same as it did before I made this change.

image

image

Step 3: Add some UI to allow the user to filter the data

Now I want the user to be able to filter this table in two ways:

1. By using a slicer to control which days of the week are displayed

2. By entering a value into a cell, and filtering the table so only the rows where the day number of the month is greater than that value

Here’s what this looks like:

image

I’ve also added a ‘Run Report’ button onto the worksheet for the user to press when they want to refresh the data in the query

Step 4: Use VBA to dynamically generate the query used by the table

The challenge is now to take the selection in the slicer and the value entered for the day number of month filter and use that to construct a DAX query.

Here’s an example of what one of these DAX queries might look like:

evaluate
Filter(
DimDate
, DimDate[DayNumberOfMonth]>21
&& (DimDate[EnglishDayNameOfWeek]=”Monday” || DimDate[EnglishDayNameOfWeek]=”Saturday”))
order by DimDate[DateKey]

Here I’m filtering the DimDate table so that the only rows displayed are where day number of month is greater than 21, and day name of week is either Monday or Saturday. If you’re interested in learning more about writing DAX queries, check out the series of blog posts I wrote on this topic here.

Paul te Braak has a great post here on how to work out what has been selected in a slicer using VBA, and I need to acknowledge the fact I’ve borrowed some of his code! Here’s my VBA routine, called by the button on the worksheet, to build and run the query:

Sub RunReport()
    Dim SC As SlicerCache
    Dim SI As SlicerItem
    Dim SelectedList As String
    Dim DayNumberOfMonthFilter As String
    Dim DAXQuery As String
    Dim DemoWorksheet As Worksheet
    Dim DAXTable As TableObject
    Set DemoWorksheet = Application.Worksheets("TableDemo")
    'Find the value of the cell containing the Day Number Of Month filter value
    DayNumberOfMonthFilter = DemoWorksheet.Range("DayNumberOfMonthFilter").Value
 
    'Find what is selected in the slicer Slicer_EnglishDayNameOfWeek
    Set SC = ActiveWorkbook.SlicerCaches("Slicer_EnglishDayNameOfWeek")
    SelectedList = ""
 
    'Loop through each item in the slicer and if it is selected
    'add it to a string that will be used in the filter condition
    For Each SI In SC.SlicerCacheLevels(1).SlicerItems
        If SI.Selected Then
            If SelectedList <> "" Then
                SelectedList = SelectedList & " || "
            End If
            SelectedList = SelectedList & "DimDate[EnglishDayNameOfWeek]=""" & SI.Caption & """"
        End If
    Next
    'Construct the DAX query
    DAXQuery = "evaluate Filter(DimDate, DimDate[DayNumberOfMonth]>" & DayNumberOfMonthFilter
    DAXQuery = DAXQuery & " && (" & SelectedList & ")) order by DimDate[DateKey]"
    'Bind the table to the DAX query
    Set DAXTable = DemoWorksheet.ListObjects("Table_DimDate").TableObject
    With DAXTable.WorkbookConnection.OLEDBConnection
        .CommandText = Array(DAXQuery)
        .CommandType = xlCmdDAX
    End With
 
    'Run the query
    ActiveWorkbook.Connections("ModelConnection_DimDate").Refresh
End Sub

 

And so there we go, a dynamic DAX table report in Excel 2013. If you’d like to download my example and check it out in detail, you can get hold of it here.

Office 2013, Office 365 Editions and BI Features

By now you’re probably aware that Office 2013 is in the process of being officially released, and that Office 365 is a very hot topic. You’ve probably also read lots of blog posts by me and other writers talking about the cool new BI functionality in Office 2013 and Office 365. But which editions of Office 2013 and Office 365 include the BI functionality, and how does Office 365 match up to plain old non-subscription Office 2013 for BI? It’s surprisingly hard to find out the answers…

For regular, non-subscription, Office 2013 on the desktop you need Office Professional Plus to use the PowerPivot addin or to use Power View in Excel. However there’s an important distinction to make: the xVelocity engine is now natively integrated into Excel 2013, and this functionality is called the Excel Data Model and is available in all desktop editions of Excel. You only need the PowerPivot addin, and therefore Professional Plus, if you want to use the PowerPivot Window to modify and extend your model (for example by adding calculated columns or KPIs). So even if you’re not using Professional Plus you can still do some quite impressive BI stuff with PivotTables etc. On the server, the only edition of Sharepoint 2013 that has any BI functionality is Enterprise Edition; there’s no BI functionality in Foundation or Standard Editions.

[For those of you thinking of upgrading from Excel 2010 PowerPivot to Office 2013, Marco has all the details on compatibility of PowerPivot workbooks across different versions here.]

Office RT, which runs on Windows RT, has several limitations on its BI functionality: there’s no PowerPivot, Power View or Excel Data Model. Luckily, Kasper has summarised what it does do in a blog post here, so I won’t repeat what he says.

Moving on to 2013 functionality in Office 365, and specifically BI in Sharepoint Online, things get more complicated. Although feature support information for Office 365 is on Technet here, the best place to start is Andrew Connell’s blog post and corresponding feature matrix that is viewable through (appropriately enough) the Excel Web App. The feature matrix makes it very easy to filter Office 365 features by workload so you only see the BI-related ones:

image

image

As you can see, the short answer is that you need either Office 365 E3 or E4, or SharePoint Online Plan 2 to get BI functionality. The Office Professional Plus, E3 and E4 plans are also the only plans to include subscriptions to the desktop versions of Office Professional Plus, and they allow it to be installed on up to 5 machines per user. The other thing you’ll notice is that PerformancePoint is not available at all in Office 365 (read into that what you will); it is of course available in Sharepoint 2013 Enterprise Edition on-premises.

There are other functionality differences between Sharepoint Online in Office 365 and on-premises Sharepoint too. The details are here, but the important ones are:

  • At least for the moment, Excel workbooks can be no larger than 10MB
  • The Excel Data Model will only refresh successfully if it sources data from the workbook itself; no external data sources are supported (though again I’d be surprised if that restriction isn’t lifted in the future)
  • There is no PowerPivot for Sharepoint functionality such as the Gallery, usage reporting or scheduled data refresh.

These are quite significant restrictions, it’s true, but if you are a purely self-service BI shop and you just want to use Sharepoint Online to publish PivotTable or Power View reports that don’t need to be refreshed (or can be refreshed manually on the desktop and then uploaded) then this functionality should be sufficient. This is the kind of scenario I showed here, and I think a lot of customers with no existing BI will still be impressed with it; obviously it’s a problem if you want to do any kind of corporate BI.

BUT. At the time of writing the Enterprise plans for Office 365 haven’t been fully updated for Office 2013 functionality, so all this BI functionality isn’t actually available yet to most subscribers. This means that the desktop versions of Office you can download are still 2010 and not 2013; online, while you can get the latest Sharepoint features if you’re part of the Office 365 Preview, if you’re currently an Office 365 subscriber you’re probably still on Sharepoint 2010. The official line on when the upgrade to 2013 functionality will take place is a bit vague – it will take place “in the course of 2013” – and there seem to be a few upset customers out there (see here for example). February 27th seems to be a significant date.

Finally, apart from Office 365 it’s also possible to view Excel workbooks via SkyDrive. However pretty much no BI functionality is available when you do this: no Excel Data Model, no external connections, no Power View, just the ability to view (and not alter) PivotTables. These restrictions seem to be more or less the same if you use just the Office Web Apps server on-premises without Sharepoint 2013 – see the relevant table here for details.

In summary: my head hurts! All these editions and licences… it would be nice if it was less complicated.

UPDATE: some new information, and some clarifications, since I first wrote this post

1) Office Professional Plus 2013 will be available via Office 365 on February 27th 2013. The cheapest subscription option that includes Excel on the desktop with PowerPivot and Power View is, as far as I can see, this one, an Office Professional Plus subscription, that is included in the E3 and E4 plans.

2) Office Professional Plus is only available via Open, Select or EA licensing (see http://www.microsoft.com/en-gb/licensing/default.aspx for more details on what these options are). Excel 2013 standalone is only available via Open or Select. This means that no regular retail editions of Excel include PowerPivot or Power View, you can only get them through a Volume Licence Agreement or Office 365 (ie you need to be working for a big company with deep pockets unless you buy yourself an Office Professional Plus, E3 or E4 Office 365 subscription); compare this with PowerPivot for Excel 2010 worked with any edition of Excel. Existing PowerPivot users are not particularly happy about this when they find out: see here and here for example. Is this a good strategy? Hmm…

3) Right now, I’m told there is a problem with how the addins are packaged with Excel 2013 standalone which will be addressed in a future update.

UPDATE  2: I’ve just found out that standalone Power View is not supported at all in Sharepoint Online/Office 365. Only Power View sheets inside Excel workbooks are supported.

UPDATE 3: Power Pivot is now available in standalone versions of Excel too as of August 2013 – http://www.powerpivotblog.nl/power-pivot-and-power-view-now-available-in-excel-stand-alone

UPDATE 4: This blog does not cover the BI features that are available in Power BI. A Power BI subscription is an add-on to an Office 365 subscription and gives you extra functionality. You can find out about the licensing here and I’ve blogged about what it gives you here, here and here.

Some thoughts on what Office 2013 means for Microsoft BI

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?

Office 2013 Store and BI

By now you’ve probably already seen that the new Office Store, where you can get hold of apps for Office and Sharepoint, is now open. If you haven’t, check out the following blog posts:
http://blogs.office.com/b/office-next/archive/2012/08/06/introducing-apps-for-the-new-office-and-sharepoint-and-the-office-store.aspx
http://blogs.msdn.com/b/officeapps/archive/2012/08/06/173-173-the-office-store-is-now-open.aspx
http://www.theregister.co.uk/2012/08/07/microsoft_apps_for_office/

The implications for BI are obvious: new apps for data visualisation (along the lines of what’s available in  Sparklines for Excel maybe; perhaps also the long-lost decomposition tree from Proclarity?), analysis, importing and exporting data. I’ve already downloaded and had a play with the Bubbles app, which is quite fun:

image

Will it take off? Who knows; it’ll certainly be a while before enough people are on Office 2013 before we can tell. Will anyone want to pay for apps? Again, who knows – I wonder if we’ll see something similar to OLAP PivotTable Extensions appear, and if free, open source apps will kill the paid app market at least in some areas? If you’ve got any ideas for a BI-related app, please leave a comment!

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