Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

Monthly Archives: September 2013

If you’ve been a member of PASS since before June 13th this year, you’ll have received an email telling you that voting has opened in the PASS Board of Directors Elections. This is a friendly reminder to you to use that vote! Check out the candidates here.

For those of you who (like me) live in Europe, the Middle East or Africa, you should realise that for the first time one seat on the board is reserved for someone from this region. I welcomed this change when it was announced, and even though I was a little disappointed to see that the only EMEA candidates who made it this far are from the UK I still think it’s important that PASS is reaching out beyond its North American heartland.

I cast one of my votes for Jen Stirrup. While I know many of the candidates to a greater or lesser extent, Jen is the one I know best and I have always been impressed with her energy, organisational ability, integrity and vision. I think she would make a great contribution to the Board if she was elected.

Share this:

Like this:

Today the natural language query functionality in Power BI, Q&A, was enabled in my Power BI Preview tenant. This is the last major piece of functionality to be added to the Preview, and of course as soon as I heard that it was there I had to go and play with it, despite being on a slow mobile WIFI connection and having tons of emails to catch up with. So apologies to anyone who is waiting for an email from me!

First thing to point out: you can only use it on some sample workbooks provided by Microsoft, and these get added to your Power BI site when you enable Q&A. The ability to use it on your own data is coming soon but I don’t think this stopped me from getting a feel for how well it works.

With Q&A switched on my Power BI site now looks like this:

Notice that the top half of the page now has a ‘Popular Questions’ area which takes you to some pre-defined questions on the sample data, while in the bottom half you see the sample workbooks along with any other workbooks you have uploaded. The two sample workbooks contain data on medals won at the Olympics and sales of drinks in a bar.

Here’s what one of the sample questions looks like when you click on it:

At the top of the screen you have a search box where you can type your question; immediately underneath that box is how Q&A has interpreted the question. On the left-hand side you see the workbook used as the data source, and you can see alternative visualisations for the data that you can choose. On the right-hand side there are some suggested questions for this data and other hints and tips. It’s all pretty straightforward.

But enough of the descriptions – there are two big questions that need to be answered here:

Does it work? That’s to say, will a real user be able to ask questions and find they data they want?

Will anyone use it, or is it just demo-ware?

I’m aware these are loaded questions and I run the risk of upsetting my friends at Microsoft if I say rude things about this product. I’m also aware that I have only had a very limited amount of time to play with it; that it is still a Preview and will undoubtedly improve a lot over the next few months (however good it is now); and most importantly that I am not the target audience for this product therefore it will be impossible for me to judge how well it works – I know too much, and I am likely to be able to be able to write questions that work much better than a normal user can. I had hoped to try this out on my wife to get some real world feedback but unfortunately I’m travelling at the moment and she’s at home with the kids…

With that in mind, let me give you some idea of how well it works by showing questions I tried and the output I got:

The first question I tried to find an answer for was how many medals Australia had won for Swimming in 2012. I typed “how many medals did Australia win for swimming in 2012” and this was the result:

I had wanted a single value and instead I got a count of medals for swimming broken down by name; they were Australians (I assume) but it’s not exactly what I was after. I then tried “total number of medals won for swimming by Australia in 2012” and hit the jackpot:

My next question “list the cities where the games have been held” got the right answer first time:

A similar question, “total number of times the games have been held in each city”, also worked first time (but maybe because I had learned that Q&A can interpret the phrase “total number” properly):

I don’t know why it thought a map was appropriate here though.

One final test: I wanted to know which athletes had won medals at more than three different Olympics. I tried “who has won medals at more than three games” but got a list of countries that had won medals at more than three games, which of course was quite a long one; “athletes that have won medals at more than three Olympics” got a list athletes and the games they had won medals at. Eventually after several more tries I gave up.

Of course this last question was deliberately difficult question and entering the same text into Google didn’t give any useful results either. I would have been very surprised if I had got the correct answer. I think, though, this question gets to the heart of the reason why a lot of us were sceptical about Q&A when they first heard about it: end users won’t know which questions a product like this can reasonably answer and which ones are just too difficult, and they won’t know how to frame questions appropriately, and therefore a product that seems to suggest it can answer any question you type will always, ultimately, disappoint them. It will be very difficult to set users’ expectations appropriately.

That said, I was pleasantly surprised at its success rate and I’m looking forward to trying it on my own data. To answer my questions above:

Does it work? Yes, I think it does a good job of answering any question I would have expected it to be able to answer. Furthermore, as I said, I know it will improve over time, not only because we’re still in Preview but also because this is a cloud-based solution and MS can keep tweaking after it has been released as well. I wonder if MS are capturing usage metrics and questions and using it to tune the system? I bet they are.

Will anyone use it? This is a much trickier problem. I’ve never come across an end user who has asked for a product like this, but just because no-one has ever asked for something doesn’t mean they won’t love it if they get it. It’s clearly aimed at people who find every other BI tool available today too difficult (and there are plenty of them), but are these people interested in using any BI tool however easy it is to use? Will they just go and ask a colleague or an analyst or a minion to go and find the answer for them instead? Frankly, I don’t know at this point. We shall see though!

Share this:

Like this:

I’ve had access to the Power BI preview for a week or so now and had the chance to spend a bit of time studying it (there’s nothing like a looming UG presentation to focus your mind when learning new tech), so I thought I’d write a post about what my feelings so far are.

The usual disclaimer applies to this post: while it may sound like I’m being negative at times, that’s just the way I write and overall I’m actually very impressed with what has been delivered. There’s a lot for the Microsoft BI community to be happy about here, and I’ve tried to be as generous with my praise as I have been with my criticisms.

First of all, let me define the scope of this post:

We’ve had access to the Power BI Excel components for a long time now, and I’ve already blogged about them at great length so I don’t want to cover their functionality again in any detail. To summarise my feelings about them briefly:

Power Pivot/The Excel Data Model: a great foundation for everything else and unlike all of the other Power BI components, quite mature now.

Power Query: the new star, as important as Power Pivot in my opinion.

Power View: good, but that HTML5 version can’t be delivered soon enough.

Power Map: the most recent version is a big improvement, but until we can overlay our own shapes and a few other issues have been ironed out its real-world uses are limited. Great for demos though.

That leaves the Power BI cloud and mobile components, and they will be the focus here. I’m not going to bother describing functionality in much detail and instead I’ll concentrate on whether it’s any good or not.

I’m sure a vast amount of work has gone on in the background to make the Power BI service robust and scalable which isn’t visible at all, which no-one will even realise has taken place if it all works smoothly, and which I can’t comment on.

The Preview still has some limitations, namely:

Not all data refresh functionality works yet, although it is possible to set up all the components needed for it.

Q&A, the natural language querying/visualisation tool, isn’t available yet. I’ll devote another blog post to Q&A when I get to use it.

Here’s what I’d like to highlight:

The Documentation

It might seem pretty strange to mention the documentation in a review like this, but it’s one aspect of the Preview that I think has been done very well indeed. Usually when a product like this appears the documentation is a bit thin, leading to a rash of blog posts explaining how you actually do stuff. This time, though, it’s clear that a lot of work has gone into the docs so well done to whoever wrote it all up.

Power BI Sites

You can think of Power BI Sites as being an improved way of browsing and organising the BI-related workbooks you’ve uploaded to document libraries in Sharepoint Online (rather of like the PowerPivot Gallery is meant to be in on-prem Sharepoint). They’re also how all the cool extra stuff that Power BI gives you over and above the normal functionality of a Sharepoint document library is implemented. Power BI sites are implemented as an app for Sharepoint; there’s a detailed explanation of what they are and how they work here. Some thoughts:

I can understand why it was necessary to implement them this way, and while I don’t think it’s a big issue it’s a shame that there isn’t a seamless transition from normal Sharepoint and Power BI. I can see some users getting slightly confused between a regular document library and the Power BI app.

The most obvious example of this is around having to ‘Enable’ a document for Power BI and the related issue of file size limits. John White has already written an excellent post on this topic that I would urge you to read, so I won’t repeat what he says here, but you can see from the sheer length of the post that it’s a relatively complex topic. In addition, one thing that John doesn’t mention is that apart from the 10MB/250MB limits there’s also another limit: if you want to refresh a model by clicking the Refresh button in the Excel Web App, that will only work if your model is below 30MB in size. Since it’s not clear what the size of a model is from within Excel, I suspect that users working with large data sets will run into these file size limitations without any warning and not know what to do. There are some best practices that should be followed (like using Power Query to load data direct to the model, and not staging it in the worksheet) but I’m not confident that end users will remember to follow them. Hopefully a future release of the Workbook Size Optimizer will help here.

As I said in a previous post, I like the fact that Microsoft has spent some time thinking about the role of the data steward and implemented some monitoring and management functionality appropriately. I hope they continue to build more functionality in this area, because self-service BI implementations will live or die by how well the data steward does his or her job.

Data Refresh

As I said, not all data refresh functionality works yet but I’ve managed to go through all the steps to set up the data management gateway. The actual set up was straightforward and it seems like it’s a good solution to the whole problem of refreshing workbooks in the cloud against on-prem data sources. Initially only SQL Server data sources are supported for direct refresh, which is understandable, and I guess more data sources will be coming soon; the workaround for other data sources described here of using SSIS to publish an OData feed and then accessing that via a Linked Server seems a bit of a hack to me. I also like the way registered data sources can be exposed as OData feeds from Power BI, but that’s because I like OData.

Power Query Sharing

I’m starting to realise how important the ability to share a Power Query query between several workbooks you own, and with your colleagues, is in Power BI. It’s going to be interesting to see whether end users create and share queries amongst themselves or whether the data steward or BI professionals create the majority of queries and then pass them down to the end users. I also wonder whether it will be possible to create a series of relatively simple Power Query functions, share them and then use them as building blocks to create more complex queries. This is something I want to come back to in a future blog post, though, once I’ve had a chance to think about the implications of it some more.

Mobile BI

Ah, mobile BI at long last! But is it any good? Hmm, well, yes I think so, or it will be when it’s finished; I don’t have a lot of experience of mobile BI apps though so I can’t claim to make an informed judgement. Thoughts:

I don’t own a touch-enabled Windows device of any kind, but I installed it on a Windows 8.1 VM and used it with a mouse with no problems (even though I subsequently read in the docs that it’s not supported on Windows 8.1!).

It’s very simple to use. Which is good.

It also seemed very responsive on the reports I tested it on. Which is also good.

While navigating through the Power BI site folder structure to find your reports feels a bit clunky, this is only something you’re going to do once or twice before you add the reports you look at most to your Favourites.

Some Power View functionality isn’t supported yet in the HTML5 view that the app uses, so for example Scatter charts can’t be displayed, but that will be addressed soon I’m sure.

There are no native iPad or Android apps as yet. I really hope they are coming soon. At the moment the docs say that you have to make do with the HTML5 view in the browser, but I think a dedicated app is important.

The way the each Power BI workbook is broken up into parts and displayed as a separate report in the mobile app will take a bit of getting used to, but my initial reaction is that it’s a reasonable approach to have taken despite its limitations. By default the mobile BI app shows each individual table/chart/PivotTable/PivotChart/named range and Power View sheet in a single workbook in a Power BI site as a separate report (see here for more details), and while you can control what does get shown you’re going to have to make a bit of extra effort to do this. Once again the docs and John White have some good information on how to design and configure reports with the mobile BI app in mind.

My feeling is that Power View sheets are going to be the best way of delivering reports through the mobile app – from my experiments they seem to work an awful lot better than PivotTables etc when viewed through the mobile app.

Is Anyone Going To Buy It?

From a purely technical point of view, Power BI is a worthy suite of tools and deserves to be used. In terms of functionality I doubt whether Power BI will ever be able to compete with the pure-play self-service BI vendors but I don’t think that is Microsoft’s strategy. As I’ve said here before, Microsoft is (very wisely) building on the familiarity and ubiquity of Office and in particular Excel, so while Power BI might lack some specific features, customers will want to use it because they already have Excel skills and don’t have to learn something completely new. Why pay a large amount of money for QlikView or Tableau licenses if you’re going to move to Office 365 anyway and everybody already knows Excel?

However, I have a bad feeling that if Power BI fails it will fail not for technical reasons but because customers don’t understand how to buy it. To explain some more:

At a fundamental level, a lot of people still don’t understand what Office 365 is. And I don’t just mean customers here, but partners, MVPs and even Microsoft employees. They have heard Office 365 is something to do with the cloud and they think that it means you have to use Excel, Word and so on in the browser. They are surprised to learn that it’s just a different way of buying Office, and that you still get the desktop Office tools.

Once you’ve got over that hurdle, you then face the fact that there are too many SKUs for Office and Office 365 to choose from. Again, many customers, partners, MVPs and Microsoft employees I’ve talked to struggle to understand which functionality is in which SKU. The long post I wrote about BI functionality in Office and Office 365 earlier this year, before Power BI came along, just goes to illustrate this complexity.

Bring Power BI into the equation and things are even less clear. What is Power BI exactly anyway? It turns out that it’s yet another one of those vague marketing terms (see also “xVelocity”, “BISM” and “UDM”) that Microsoft comes up with to try to bring some kind of thematic consistency to a bunch of related tools – you can say that it relates to almost all the BI functionality in Office, some of which has been around a long time now, some of which is newly released with this Preview. Under the banner of Power BI come some Excel addins and a cloud service, but the licensing terms and system requirements aren’t all the same. Looking at the just Excel components:

Power Pivot is available as a free download for anyone using any version of Excel 2010. The Excel Data Model, the engine of Power Pivot, is built into all desktop versions of Excel 2013, and the Power Pivot addin (which gives you the same functionality as Power Pivot in Excel 2010) is only available if you are using Office 2013 Professional Plus or an Office 365 SKU that includes Professional Plus, or soon the standalone retail version of Excel 2013. Power Pivot for Excel 2010 requires Windows XP SP3 or greater; Office 2013 will only run on Windows 7 or greater.
The change in licensing between Excel 2010 and 2013 was, of course, the trigger for a lot of protests earlier this year. Whatever rights and wrongs of the licensing, though, the change itself is the cause of a lot of confusion: I’m still hearing about people who have installed the wrong SKU of Excel 2013 and find they can’t use the Power Pivot addin.

Power View Excel functionality is also free, but only available to users of Excel 2013 and with the same restrictions as Power Pivot – ie you need Professional Plus or standalone. Power View in Excel is, of course, closely related to but not the same thing as the standalone Power View app available via Sharepoint.

Power Query is available to users of Excel 2010 and 2013, but only for those who have Office 2010 Professional Plus or Excel 2010 standalone or Office 2013 Professional Plus, an Office 365 SKU that includes Professional Plus or Excel 2013 standalone. Note the subtle difference between these terms and the Power Pivot terms – Pro Plus is required on both 2010 and 2013. Power Query is only supported on Windows Vista or greater.

Power Map is only available to users of Excel 2013, with the same restrictions as Power Pivot and Power View – Professional Plus or Excel standalone.

Turning to the Power BI cloud components, no licensing details have been announced yet but I assume they will be licensed separately and will require you to have bought the right SKU of Office 365 first.

In an ideal world, there would be just three SKUs of Office – Home, Small Business and Enterprise – and Power BI would not be an optional extra but would come as part of the Enterprise SKU. That isn’t going to happen, though, and Microsoft can’t afford to give away too much stuff for free any more alas. I’m still hoping for some kind of simplification of the licensing but maybe I’m being naive. At the very least some very clear and concise guidance will be needed to help customers understand the prerequisites and licenses necessary for each of the Power BI components. For example a customer might want to use Power Map, know that Power Map is part of Power BI, hear generalised statements like “Power BI needs Office 365”, and therefore think they need to have an Office 365 subscription to use Power Map – which is not true. Microsoft needs to get to the point where it doesn’t take a long and complicated blog post from someone like me to explain all of this.

Of course the dependency on Office 365 for the cloud-based components remains controversial. I’ve been explicitly told by Microsoft that the rumours that there are no plans to incorporate Power BI cloud features into Sharepoint on-prem are wrong. Features will show up in the cloud first, and while it may prove to be difficult to implement some features on-prem, in general most features will make it to on-prem Sharepoint eventually. In any case, I think enough customers will move to Office 365 for Power BI to gain some traction, notwithstanding all of the very valid worries companies have about storing data in the cloud and the general inertia around upgrading Office in large organisations.

Conclusion

I think Power BI has the potential to make Microsoft a player in the self-service BI market and to re-energise the Office platform. If Microsoft can keep up the momentum with development and if it can persuade its customers to move to Office 2013/365 and the cloud then it will at last be able to take full advantage of Excel’s gigantic user base to bring BI to the masses. There are a lot of “ifs” here though, and the current mess of Office SKUs and licensing could strangle it at birth. I hope this doesn’t happen.

Share this:

Like this:

Last week a new, preview version of Power Query was released to work with the Power BI public preview (John White, whose blog has a lot of good Power BI information, has the details here); today, a new version of Power Map was released too. You can download it here:

You can now overlay certain geographical regions onto a map. For example, with the following table in the Excel Data Model:

In Power Map you can create a new layer type of Region and see each country shaded by their sales value:

The Region shapes are sourced from Bing; you can’t upload your own shapes unfortunately. I have no idea what regions Bing does know about, but it clearly knows about English county boundaries (though it doesn’t know about postcode boundaries):

You now have the option of seeing a ‘Flat Map’. Here’s the map above shown in flattened form:

You can now control the colours used in a layer:

You can add annotations with images in:

You can now record tours from within Power Map and save them to an MP4 file.

Calculated columns and hidden columns can now be referenced in a layer.

There are plenty of other changes – I’ll update this post if I’ve missed any other major ones – but in addition the app seems smoother and faster, as well as being (slightly) easier to use.

Share this:

Like this:

Here’s something strange I came across today: a customer had created a calculated member, not on the measures dimension but on another hierarchy, and even though it had been selected in their client tool it wasn’t appearing in the query results. I tested in Excel and saw some strange error message. I was mystified, but after a bit of thought I found out what was going on…

Take the following query in Adventure Works:

WITH MEMBER [Customer].[Gender].[CALC] AS 123 SELECT {[Measures].[Internet Sales Amount]} ON 0, {[Customer].[Gender].&[F], [Customer].[Gender].&[M], [Customer].[Gender].[CALC]} ON 1 FROM [Adventure Works]

When you run it, you get the results you would expect:

Now, if you change the calculated member name, you’ll see the problem that my customer was running into:

WITH MEMBER [Customer].[Gender].[Gender] AS 123 SELECT {[Measures].[Internet Sales Amount]} ON 0, {[Customer].[Gender].&[F], [Customer].[Gender].&[M], [Customer].[Gender].[Gender]} ON 1 FROM [Adventure Works]

Instead of the calculated member, you now see Female and Male repeated twice. The reason why this is happening is down to how SSAS interprets the expression [Customer].[Gender].[Gender]. It’s the name of the calculated member that’s been defined, but it’s also the unique name of the Gender level on the Gender hierarchy of the Customer dimension:

As I explained in this blog post, when SSAS sees the unique name of a level it sticks the .MEMBERS function on the end and this returns the set of all members on the Gender level of the Gender hierarchy – and not the calculated member.

Of course, if you rewrite the query to use the ALLMEMBERS function as follows:

WITH MEMBER [Customer].[Gender].[Gender] AS 123 SELECT {[Measures].[Internet Sales Amount]} ON 0, [Customer].[Gender].ALLMEMBERS ON 1 FROM [Adventure Works]

…you’ll see the calculated member returned:

So, the moral of this post is don’t create calculated members that have the same name as a level on a hierarchy. It’s unlikely that you’ll do this, but possible – in my customer’s case they had a hierarchy on a time utility dimension called “Periods To Date” and a calculated member on that hierarchy with the same name.

Post navigation

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk