Power BI And SQL Server 2016 BI Announcements At PASS Summit 2015


This year’s PASS Summit is drawing to a close as I write this, and I have to say that the number of Microsoft BI-related announcements made over the last few days has been overwhelming. There have been announcements made via blog posts, such as (shock!) the roadmap blog post:

…which you should probably read before anything else, as well as the following posts which have more details on specific areas:



There have also been a lot of other announcements made in sessions about functionality that will be available at some point in the next few months, including (and in no particular order):

  • The performance problem with Excel subtotals that I described in this blog post: https://blog.crossjoin.co.uk/2011/10/07/excel-subtotals-when-querying-multidimensional-and-tabular-models/ is finally going to be addressed in Excel 2016 in an update that will be available before the end of the year. This is going to solve a lot of people’s performance problems – problems that people may not even realise they had.
  • SSDT for SSAS 2016 will have a script view where you can see all of your DAX calculations in one place
  • SSDT will be getting monthly updates so new functionality can be delivered much more quickly
  • On top of the improvements in SSAS Tabular DirectQuery mentioned in the blog posts above, we’ll also get support for row-level security and calculated columns (but only ones that reference values in the same row of the table that the calculated column is on)
  • SSAS Tabular will also get Translations, but only for metadata and not for data
  • There will be a Power BI Enterprise Gateway, the corporate big brother of the Personal Gateway
  • Datazen will be rolled into SSRS and Datazen reports will be a new ‘mobile’ report type
  • The Power BI mobile app will be able to display these new SSRS mobile reports as well as Power BI reports
  • The Power BI team will be releasing a new custom datavisualisation component every week. We had the new Chiclet slicer this week, which I am already using lots, and in one demo I spotted a Proclarity-style decomposition tree
  • Power BI desktop will work with SSAS Multidimensional as a live data source (ie not through importing data, but running DAX queries in the background) by the end of this year
  • PowerBI.com dashboard tiles will become properly interactive, and you will be able to pin entire reports as well as just individual components to them
  • You’ll be able to embed ranges and charts from Excel workbooks into PowerBI.com reports; integration looks much nicer than the rather basic functionality that’s already there
  • Power Map/3D maps will be embedded in Power BI Desktop and PowerBI.com
  • You’ll be able to run R scripts in Power BI Desktop and display R visualisations in there too
  • There was a demo of an Android(?) phone version of the Power BI mobile app, where when the phone camera saw a QR code it displayed a report for the product that the QR code represented over the camera feed. Virtual reality BI!
  • Power BI Desktop will get a “Get Insights” button that, when pushed, will display a report that does some basic statistical analysis of your data, looking for minimums, maximums, outliers etc
  • The Power BI API will be able to give you a list of reports and their URLs
  • Power BI will soon have its own registration page for applications that use the API; no need to go to the Azure Portal.
  • Synonyms and phrasings for Q&A will be coming to Power BI by the end of the year

I *think* that’s everything, but I may well have missed a few things. Many of the features that were mentioned in passing would have deserved a five-minute slot in a keynote in previous years.

Power BI is finally a commercially viable product and it’s getting even better every week – the competition should be very worried. I’m also really pleased that MS are taking corporate, on-premises BI seriously at last and that SSRS is back in favour (I would have loved more new features in SSAS Multidimensional, but hey, you can’t have everything) – if you’re wondering what the picture at the top of this post is, it’s the cloud and boxed SQL Server “happy together” at last, and it appeared in several MS presentations this week. The box is back! Most importantly, for the first time in a long time, Microsoft has a coherent vision for how all of its BI products should work together, it’s working on new features to make that vision a reality, and it is willing to share it with us as a roadmap.

In summary I can’t remember the last time I felt this positive about the future of Microsoft BI. What MS have achieved over the last year has been remarkable, and it seems like it’s the leadership of James Phillips that has made all the difference – every MS employee I’ve talked to has had good things to say about him and I guess this explains why he got a promotion in the reorg last week. I hope all this continues.

Loading English LiDAR Open Data Into Excel And Power Map Using Power Query

…or, if you want the clickbait title of this post, “Creating cool Minecraft-type landscapes in Power Map”…

I’m a big fan of Owen Boswarva’s UK open data blog, and yesterday this post on English LiDAR data released by the Environment Agency caught my eye:


I won’t repeat what he says because there’s nothing I could add to it, but basically this data is a 3D scan of large parts of England at extremely detailed resolutions. You can download the data from this site:


My first thought after reading this was post was that it should be possible to load this data into Excel (in general) and Power Map (in particular) using Power Query fairly easily. And indeed it was – so in this blog post I’ll show you how.

First of all, here’s a sample of the output. This is a Power Map custom map showing tile SU9090, which shows a section of the M40 motorway and its surroundings near High Wycombe:


Zooming in on an area just below the centre of the above screenshot you can see several distinctively-shaped office buildings, the M40 going across the screen and, on the top right-hand side, a Tesco superstore:


Here’s the same area shown in Bing Maps (http://binged.it/1RkakM7):


Here’s the obligatory video:

How did I do this? The first step is to load the data into the Excel Data Model using Power Query. I created a Power Query function called LoadLiDARData to do this, and you can get a sample workbook with it in from here. There isn’t much to say about the function – it’s not trivial, but not very interesting in itself. To use it, in Excel go to the Power Query tab on the ribbon and open the Workbook Queries pane. To load data from a single .asc file downloaded from the Environment Agency site, right-click to invoke the function and enter the filename (including the full path) of that file. The Power Query Editor window will open but you don’t need to do anything here except click the Close & Load button. I recommend you load the output to the Excel Data Model rather than a table in the worksheet because a single file contains a lot of data!

Next, go to the Insert tab on the Excel ribbon and click the Map button to open Power Map. Then click New Scene\New Custom Map to create a new custom map. You need to make sure that the Min values for the X and Y axis are set to 0, the Max value for X is set to the number of columns and the Max value for Y is set to the number of rows in the data you’re working with. Also, check the Flip Axis button for the Y axis.


Then, on the right-hand side of the screen check the ColumnNumber and RowNumber boxes and set them to be the X Coordinate and Y Coordinate respectively:


Click the Next button, then check Height to use as the height of a bar graph:


Finally – and this is important – go to Settings and adjust the Thickness property appropriately. If you don’t do this you won’t be able to pick out any detail; I have used 18% as a good starting setting. Ticking the Lock current scale box is also a good idea, so that when you zoom in the bars don’t disappear.


This is no more than a proof-of-concept. I’ve only tested my code with a very limited amount of data, and there are a lot of obvious improvements that could be made: for a start, it would be good to convert the grid co-ordinates used to latitude and longitude so that this data can be used with the maps in Power Map. I could also imagine some really useful applications for this, such as combining a map like this with snowfall data and creating an animation showing where snow falls over a landscape during the winter.

Of course a dedicated tool will do a much better job of visualising this data than Excel, but if you have the right edition of Excel 2013 then you can get everything you need to do this for free and in Excel 2016 Power Query and Power Map (as Get & Transform and 3D Maps) are available out-of-the-box. It also sounds like Power Map will be integrated with Power BI at some point too:



UPDATE: Here’s a video of another tour I created using LiDAR data, showing Waterloo Station, the Houses of Parliament and Westminster Abbey in central London

Are You Going To PASS Summit 2015? I Need Your Help!

This year at the PASS Summit I’m delivering a rather ambitious and risky session. Here’s the abstract:

Analyzing Audience Reaction to the PASS Summit Keynote

If you’ve been to PASS Summit before, you know that social media channels such as Twitter go wild during the keynote speeches. Many people comment on what’s being said: Some comments are positive, some … not so positive. How can you make sense of it all? Which announcements did attendees like and which ones didn’t they like? When were the attendees engaged and when were they bored? Who are the thought-leaders in the SQL Server community? Do DBAs behave differently from BI pros?

In this session, you’ll see how a variety of Microsoft tools can be used to analyze this activity:

* Use Bing Pulse to capture real-time audience feedback during the keynote.
* Use Power BI to analyze data from Bing Pulse, Twitter, and other social media sources.
* Use NodeXL to perform network analysis on Twitter data.
* Use Azure Machine Learning to perform sentiment analysis.

As you can see, a important part of this session involves using Bing Pulse (I blogged about it here) to capture feedback on the session as it happens. And this is where I need your help.

If you are attending the PASS Summit 2015 keynote on Wednesday October 28, and you don’t mind clicking a button on your phone/tablet/laptop every minute to indicate what you think about what’s being said, please leave your name and email address here:


It won’t involve much effort, I promise, nothing more than a few clicks throughout the keynote. Your individual opinions will remain private. However I do need to sign up as many people as possible from all parts of the SQL Server Family – DBAs and developers as well as BI Pros – so I can get as much data as possible, and so get some interesting insights into what you’re all thinking.


Drillthrough On Multiselect Now Works In Excel 2016 And SSAS 2016

One unadvertised – but still very welcome – feature of Excel 2016 is that it is now possible to do a drillthrough in a PivotTable when there is a multiselect on a filter or a slicer. It only works if you are using SSAS 2016 on the server, or if you’re querying the Excel Data Model/Power Pivot, because the fix needed changes both in Excel and on the server.

In Excel 2013 and earlier, when you try to do a default drillthrough where there is a multiselect on a filter or a slicer, you get the following error message:


Show Details cannot be executed when multiple items are selected in a report filter or in a slicer. Select a single item for each field in the report filter area and for each slicer connected to this PivotTable before performing a drillthrough.

For drillthrough actions, where there is a multiselect, you won’t see the action listed under the Additional Actions right-click menu at all.

This is the result of two limitations. First, there’s the issue with the MDSCHEMA_ACTIONS schema rowset that I blogged about here. In SSAS 2016 you can now pass in multiple members from the same hierarchy in the COORDINATE restriction column, as shown in this example I captured in Profiler:

<RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis" 
[Measures].[Sum of SalesAmount])

Second, subselects on a drillthrough MDX query are ignored in SSAS 2014 and earlier. For example, here’s a drillthrough query generated by an Excel 2016 PivotTable with a multiselect slicer connected to an SSAS Tabular model:

SELECT [Measures].[Sum of SalesAmount] ON COLUMNS 
FROM [Model]))

When run against SSAS 2014, this drillthrough returns records that are not filtered by date; when run against the same model in SSAS 2016, the subselect is respected and the resultset is filtered by the selected dates.