New SSAS, Power BI And SQL Server Training Courses For 2016

I’ve just added a number of new 2016 training course dates to the Technitrain site. If you’re looking for Power BI, Analysis Services, SQL Server or Data Science training in London then please check them out! Also, if you’d like to sign up for the Technitrain newsletter to stay up-to-date with our news you can do so here.

Here are more details on the new courses:

SQL Server Performance Tuning and Internals Boot Camp, Bradley Ball, 15-19 February 2016, London
This 5-day hands-on course is designed to provide DBAs with the tools and knowledge that are required to keep their SQL Servers running efficiently and reliably.

Introduction To Power BI, Chris Webb, 22-23 February 2016, London
A two-day introduction to Microsoft’s exciting new BI tool suitable for BI professionals, analysts, report developers or anyone interested in using it to build reports or dashboards.
Real World Cube Design And Performance Tuning With SSAS Multidimensional,  Chris Webb, 11-13 April 2016, London
A course aimed at intermediate-to-experienced Analysis Services Multidimensional developers, looking at more advanced cube design topics and query performance tuning.

Mastering DAX, Marco Russo, 20-22 June 2016, London
A three-day introduction to the DAX language used by Power BI, Analysis Services Tabular models and Power Pivot.

Optimising DAX, Marco Russo, 23-24 June 2016, London
An advanced two-day course for experienced DAX developers who wish to learn how to optimise DAX calculations and queries for performance.

Introduction to MDX, Chris Webb, 4-6 July 2016, London
A three day course designed for those with little or no experience of MDX, this course will teach you how to write MDX queries and calculations for Analysis Services.
Practical Data Science with Cortana Analytics, Rafal Lukawiecki, 24-27 October 2016, London
This course is aimed at analysts, analytical power users, predictive developers, BI power users and developers, budding data scientists and consultants.

Webinar Recording: Is OLAP Dead?

Yesterday I presented a webinar for the nice people at Pyramid Analytics, the first of two. I was paid for it of course (I have to earn a living somehow, you know), but the main reason I agreed to do it was because they wanted me to discuss interesting Microsoft BI questions rather than just demo their product – which is, as I’ve said here before, the most effective way of doing tech marketing in my opinion. In any case Pyramid Analytics is one of a small number of third-party tools I’m happy to recommend to my customers even without being paid.

Today’s discussion was on the topic of whether OLAP is dead and you can watch the recording here: [free sign-up required]

You won’t be surprised to find out that I don’t think OLAP is dead. By “OLAP” I mean the idea of a centralised model containing not just all your data but also things like how your tables should be joined, how measures aggregate up, advanced calculations and KPIs and so on. Using this definition both SSAS Multidimensional and SSAS Tabular are OLAP; I would say both pass the FASMI test for sure. The opposite of OLAP is of course the recently-fashionable self-service BI approach of tools like Power Pivot where every end user can create their own model, which is certainly more flexible but also less easily maintainable, leads to duplication of effort and data, and can be a problem for less technically-able end users. Both a single centralised OLAP model and a distributed self-service approach are useful in different circumstances and the latter certainly doesn’t replace the former. One of the reasons why I’m such a fan of Microsoft’s new direction with Power BI is that it recognises this fact.

The other big question that came up was what the relative strengths and weaknesses of SSAS Multidimensional and Tabular are. Before the webinar I ran a Twitter poll asking people whether they had used Multidimensional or Tabular on the last SSAS project that had worked on; from 151 responses, 48% said Tabular and 52% said Multidimensional. This even split doesn’t surprise me to be honest, based on my own experiences: both flavours of SSAS have different strengths and weaknesses and choosing between them can be very difficult. I’m sure new features like bi-directional cross-filtering and the improved development experience in SSAS Tabular 2016 will increase Tabular’s popularity in the future. Personally, I think if Microsoft were able to offer equivalent functionality to:

  • Calculated members on non-measures dimensions
  • MDX SCOPE statements
  • Parent/child hierarchies (without the performance penalties)
  • Writeback

…in SSAS Tabular then there would be very few good reasons to choose SSAS Multidimensional for new projects.

First Look At SSAS 2016 MDX On DirectQuery

Following on from my last post covering DirectQuery in Power BI, I thought it might be interesting to take a look at the way MDX queries are supported in SSAS Tabular 2016 CTP3 DirectQuery mode.

There were a lot of limitations when using DirectQuery in SSAS Tabular 2012/4, but for me the showstopper was the fact that it only worked if you were running DAX queries against your model. Historically the only major client tool that generated DAX queries to get data was Power View, and Power View was/is too limited for serious use, so that alone meant that none of my customers were interested in using DirectQuery. Although we now have Power BI Desktop and, which also generate DAX queries, the fact remains that the vast majority of business users will still prefer to use Excel PivotTables as their primary client tool – and Excel PivotTables generate MDX queries. So, support for MDX queries in DirectQuery mode in SSAS 2016 means that Excel users will now be able to query a Tabular model in DirectQuery mode. This, plus the performance improvements made to the SQL generated in DirectQuery mode, means that it’s now a feature worth considering in scenarios where you have too much data for SSAS Tabular’s native in-memory engine to handle or where you need to see real-time results.

At the time of writing the most recent release of SQL Server 2016 is CTP3. If you want to test out the BI features in SQL Server 2016 CTP3 in an Azure VM, I highly recommend Dan English’s blog post here showing how to set one up. To test DirectQuery mode you need to use the older 1103 compatibility mode for your project and not the latest 1200 compatibility mode. This is documented in the release notes:


Once you’ve created your project, you can enable DirectQuery mode in the same way as in previous versions by following the instructions here. The DirectQueryMode property on Model.bim needs to be set to On, and the QueryMode property on the project should be set to DirectQuery.

For testing purposes I downloaded the 2016 version of the Adventure Works DW database and restored it to SQL Server, then created a SSAS Tabular model containing only the DimDate table to keep things simple. I created one measure in the model with the following definition:

First of all, I ran the following MDX query:

ON 0,
ON 1


Using a Profiler trace (yes, I know I should be using XEvents but Profiler is so much more convenient for SSAS) I could see the SQL generated by SSAS in the Direct Query Begin and Direct Query End events. For the MDX query above there were three SQL queries generated. The first looks like it is getting the list of years displayed on the Rows axis:

TOP (1000001) [t0].[CalendarYear] AS [c15]
  (SELECT [dbo].[DimDate].* FROM [dbo].[DimDate])
AS [t0]
GROUP BY [t0].[CalendarYear] 

The second SQL query gets the measure value requested:

TOP (1000001) [t0].[CalendarYear] AS [c15],
AS [a0]
  (SELECT [dbo].[DimDate].* FROM [dbo].[DimDate])
AS [t0]
GROUP BY [t0].[CalendarYear] 

The third is simply a repeat of the first query.

However, there’s one important thing to say here: there are going to be significant changes and improvements to the SQL generated before RTM, so don’t read too much into the queries shown here.

There are several limitations in CTP3 that may or may not remain at RTM. One that you may run into is the that you can only use fully qualified MDX unique names in your queries, so


…will work but


…will not. To be honest, I consider it a best practice to use fully qualified unique names anyway so I’m not too bothered about this. Drillthrough doesn’t work at the moment either.

MDX calculations defined in the WITH clause of a query are supported, which is really useful if you’re writing custom MDX queries for SSRS. For example the following query works and generates the same SQL (though with a few more executions) as the previous query:

MEMBER [Measures].[TestMDXCalcMeasure] AS 

ON 0,
ON 1


All in all, this looks like a solid piece of work by the SSAS dev team. Go and test it! I would love to hear from anyone with genuinely large amounts of data (maybe APS/PDW users?) regarding their experiences with 2016 DirectQuery. Recently I’ve been working with a customer using SSAS Multidimensional in ROLAP mode on top of Exasol and I’ve been surprised at how well it works; I would imagine that 2016 DirectQuery and APS would be an even better combination.

One last thought. If we get the ability to query a cloud-based Power BI mode with MDX and MDX on DirectQuery is supported in Power BI too, why would you bother paying for an expensive SQL Server Enterprise/BI Edition licence plus hardware to use DirectQuery when you can get almost the same functionality in the cloud for a fraction of the price?

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: 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
  • 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 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
  • 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.

What the MDX Axis() Function Actually Returns

A month or so ago, before I went on holiday, I was working on a really cool MDX idea that involved the Axis() function. Unfortunately I’ve forgotten what that idea was but while I was working on it I did find out something interesting about the Axis() function – namely that it doesn’t do exactly what the documentation says it does.

The documentation says that the Axis() function returns the set of tuples on a given axis in an MDX query. Here’s a simple example query on the Adventure Works cube showing it in action:

[Customer].[Gender].MEMBERS ON 1
[Adventure Works]


Here, I’m using the SetToStr() function to take the set returned by the Axis() function and display it in a calculated measure. As you can see from the screenshot, I’m showing all three members from the Gender hierarchy on the Customer dimension on rows and the set returned by Axis(1) is indeed that set.

BUT, now look at this second query and what it returns:



[Customer].[Gender].MEMBERS ON 1
[Adventure Works]


Why is this interesting? The calculated measure FIRSTMEMBER returns the unique name of the first member in the set returned by Axis(1), which should be the first member shown on the rows axis. The calculated measure TEST returns null if the currentmember on the Gender hierarchy has the same unique name as the member returned by FIRSTMEMBER. The calculated measure TEST is on columns in the query, and on rows we get all the members on the Gender hierarchy that return a non null value for TEST. Since only Female and Male are returned, the All Member on Gender must return null for TEST, which means that the All Member is the first member in the set returned by the Axis() function.

So, to summarise, the Axis() function actually returns the set of members on an axis the current query before any NON EMPTY filtering is applied.

How To Optimise The Performance Of MDX Queries That Return Thousands Of Rows

One problem I encounter on a regular basis is how to optimise the performance of MDX queries that return thousands, hundreds of thousands, or even millions of rows. The advice I give is always the same:

Reduce the number of rows that your query returns!

Yes, there are some things you can change in your queries and cube design to improve performance, but these are the same things I’d suggest for any query (build aggregations, rewrite MDX, partition etc etc). In my opinion, if you have a query that returns a ridiculously large number of rows you are doing something fundamentally wrong.

There are three reasons why SSAS developers write this kind of query:

  1. They are doing a data-dump from SSAS to another system. Mostly the developer doesn’t realise this though, since the other system is Excel-based and the end user has disguised their requirement as a report. In most cases, user education about how to use Excel with SSAS results in an approach that doesn’t require dumping thousands of rows of data to an Excel worksheet.I will admit that I have seen a few cases where developers need to dump data out of SSAS for other purposes, and have no option but to use SSAS because they have to add complex calculations that can only feasibly be implemented in MDX. These are very rare though, and most of the time using SQL queries against the underlying relational database works a lot better.
  2. The end users have specified a report that returns lots of data, because that’s just what they want, dammit! Often this is to recreate a report built in a previous system that, at some point in the 1970s, was printed out into a gigantic book every month. My argument here is that a report should return no more data than can be seen on a screen without scrolling. If you need to scroll in a report, you probably should be giving the end user more parameters to filter that report so they can find the data they want to see more easily instead.Of course it’s one thing to know what you should be doing, it’s another thing entirely to tell the CFO that their requirements are stupid. If you can’t convince your end users that you know better than them, you have my sympathy. Usually I find that having to choose between the poor performance of what they want and the better performance of a different approach helps them come to their senses.
  3. Finally, the way that SSRS handles drilling down in reports often leads report developers to bring back vast amounts of data. The advice to increase the number of parameters for filtering is equally relevant here, but you can also use MDX techniques like this one to implement drill down in a much more efficient way.

At the end of the day, SSAS just isn’t optimised for returning large resultsets – it was designed to return PivotTable-style queries, which are always relatively small. You can get good performance for large resultsets if you know what you’re doing, you have the time, and you’re lucky, but you’ll usually be better off rethinking your requirements or choosing a different tool.

MDX Scoped Assignments Outside The Granularity Of A Measure Group

If you’re an SSAS Multidimensional developer, you’ll know that not every dimension has to have a relationship with every measure group in your cube. You may also know that by setting the Granularity Attribute property of a regular relationship, you can join a dimension to a measure group using an attribute that isn’t the dimension’s key attribute. What happens when you make a scoped assignment to a non-calculated measure outside the granularity of a measure group?

The simple answer is that, unlike what happens when you assign to a non-calculated measure inside the granularity of a measure group, your assigned value does not aggregate up. For example, consider a dimension called Demo Dim with one user hierarchy, where there is just one member on each level:


If you add this dimension to a cube but don’t define any relationships to any measure groups (and don’t change the IgnoreUnrelatedDimensions property of the measure group) you’ll see the value of the All Member on the hierarchy repeated across all of the other members of the hierarchy:


If you use a scoped assignment to change the value of the member D for a regular, non-calculated measure M1, like so:

SCOPE([Measures].[M1], [Demo Dim].[L4].&[D]);
    THIS = 999;

You’ll see that D changes value, but the value isn’t aggregated up:


The same thing happens if you make an assignment below the granularity attribute of a dimension. This all makes sense when you think about it, in my opinion, and it means that in this scenario at least non-calculated measures and calculated measures behave in the same way.

One last word of warning: whenever I’ve done this, I’ve found that query performance hasn’t always been as good as I would have liked.