Archive for the ‘Power BI’ Category
There were a couple of big (well, big if you’re a Power Query fan like me) announcements made today by Miguel Llopis at the PASS BA Conference:
- Today Power Query is available only to people who have Excel Professional Plus or Excel standalone, but as of May a version of Power Query will be available on every Excel SKU. There will be some limitations around data sources that are supported if you don’t have Excel Professional Plus, but that’s ok – this change will make it much easier for people to learn about and use Power Query, and I’m really happy about that.
- Other new features coming in the May update of Power Query include the ability to turn off prompts about native database queries (useful in this scenario, for example), OData v4.0 support, the ability to use alternative Windows credentials to run queries, and a couple of new transformations such as removing empty rows.
- Excel 2016 – where Power Query is now native to Excel – will have support for creating Power Query queries using VBA and macro recording. I understand you won’t be able to edit individual steps in a query, but you’ll be able to create and delete queries programmatically and change where they load their data too.
- Excel 2016 will also support undo/redo for Power Query and give you the ability to copy/paste queries (even from workbook to workbook).
- There was a commitment that Power Query in Excel 2016 will keep getting updates on a regular basis, rather than get tied to the much slower Office release cycle, so it retains parity with the Power Query functionality in the Power BI Dashboard Designer.
All very cool stuff!
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?
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.
I wonder if I can justify buying one for demo purposes?
At the end of last week I came across an interesting link on Twitter (thanks Devin) with more details on how the ‘new’ Power BI will be licensed, and how existing Power BI subscribers will move to the new experience, that I thought was worth highlighting. Here it is:
Some points to highlight:
- The Power BI service will become a standalone service and will no longer require SharePoint Online
This is not exactly a surprise based on what’s been announced so far, but it’s the first time it’s been clearly stated that the ‘new’ Power BI is not dependent on Office 365 and SharePoint Online
- At general availability of the new experience, existing customers will be asked to move to the new experience
That’s to say the ‘new’ Power BI is a direct replacement for the old, O365-based Power BI, and that there will only be a single version – the ‘new’ version – of Power BI in the future
- Power BI will seamlessly work with Office 365 for customer with subscriptions to both
This move will require configuring the new experience to connect to existing Excel workbooks. Users will need to either point to their Excel workbooks on SharePoint Online or reload these workbooks into Power BI
If you are an existing Office 365/SharePoint Online/Power BI customer, there will be integration between SharePoint Online and the new Power BI service – so it looks like no functionality is going to be lost in the transition
- Existing Power BI for Office 365 customers will be able to transition from the existing user experience to the new user experience when it is made available using their existing subscription license…
This license transition should not interrupt access to the Power BI service.
So existing customers should be able to move to the ‘new’ Power BI very easily when it is released (remember, we’re currently in preview and the preview of the cloud service is only available to US customers)
If you’re a new customer thinking of signing up for the ‘new’ Power BI, you should check out the licensing options here:
…and also this post on the Power BI support blog on making sure you sign up to the service with the appropriate email address:
There’s a lot of cool stuff in the new Power BI Designer desktop app, but for me the most important new bit of functionality is one that’s not immediately obvious: relationships between tables in the data model have had a significant upgrade. Let me illustrate…
First up: relationships can now filter in two directions. Consider the following two tables in an Excel workbook, a dimension table called Fruit and a fact table called Sales:
When you first load these tables into the Power BI Designer no relationships are created between the tables. To create relationships you need to click on the Manage button on the Home tab so that the Manage Relationships dialog appears. You can then click the Autodetect button and the relationship between the two FruitID columns is created.
However, click on the Edit button and you’ll see something interesting. In the Edit Relationship dialog, under Advanced options, you’ll see that the Cross filter direction is set to Both (the other option is Single).
This means that, not only can you create a report like this with FruitName field on rows axis of a table in a Power View report along with a measure showing the sum of values from the Sales field:
But you can also now take the Date field from the Sales table and put it on rows in the report along with a measure showing the distinct count of values from the Fruit Name field from the Fruit table:
The relationship between the two tables is working in both directions, from the dimension table to the fact table and from the fact table to the dimension table, which is a big change from Power Pivot in Excel where a relationship can only work in one direction (from the dimension table to the fact table). You can still get the original Power Pivot relationship behaviour by setting the Cross filter direction property to Single.
If you thought that was impressive, there’s another implication of this change: many-to-many relationships now work automatically. No nasty DAX is necessary – which is lucky because, at the time of writing, there’s nowhere to use DAX in the Power BI Designer. Here’s the same data as above but with two more tables, so that there is now a classic many-to-many model with a dimension table called Group and a factless fact table called GroupToFruit associating each fruit with one or more groups and each group with one or more fruit.
Here are the relationships in the model, all of which were created using the Autodetect button and all of which have their Cross filter direction set to Both:
And here’s what you see in a report when you put GroupName on rows with a measure showing the sum of Sales:
The sales value for Berries is 35, the sum of the sales for Raspberries and Strawberries; the sales value for Red Fruit is the same because that group contains the same fruit; but the grand total is not the sum of the groups but the total sales for all fruit.
Anyone that has tried to build a reasonably complex model in Power Pivot or SSAS Tabular will understand how big a change this is. Up to now if you wanted to use many-to-many relationships you needed to add extra DAX code to each measure you created, and that added an unwelcome layer of complexity; now it just works. I haven’t thought it through properly yet but I bet that many other modelling scenarios can now be solved with this new functionality too. Time to do some thinking…
There’s been a lot of Power BI-related news in the last few days. I think it’s enough to say that there is a new Power BI in town, a very different (and much better) story from what we’ve had up to now. Sadly the preview is only available in the US at the moment.
I’m not going to repeat what the official announcements say, though, and you can read them here if you haven’t seen them already:
Here’s the main Power BI website:
Here’s the documentation for the APIs:
Here’s the YouTube channel with lots of videos:
Here’s the help:
So what does all this mean for us, the Microsoft BI community?
One of the biggest complaints I’ve heard from people who are just starting to test Power Pivot and Excel 2013 is that it just hasn’t been stable (even compared to Excel 2010 Power Pivot). If you’re evaluated new software then crashes and weird behaviour are a massive turn-off, and I sincerely hope that the new update will improve the situation here and make people’s first impressions of Excel as a BI tool much better.
Power BI Decoupled From Excel And Office 365
OK, so Excel is still important but it’s no longer the centre of the Power BI universe. We can now create models and reports in the browser, and even more significantly we have the Microsoft Power BI Designer, a standalone app which is basically Power Query and Power View (no Power Pivot as yet, but I’m sure that will come) bundled together minus Excel. Also, dashboards and reports no longer have to be stored inside SharePoint, there’s a separate site at https://preview.powerbi.com/ for viewing and even creating them.
This is noteworthy for several reasons:
- The whole Office 2013 Professional Plus/Office 365 requirement is now gone. Yes, Power BI will integrate well with Office 2013 and Office 365 if you have it, but you don’t need them any more. This removes a massive barrier to adoption for Power BI: in the past year or so I’ve seen lots of customers get excited about Power BI and then realise they can’t use it because their organisation isn’t using the right version of Excel, or isn’t an Office 365 customer, or they have the wrong SKU of Office 365, and so on.
- We no longer have to wait as long for new functionality. The new HTML5 Power View is available in Office 365 and PowerBI.com but in Excel on the desktop, even after the latest updates, it’s the old Silverlight Power View still. Tight links to Excel slow down the delivery cycle and prevent Power BI from competing effectively. Excel has clearly not been abandoned but my guess is that it won’t see new features arrive in Excel at the same pace as the rest of Power BI because, well, it’s Excel and it moves very slowly (the Excel team are rightly very cautious about new functionality – if Excel is broken then millions of companies around the world have big problems).
- There is a down side to this: more choices and more confusion over which tool to use. Do you build dashboards and reports with the Power BI Dashboard Designer and PowerBI.com, with all the latest Power View chart types and other cool new stuff; or good old Excel, with its mature, well-understood functionality like PivotTables, cube functions, and the worksheet, plus SharePoint Online and Power BI sites; or a mixture of the two? How well do the options work together? What functionality is available in each option exactly? More conservative users will opt for the latter; BI consultants like me will probably go for the former. But it’s yet another difficult conversation to have with the customer.
Is The New Functionality Any Good?
There are three ways of answering this question: looking at the merits of the new functionality on its own, as it stands now; looking forward to what it will be soon; and comparing it with the competition. The new Power BI is certainly a big improvement over what we’ve had so far, and answers a lot of long-standing requests in terms of functionality. The simple fact that it looks sexy is in itself a great selling point. Here, for example, is what a treemap looks like in a Power View report stored in Office 365:
Here’s a dashboard (though I think ‘dashboard’ is a misleading term here, because it’s not what a lot of us would call a dashboard) from PowerBI.com:
It’s fast and I am impressed with how easy it is to use too. Although it’s not ready for production use yet (see below) I can imagine it will be quite soon and it should be good enough to win some deals then. It’s still a long way behind some of the competition in some respects, such as variety of chart types, but in other respects (such as the kind of transformations available in Power Query and the types of advanced calculations that are possible in Power Pivot) it’s as good if not better.
Mobile BI On iOS At Last!
At long last we have a mobile BI story on iOS. I haven’t played with it but I have seen demos and it’s pretty good. The touch interface is cool.
This Is Still A Preview
There’s a lot of functionality missing in the current build by the looks of things. The Power BI dashboard designer includes Power Query and Power View, but what about Power Pivot? I’m surprised it’s not included, and I strongly suspect it will be in a later release. Where does Power Map fit in, if at all? We can connect to on-premises SSAS Tabular, but when will be able to connect to Multidimensional? It will be interesting to see what gets released in the next six months. I bet we’ll also see new chart types and data sources very soon. I hope the Power BI team copy the approach the Power Query team have taken and release new builds every month.
And please, please, please Microsoft don’t screw up on the licensing again. No details on it have been released yet. I hope they keep it simple and affordable.
And It’s Still In The Cloud
Apart from the whole Office 2013/365 issue, the other major blocker to adoption of Power BI is the fact that it’s cloud based and that hasn’t changed. Some customers (especially in Europe) are never going to consider a cloud BI tool because of data privacy concerns, and I don’t think that’s going to change soon. Then there are the loyal MS BI customers who have invested heavily in on-premises SharePoint and can’t just ditch all that infrastructure to move to the cloud. Microsoft needs to have a Power BI story for these customers too.
Finally, what I think could be the killer feature for Power BI: the APIs. Microsoft products are most likely to succeed when they give the partner community a platform to build on. Even if Power BI might not match some of the features of other products out there the ability for a partner to push data into Power BI and support obscure data sources will, I think, allow Power BI to beat its rivals in many cases.
Overall I think the changes that Microsoft have made to Power BI are the right ones in the circumstances. If I’m honest, over the last year I saw a lot of hype for Power BI, a lot of interest in it from customers, but while Power Pivot and Power Query are being used more and more there are very few customers who are using the whole package. The reasons are clear: the Office 2013 ProPlus/365 requirements; the fact it’s in the cloud; and the fact that it’s horrendously difficult to understand what Power BI even is and how the components fit together. The new Power BI deals with some of those problems but not all; I hope Microsoft has done enough to ensure that Power BI gets traction in the marketplace. The new functionality is really good and I’m a lot more optimistic about the future than I was. If Microsoft can keep up the momentum it will have a hit on its hands.
I have recorded two new videos for Project Botticelli about Power Query that are now available to view. They are:
What Is Power Query?
This is short introduction to Power Query is free to view (registration required) and covers just the basics.
Power Query Fundamentals
This 45 minute video goes into more detail about all of the cool things you can do in the Power Query Query Editor. You’re going to need a subscription to see this, but if you subscribe you will of course get access to loads of other videos too (including my MDX course, Marco and Alberto’s DAX videos, and lots of other cool MS BI content).
Don’t forget that my Power Query book is still available (even if it’s now a little out of date because the Power Query UI has changed over the last few months, you’ll probably want it for the more advanced content which is still good) and that I have several classroom-based training courses on Power BI and SSAS in London next year.
Yesterday we heard (again) that SSAS will be supported as a data source for cloud-based reports in Power BI. Today, in a session, two new important details on this emerged:
- It will work with both Tabular and Multidimensional
- It will connect as the user running the query, so SSAS security (eg dimension security) will work just the same as it does on-premises. No special setup will be needed; there were no details apart from the fact it will work using the EffectiveUserName connection string property.
I’m sure a lot of people will be interested to hear this…