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: http://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:


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.

Tables.GetRelationships() Function In Power BI M

There’s a new M function in the latest release of Power BI Desktop that I spotted: Tables.GetRelationships(). Here’s the help page for it:


Basically, what it does is return all the relationships between all of the tables in a data source such as SQL Server. The documentation is a bit cryptic but I worked out what it does.

When you use a function like Sql.Database(), you get a table that contains a list of all of the tables in a database. For example, if I use the expression:

Sql.Database("localhost", "adventure works dw")

On my local instance of the Adventure Works DW database, this is the table that is returned:


This, it turns out, is the “navigation table” that the Tables.GetRelationships() function needs for its first parameter. The column called “Data” in this table, which contains links to the actual tables in the database, is what Tables.GetRelationships() needs for its second parameter. Put the two functions together in a query like this:

    Source = Sql.Database("localhost", "adventure works dw"),
    Rels = Tables.GetRelationships(Source, "Data")

And you get the following table:


Expand all the columns here and you get a table with one row for every relationship detected between every table in the database:


Useful if you need to report on the structure of a database, I guess. It’s a shame that this isn’t available in Power Query in Excel yet (it isn’t as of September 2015, I assume it’s coming soon) because it would be cool to use this data with NodeXL.

Calculated Tables In Power BI

There are loads of great new features in today’s release of Power BI Desktop, but for me the most important by far is the introduction of calculated tables. Miguel Llopis gives a good introduction to what they are in the post announcing the release, but I thought it was worth going into a bit more detail about what they are and why they’re so useful.

What are calculated tables?

Calculated tables are tables in the Data Model whose data source is a DAX expression that returns a table. Here’s a simple example. Imagine that you have already imported the DimDate dimension table from the Adventure Works DW database into your Power BI Data Model. If you go to the Data tab you would see the contents of that table shown, and on the ribbon you can see the new New Table button:


Clicking the button allows you to enter a new table name and a DAX expression that returns the table, such as this one that returns a filtered subset of the rows in the DimDate table:

MyFilteredDimDateTable =
    DimDate[CalendarYear] = 2002
        && DimDate[EnglishMonthName] = "June"
        && DimDate[DayNumberOfMonth] < 5


Calculated tables are created when the data in the model is refreshed (like calculated columns), and after that behave like any other table – so you can create relationships between calculated tables and other tables. You can also create calculated tables whose DAX expressions reference other calculated tables. They do take up memory like other tables too, so over-using them could be a bad thing.

Why are calculated tables useful?

Miguel’s blog post already lists some of the scenarios where calculated tables are useful, and I can already think of lots of practical scenarios where I’m going to be using them myself.

Role playing dimensions are one obvious use: in a lot of models you need to use the same dimension table more than once in different places, with different relationships and maybe with different filters in place. It might be that you have a single Company dimension in your data warehouse that contains all of the companies your organisation does business with; with calculated tables you only need to import that table once, and you can then use calculated tables to create filtered copies of that table to use as Supplier and Customer dimension tables, joining them to your Stock and Sales fact tables, and only showing the relevant companies in each case.

Certainly for debugging complex DAX expressions they’re going to be handy, because they allow you to see what DAX table expressions return. We’ve already DAX Studio for that but now we don’t have the hassle of switching to another application…!

I can also see calculated tables as a way of doing certain types of ETL – which raises the question of whether you should do a certain operation in Get Data (ie what was Power Query) or using a calculated table. I strongly suspect that a lot of operations are going to be much faster with calculated tables because of the power of the underlying engine. It would be interesting to know if there are plans to allow Get Data to make use of calculated tables, for example as a way of buffering tables in memory, with M transformations folded back to DAX on those tables.

The Calendar() and CalendarAuto() functions

If you were wondering what the new DAX Calendar() and CalendarAuto() functions were for, well, you can probably guess now – Date tables. The Calendar() function returns a table of continuous dates within a given range. So, the expression

CalendarDemo =
CALENDAR ( "1/1/2015", "2/2/2015" )

Will return a table with one column containing all the dates from January 1st 2015 to February 2nd 2015:


The CalendarAuto() function looks at all of the Date columns in all of the other tables in the model, and returns a similar table but one where the first date is the beginning of the year that contains the earliest date found in any non-calculated column in any non-calculated table, and where the last date is the end of the year that contains the latest date found in any non-calculated column in any non-calculated table. By default the beginning of the year is January 1st and the end of the year is December 31st, but there’s also an optional parameter to specify a different month to end the year on, if you want to create a fiscal calendar table.

I wonder if we’ll get something like the functionality in Excel 2016 for creating date tables with other useful columns?

Upcoming User Group, Conference And Teaching Dates In Scandinavia, UK and USA

I’m going to be doing a lot of speaking at various events over the next few months, and so I thought I would let you know about where I’ll be in case you want to attend.


Next week I’m doing a mini-tour of four Scandinavian user groups in a week:

SQL Relay

I’m going to be speaking at two SQL Relay events in October. SQL Relay is a series of one-day SQL Server events held in various places all over the UK and is always well worth attending. I’ll be speaking at:

MDX Training Course

There are still a few places available on my “Introduction to MDX” training course in London, running from October 12th-14th. It will teach you everything you need to know about MDX queries and calculations for Analysis Services, starting from the absolute basics and going up to SCOPE statements. Check out the Technitrain site for details of other courses including Allan Hirt’s Mission Critical SQL Server, Rafal Lukawiecki’s Practical Data Science With Cortana Analytics, and more to be announced soon.

My MDX and SSAS cube design and performance tuning courses are also available in video form from Project Botticelli, and you can get a 10% discount if you register using the code TECHNITRAIN2015

PASS Summit 2015

It is always an honour to be selected to speak at the PASS Summit, and this year I’ll be doing two sessions: “Using Power Query to build a Reporting Solution in Excel” and “Analysing audience reaction to the PASS Summit keynote”. The latter should be particularly fun, since it will involve me using Bing Pulse, Power BI, Excel, NodeXL and Azure Machine Learning in a lot of demos! I hope to be making a guest appearance in a third session, which I’m also excited about, but I’ll leave that as a surprise…