Pre-Conference Training Day On Data Loading With Power BI/Excel Get & Transform/Power Query At PASS Summit

I’m pleased to announce that I’m running a pre-conference training day on “Loading and transforming data with Power BI and Power Query” at this year’s PASS Summit in Seattle. For the full agenda and details on how to register, see:

This full-day session will teach you everything you need to know about loading data into Power BI or Excel; it’s specifically focussed on self-service BI scenarios, but if you want to learn M for SSAS 2017 you might find some of what I cover useful too. Topics covered include using the UI to build queries; using parameters and functions; the M language; data privacy settings; and creating custom data connectors. I hope to see you there!

Microsoft Data Insights Summit Videos Published

The Microsoft Data Insights Summit finished today, and the videos of the sessions have already (!!) been published online here:

I can’t remember ever attending a conference where there was so much new functionality revealed. I’m not talking about things like Power BI Premium – that’s last month’s news – I’m talking about lots and lots of really cool new features that I had not seen before, so many that I feel completely overwhelmed by them all. There are a few blog posts out there that try to summarise the announcements (apart from the post on the official Power BI blog, check out Matt Allington and Dustin Ryan’s posts) but really your only option is to watch as many of the session videos as you can! I thought I would call out some videos about the new features for you to start off with:

  • The keynote on the first day was pretty amazing (Amir Netz’s demo should be watched in full) with some highlights being:
  • The session on the Visio custom visual for Power BI had a lot of detail on how Visio/Power BI integration will work, and was very impressive. You can sign up for the preview here.
  • Power BI/PowerApps/Flow integration, with Power BI content appearing in Power Apps and vice versa is something I am going to watch with interest.
  • One major announcement (at least for me) that slipped out in the session on the Common Data Model is that there’s going to be a standalone, SaaS version of Power Query available that will be able to load data into the Common Data Model
  • Matt Masson’s session has a lot of information on building custom data connectors as well as the announcement that M is officially called M!
  • Azure Analysis Services will soon have a much better web interface that will allow you to create simple models, and also to import models from Power BI .pbix files. Christian Wade’s session on Azure Analysis Services also has a great extended demo showing off new SSAS Tabular features, including one showing using BISM Normalizer to merge parts of a mode in a .pbix file into a Tabular model.

This is by no means comprehensive. I haven’t been able to watch all the videos yet either, so there may be other important new features in other sessions that I completely missed.

Last of all, although they don’t contain any new announcements, you can also watch my two M-themed sessions from the conference: Working With Parameters And Functions and Working With Web Services in Power BI/Power Query/Excel Get&Transform.

Two Upcoming Power BI Webinars

This is just a quick post to let you know about two webinars I’m presenting soon. First, on the 21st of June (today!) at 1pm PST I’m presenting a webinar on “Introduction to M” as part of the Power BI community webinar series; more details on it and how to register can be found here:

Second, I’m presenting a webinar with Pyramid Analytics about their on-premises BI solution and how it integrates with Power BI on the 30th of June called “The Public Cloud Is Not For Everyone”:

[Full disclosure – I’m being paid for this – but it won’t be marketing fluff, just honest discussion, similar to the webinars I did with Pyramid last year]

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…

Advanced SSAS Multidimensional Security Tips & Tricks Webinar This Thursday

In association with the nice people at SQLRelay I’ll be presenting an hour-long webinar on advanced SSAS Multidimensional tips and tricks this Thursday July 9th 2015 at 1pm UK time (that’s 8am EDT for you Americans). It’s free to attend and open to anyone, anywhere in the world. You can join the meeting by going to

In the webinar I’ll be covering topics such as:

  • The difference between Allowed Sets and Denied Sets in dimension security
  • Handling security-related errors in your MDX calculations
  • The different ways of implementing dynamic security
  • Why you should avoid cell security, and how (in some cases) you can replace it with dimension security

…and lots more.

If you’re in the UK, you should definitely check out SQLRelay, an annual series of one-day SQL Server events that happens at a number of different places around the country each autumn. For more details, see

I’m presenting this webinar in my capacity as a sponsor of SQLRelay, so expect me to spend a small amount of time promoting Technitrain’s autum course schedule. There are some cool courses on SSIS, MDX, SQL Server high availability and data science/machine learning coming up, you know…

UPDATE: you can download the slides and demos from the webinar at and watch the recording at

For whoever was asking about using a measure group to store permissions for dynamic security, this blog post has all the details:

Power BI And Excel 2016 BI News

There have been quite a few Power BI and Office BI-related announcements over the last few weeks, and while I’ve tweeted about them (I’m @Technitrain if you’re not following me already) I though it would be a good idea to summarise them all in one post.

Power BI Announcements at Convergence and SQLBits

You’ve probably already seen the announcement today on the Power BI blog that Power BI is FINALLY available to those of us outside the USA:

At last! I’m sure MS had very good reasons why they couldn’t make the Power BI Preview available worldwide back in December, but this decision caused a lot of frustration in the MS BI community and I hope it’s not something that happens again. I can also confirm that the Power BI iPhone app is now available in the UK as well. The new data sources for Power BI that are coming soon – especially Google Analytics – will be very popular I think.

While I’m on the topic of Power BI, a few interesting nuggets about upcoming functionality emerged at SQLBits last week. Kasper mentioned that there will be some new DAX functions appearing in Power BI soon: Median, Percentile, DateDiff and XPNV. Presumably they will appear when we get the ability to create DAX measures and calculated columns in the Power BI Dashboard Designer. Also, following on from the bidirectional relationships functionality I blogged about earlier this year, there was the news that Power BI will also understand 1:1 relationships as well as 1:many, many:1 and many:many.

Office 2016 Preview BI Features

The Office 2016 preview went public today too:

There’s a great overview of what’s new for BI in Office 2016 here:

The main points are:

  • Power Query is now a native feature of Excel 2016.
  • Power View works on SSAS Multidimensional (this is only going to work on the versions of SSAS Multidimensional that support DAX queries, ie SSAS 2014 or SSAS 2012 SP2)
  • New Excel forecasting functions
  • Time grouping functionality in PivotTables

I’ll be writing a more detailed blog on all of this at some point soon, once I know what’s officially public and what isn’t.

The Power Query announcement is interesting because, as things stand at the moment, we’ll be able to use full Power Query, Power Pivot and Power View functionality for free in the Power BI Dashboard Designer, but in Excel the same functionality is restricted to users of the Professional Plus SKUs. This is crazy, and I hope Microsoft makes the Power add-ins available for every SKU of Excel 2016. Have you signed the petition for this yet?

Power Map

Last week the Power Map team released a new video showcasing functionality from an upcoming release:

Although there are no details about what is shown in the video, it certainly looks like the ability to use custom shapes (the main missing feature in Power Map up to now) will be coming soon.


Wow, psychedelic…

Surface Hub

Finally, BI is clearly one of the main use-cases of the new Surface Hub (see also this video):


I wonder if I can justify buying one for demo purposes?

PASS Summit 2014 Day 2 Keynote: A Masterclass In Cloud Databases, And Also A Masterclass In Tech Marketing

The PASS Summit keynote today was given by Dr Rimma Nehme, a colleague of PASS favourite Dr David DeWitt, who gave a great talk on cloud databases. A recording of the keynote will I’m sure be posted somewhere to view if you weren’t able to watch it live – it was an excellent presentation, I learned a lot and I recommend you watch it. However, the technical content of today’s presentation is not what I want to talk about here.

Sitting on the blogger’s table yesterday and today, I realised something about tech marketing in general and the challenges that Microsoft faces in marketing its cloud-first, BI-heavy strategy to its existing SQL Server customers. Let’s imagine you knew nothing about SQL Server, Microsoft, PASS and so on. If you looked at the reactions on Twitter (which I think are representative of the reactions of the SQL Server community as a whole) to yesterday’s keynote and today’s keynote, you would have seen a big difference. Yesterday there was a mixture of supportive comments and the snarking/complaining/moaning that has become common in PASS keynotes. Now it’s hard to put your own opinions on Microsoft’s strategy and how MVPs should behave on Twitter to one side, but if you can then you have to admit that the negative reactions represent a gigantic marketing failure. Some people, a lot of people, are unhappy with the message that Microsoft is putting across. Part of me wants people not to be unhappy – and I’m sure lots of people at Microsoft are equally frustrated – because I can see the logic behind Microsoft’s decisions, but wanting people to change their minds is not the same thing as persuading people to change their minds.

In contrast, the reaction on Twitter to Dr Nehme’s talk today was overwhelmingly positive. The same people who were unhappy yesterday were respectful and attentive today. There was a standing ovation at the end. But what was the topic? The cloud! Isn’t the cloud the root of all evil? Why were the reactions so different? Well, you say, this was technical education, not marketing. It was indeed technical education, but let me be clear: today’s keynote was just as much a marketing presentation as yesterday’s keynote. The difference is that it was highly effective marketing for the cloud, rather than a ham-fisted attempt to ram the cloud down people’s throats. Truly effective marketing is not obvious as marketing; truly effective marketing of this kind is something that the intended audience actively enjoys (and I’m not saying this is done as some kind of cynical deception – today’s audiences are too aware for anything insincere to succeed). Today’s keynote did more for the perception of Microsoft’s cloud strategy in its target audience than anything else I have seen recently. The other confusing aspect of this is that the ineffective marketing here is the work– I assume – of marketing professionals – whereas the effective marketing has been done by someone who is clearly One Of Us (although she mentioned at the beginning of her presentation, I think, that she had an MBA as well as a laundry list of other impressive qualifications) and not a marketing professional.

So what can we, and more importantly Microsoft, learn from this? It’s that if you want to do effective marketing to a technical audience you have to talk tech to them. Speak to them as equals in a language they understand, and provide strong technical reasons to back up what you’re saying. Traditional marketing that relies on theatre and pizzazz and that has no substance is actually counter-productive and damaging. The good news is that Microsoft does have a lot of people who instinctively understand this. I have always felt that Buck Woody is a true technical marketing genius, quite apart from his many other accomplishments. Donald Farmer is too, and that’s why so many people saw his leaving Microsoft as a significant blow for Microsoft BI. I’m not arguing that Microsoft fire its marketing department and let the techies handle marketing itself because that’s clearly never going to happen, I doubt the techies would want to do that job full-time, and let’s face it the techies would be equally inept at marketing but in different ways. What needs to happen is that the marketing professionals at Microsoft understand that their current, very traditional strategy is failing and that it should be replaced with an entirely new approach. The evidence from PASS is that the content itself is not the problem, it’s the way it is being presented.