What’s New In The Excel 2016 Preview For BI?

Following on from my recent post on Power BI and Excel 2016 news, here are some more details about the new BI-related features in the Excel 2016 Preview. Remember that more BI-related features may appear before the release of Excel 2016, and that with Office 365 click-to-run significant new features can appear in between releases, so this is not a definitive list of what Excel 2016 will be able to do at RTM but a snapshot of functionality available as of March 2015 as outlined in this document and which I’ve found from my own investigations. When I find out more, or when new functionality appears, I’ll either update this post or write a new one.

Power Query

Yesterday, in the original version of my post, I mistakenly said that Power Query was a native add-in in Excel 2016: that’s not true, it’s not an add-in at all, it’s native Excel functionality. Indeed you can see that there is no separate Power Query tab any more, and instead there is a Power Query section on the Data tab instead:


Obviously I’m a massive fan of Power Query so I’m biased, but I think this is a great move because it makes all the great Power Query functionality a lot easier to discover. There’s nothing to enable – it’s there by default – although I am a bit worried that users will be confused by having the older Data tab features next to their Power Query equivalents.

There are no new features for Power Query here compared to the latest version for Excel 2013, but that’s what I expected.

Excel Forecasting Functions

I don’t pretend to know anything about forecasting, but I had a brief play with the new Forecast.ETS function and got some reasonable results out of it as seen in the screenshot below:


Slicer Multiselect

There’s a new hammer icon on a slicer, which, when you click it, changes the way selection works. The default behaviour is the same as Excel 2013: every time you click on an item, that item is selected and any previous selection is lost (unless you were holding control or shift to multiselect). However with the hammer icon selected each new click adds the item to the previously selected items. This is meant to make slicers easier to use with a touch-screen.


Time Grouping in PivotTables

Quite a neat feature this, I think. If you have a table in the Excel Data Model that has a column of type date in it, you can add extra calculated columns to that table from within a PivotTable to group by things like Year and Month. For example, here’s a PivotTable I built on a table that contains just dates:


Right-clicking on the field containing the dates and clicking Group brings up the following dialog:


Choosing Years, Quarters and Months creates three extra fields in the PivotTable:


And these fields are implemented as calculated columns in the original table in the Excel Data Model, with DAX definitions as seen here:


Power View on SSAS Multidimensional

At-bloody-last. I haven’t installed SSAS on the VM I’m using for testing Excel 2016, but I assume it just works. Nothing new in Power View yet, by the way.

Power Map data cards

Not sure why this is listed as new in Excel 2016 when it seems to be the same feature that appeared in Excel 2013 Power Map recently:


Power Pivot

There isn’t any obvious new functionality in the Power Pivot window, but it’s clear that the UI in general and the DAX formula editor experience in particular has been improved.


Suggested Relationships

When you use fields from two Excel Data Model tables that have no relationship between them in a PivotTable, you get a prompt to either create new relationships yourself or let Excel detect the relationships:


Renaming Tables and Fields in the Power Pivot window

In Excel 2013 when you renamed tables or fields in the Excel Data Model, any PivotTables that used those objects had them deleted. Now, in Excel 2016, the PivotTable retains the reference to table or field and just displays the new name. What’s even better is that when you create a measure or a calculated column that refers to a table or column, the DAX definition of the measure or calculated column gets updated after a rename too.


There are lots of new DAX functions in this build. With the help of the mdschema_functions schema rowset and Power Query I was able to compare the list of DAX functions available in 2016 with those in 2013 and create the following list of new DAX functions and descriptions:

DATEDIFF			Returns the number of units (unit specified in Interval) 
			between the input two dates
CONCATENATEX		Evaluates expression for each row on the table, then 
			return the concatenation of those values in a single string 
			result, separated by the specified delimiter
KEYWORDMATCH		Returns TRUE if there is a match between the 
			MatchExpression and Text. 
ADDMISSINGITEMS		Add the rows with empty measure values back.
CALENDAR			Returns a table with one column of all dates between 
			StartDate and EndDate 
CALENDARAUTO		Returns a table with one column of dates 
			calculated from the model automatically
CROSSFILTER		Specifies cross filtering direction to be used in 
			the evaluation of a DAX expression. The relationship is 
			defined by naming, as arguments, the two columns that 
			serve as endpoints
CURRENTGROUP		Access to the (sub)table representing current 
			group in GroupBy function. Can be used only inside GroupBy 
GROUPBY			Creates a summary the input table grouped by the 
			specified columns
IGNORE			Tags a measure expression specified in the call to 
			SUMMARIZECOLUMNS function to be ignored when 
			determining the non-blank rows.
ISONORAFTER		The IsOnOrAfter function is a boolean function that 
			emulates the behavior of Start At clause and returns 
			true for a row that meets all the conditions mentioned as 
			parameters in this function.
NATURALINNERJOIN		Joins the Left table with right table using the 
			Inner Join semantics
NATURALLEFTOUTERJOIN	Joins the Left table with right table 
			using the Left Outer Join semantics
ROLLUPADDISSUBTOTAL		Identifies a subset of columns specified 
			in the call to SUMMARIZECOLUMNS function that should be 
			used to calculate groups of subtotals
ROLLUPISSUBTOTAL		Pairs up the rollup groups with the column 
SELECTCOLUMNS		Returns a table with selected columns from the table 
			and new columns specified by the DAX expressions
SUBSTITUTEWITHINDEX		Returns a table which represents the semijoin of two 
			tables supplied and for which the common set of 
			columns are replaced by a 0-based index column. 
			The index is based on the rows of the second table 
			sorted by specified order expressions.
SUMMARIZECOLUMNS		Create a summary table for the requested 
			totals over set of groups.
GEOMEAN			Returns geometric mean of given column 
GEOMEANX			Returns geometric mean of an expression 
			values in a table.
MEDIANX			Returns the 50th percentile of an expression 
			values in a table.
PERCENTILE.EXC		Returns the k-th (exclusive) percentile of 
			values in a column.
PERCENTILE.INC		Returns the k-th (inclusive) percentile of 
			values in a column.
PERCENTILEX.EXC		Returns the k-th (exclusive) percentile of an 
			expression values in a table.
PERCENTILEX.INC		Returns the k-th (inclusive) percentile of an 
			expression values in a table.
PRODUCT			Returns the product of given column reference.
PRODUCTX			Returns the product of an expression 
			values in a table.
XIRR			Returns the internal rate of return for a schedule of 
			cash flows that is not necessarily periodic
XNPV			Returns the net present value for a schedule of cash flows

Plenty of material for future blog posts there, I think – there are lots of functions here that will be very useful. I bet Marco and Alberto are excited…


We now have support for working with Power Query in VBA.

Analysis Services Multidimensional Now Works With Power View–And Why That’s Important

By now you may have already heard the news that, as part of SQL Server 2012 SP1 CU4, new functionality has been released that means that Power View now works with Analysis Services Multidimensional (ie cubes, as opposed to the Tabular Model, which always worked with Power View). I won’t bother to repeat the technical details which you can read about here:

…but the main points are that Analysis Services Multidimensional can now be queried in DAX, and this plus some tweaks to Power View mean that the two can be used together for the first time. Unfortunately Power View in Excel 2013 doesn’t work with Analysis Services Multidimensional yet, but I hope that will also be fixed very soon.

I’ve been playing with the public CTP of this for a while and done a few presentations with it, and from a technical point of view it’s a solid bit of work by the Analysis Services dev team. It just works, and while there are a few limitations they’re trivial. Arguably it should not have been necessary to do it in the first place – why didn’t Power View speak MDX when it was built, which would have meant it could have queried both Tabular and Multidimensional? But it’s here now, and that’s what counts. It also opens up some interesting possibilities for using DAX queries to create detail-level reports on cubes, and also for defining DAX calculations inside those queries.

However I think its real importance is strategic. This is the first significant bit of new functionality in Analysis Services Multidimensional for a long while, and it acts as a bridge between the classic SQL Server BI stack that most of us are using and the brave new world of Office/Sharepoint-led BI. It is also the first time in a long time that Analysis Services Multidimensional users have had a dedicated client tool for data analysis from Microsoft that isn’t Excel. Don’t get me wrong, I love Excel as a client tool for SSAS but I’ve always thought (and I think industry trends over the last few years support this view) that even though Excel is a great way to bring data analysis to the masses, there’s still an important niche among power users for a more advanced data analysis and data visualisation tool.

You may be thinking at this point that pretty graphs and charts are all very well, but your users don’t need anything other than the SSRS reports and basic PivotTables that they’ve been using for the last few years. I say that you ignore Power View at your own risk. Microsoft’s competitors in the BI space are hungry for new customers and are interested in migration projects. You might well arrive at the office next Monday morning to find that there’s a new CFO who used QlikView in his last job, and who wants the same pretty graphs and charts he had there again. It’s not going to be any use arguing that you’ve spent years developing this cube, that it’s lightning fast and has all sorts of tricky business logic coded into thousands of lines of MDX – if your BI solution’s user interface looks and feels dated, then whatever its technical merits it will have the musty smell of legacy software about it. If, however, you can fire up a VM with Sharepoint 2013 and Power View on and show off some slick dashboards created from your existing cubes, even if this is something the majority of your end users wouldn’t really be interested in (and you may be wrong, they might love it), you’re going to be showing the business two important things:

  • Microsoft can do sexy dashboards and visualizations too, and while they come at a price, that price is probably a lot less than it would cost to rip and replace what you’ve got with a competitor’s software. So the option’s there if you want to spend the money and do the upgrade.
  • Analysis Services cubes are not a dead-end, and Microsoft has made a significant investment here to prove this. I’d still love to see a coherent roadmap that explains where Microsoft is heading with its BI tools and how it expects its existing customers to get there, but I doubt we’ll get one. This functionality was, however, delivered in response to popular demand, so I’m hopeful that if we as customers can make our voices heard as to what we want in the future then we can influence Microsoft’s direction.

So go forth and Power View. Both Rob Kerr and Koen Verbeeck have recently published some excellent, detailed guides to setting up a Sharepoint 2013 demo environment; you have no excuse for not testing this out and being ready to face the competition.

Parameterising PowerPivot Connection Strings in Excel 2013

One of the things I’ve always wanted to do with PowerPivot is to parameterise the connections used to import data. Despite PowerPivot’s ability to handle massive data volumes, most of the time you want your end users to import only the data they actually need – and for them to be able to specify filters on the data themselves somehow. The only way to do this in Excel 2010 was to have them go into the PowerPivot model and change the connection themselves, which is not very user-friendly, but now we have a proper PowerPivot object model in 2013 we can modify connection strings in VBA (so we can take filtering information direct from the worksheet) and this post shows how. I’d like to acknowledge the help I got from Kasper’s post here which covers very similar ground, but I came across a few interesting things while building the example here so I thought it was worth a post on its own.

Let’s say we want to build our own Bing search engine client in Excel 2013, where a user can enter a search term in a cell, click a button to run the search and then not only be able to see the search results but analyse them in PowerPivot and Power View. The first step is to sign up to the Bing search API in the Azure Marketplace and then import some search results with a hard-coded search term (this tutorial will help if you’re not sure how to do this); I used Bing image search to return some results with urls pointing to images on the web. This will create a connection in the Workbook which we can then modify programmatically. However I ran into a problem at this point: I found that only connections created on the Data tab on the ribbon can be modified in VBA, whereas connections created in the PowerPivot addin cannot. This means I had to click here:


..to create my connection, and NOT here:


When you open connections created in the PowerPivot window in the Connections dialog from the Data tab, you see the following message: “Some properties cannot be changed because the connection was modified using the PowerPivot Add-In”


Trying to edit connections created in PowerPivot using VBA just gave me an error.

Not much of a issue though. With the connection in place, here’s the VBA code that’s needed to alter the connection string and replace the search term with a value from a cell in the worksheet:

Sub RunImageSearch()
Dim mdl As ModelTable
Dim wcon As WorkbookConnection
Dim cs As String
Dim ss As String
Dim azurekey As String
azurekey = "Insert your Azure Marketplace account key here"
Set mdl = ActiveWorkbook.Model.ModelTables("Image")
Set wcon = mdl.SourceWorkbookConnection
cs = "DATAFEED;" & _
 "Data Source=https://api.datamarket.azure.com/Bing/Search/v1/" & _
 "Image?Query=%27ReplacePlaceholder%27;" & _
 "Namespaces to Include=*;Max Received Message Size=4398046511104;Integrated Security=Basic;" & _
 "User ID=AccountKey;Password=" & azurekey & _
 ";Persist Security Info=false;" & _
 "Base Url=https://api.datamarket.azure.com/Bing/Search/v1/Image?Query=%27ReplacePlaceholder%27"
ss = WorksheetFunction.EncodeURL(CStr(ActiveWorkbook.Sheets("Search Term").Cells(2, 3).Value))
wcon.DataFeedConnection.Connection = Replace(cs, "ReplacePlaceholder", ss)
End Sub


Three points to note here:

  • If you’re copying this code, you need to enter your own Azure Marketplace account key in the place specified
  • The search term needs to be url encoded, and luckily there’s a new function to do this in 2013: EncodeURL()
  • If you’re using a different data source then obviously the connection string will be different. Also, because I’m using data from the Azure Marketplace my SourceWorkbookConnection object has a connection of type DataFeedConnection – other data sources will have different connection types, so check the value returned by SourceWorkbookConnection.Type (the list of values in the XLConnectionType enumeration on the web is, at the time of writing, out of date but Object Explorer is up-to-date)

That’s all there is to it. The macro can be bound to a button on the worksheet like so:


And we can then do all kinds of clever things to analyse the search results. For example, with the image search results we can show thumbnails in a Power View report (see Jeremy Kashel’s post for details):


You can download the whole example workbook here, although again you’ll have to edit the VBA to enter your own Azure Marketplace account key if you want it to work.

Power View on SSAS Multidimensional

Earlier this week I mentioned that the announcement about Power View working on SSAS Multidimensional had leaked out. There was a full session on it yesterday giving all the details and I thought I’d summarise them here for your enjoyment. Here are the main points:

  • This has not shipped yet – it is not in SSAS 2012 SP1. No release date had been announced but it sounds like it is coming very soon.
  • It will come as a new build of SSAS 2012, so to use Power View you will need to upgrade to that build and no earlier version of SSAS will work.
  • It will also require an update to Power View. This means:
    • Power View in Sharepoint (ie SSRS) will need to be updated too
    • Power View in Excel will still not work initially, even when the new build of SSAS has been released. We’ll have to wait for another update for Office (perhaps a service pack?) before Power View in Excel works on SSAS Multidimensional too.
  • In technical terms, what has happened is that SSAS Multidimensional now
    • Exposes Tabular metadata
    • Can be queried in DAX
  • There is no translation going on from DAX to MDX, SSAS Multidimensional supports DAX natively.
  • The consensus in the session room was that the SSAS team had done a really good job thinking through all the details of how this will work. In general your existing cube will not need to be redesigned, it will just work and all your queries and calculations will return the results you expect. In the session a lot of time was spent showing how things like default members will work.
  • But some things are not supported: if you have named sets or actions they will not be exposed; in some scenarios calculated members on non-measures dimensions will not be exposed either, but time utility dimensions should work; and cell security is not supported either – if a user is a member of a role that has cell security applied, they will not be able to run DAX queries.
  • There’s a minor new feature in SSAS that allows you to mark an attribute as containing a URL for an image, so that Power View can display the images automatically.

UPDATE: the public CTP is now available http://blogs.msdn.com/b/analysisservices/archive/2012/11/29/power-view-for-multidimensional-models-preview.aspx

Thoughts on the PASS Summit 2012 Day 1 Keynote

Normally I’d rush to blog about the announcements made in the keynotes each day at the PASS Summit, but this year I had a session to deliver immediately afterwards and once I’d done that I saw Marco had beaten me to it! So, if you want the details on what was announced in today’s keynote I’d advise you to read his post here:

I can’t not comment on some of these announcements though, so here (in no particular order) are some things that occurred to me:

  • The first public sighting of Power View on Multidimensional raised the biggest cheer of the morning, which surprised even me – I didn’t realise there were so many SSAS fans in the audience. I’m certainly very pleased to see it, even if it isn’t shipping right now (it’s not in SP1 either). Part of why I’m pleased is that all too often Microsoft BI has been good at building amazing new products but then forgetting about the migration path for its existing customers: think of the Proclarity debacle, and more recently I’ve heard a lot of complaints about the abandonment of Report Models. I suspect this is because Microsoft is not like most other software companies in that it doesn’t do much direct selling itself, but lets partners do the selling for it, and when partners get stick from customers over issues like Proclarity migration then the partners have no leverage over Microsoft to make it deal with the problem. Power View on Multidimensional is a welcome exception to this pattern, and I’d like to see more consideration given to this issue in the future even if it comes at the expense of developing cool new features.
  • The PDW V2 news is interesting too. It was clearly stated that Polybase will, initially allow TSQL to query data in Hadoop but that other data sources might be supported in the future. I wonder what they will be? DAX/Tabular perhaps? Or something more exotic – wouldn’t it be cool if you could query the Facebook graph or Twitter or even Bing directly from TSQL? I’m probably letting my imagination run away with me now…
  • The other thing that popped into my mind when hearing about Polybase was that it might be possible, one day, to use SSAS Tabular in DirectQuery mode on top of PDW/Polybase to query data in Hadoop interactively. I know Hadoop isn’t really designed for the kind of response times that SSAS users expect but I’d still like to try it.
  • It hardly seems worth repeating the fact that Mobile BI is very, very late but again it was good to get some details on what is coming. As partners we can deal with the criticism we get from customers and plan better if we have some idea of what will be delivered and the timescales involved, something that has been conspicuously lacking with Mobile BI up to today. To use a current phrase, Microsoft and its partners are “all in this together”, so please, Microsoft, let us help you!

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:


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


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:


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:



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


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:


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


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:


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:


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:


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:


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:


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:


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:


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:


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:


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


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.

%d bloggers like this: