I’m not at the Microsoft Data Insights Summit, unfortunately, but I watched the live stream of the keynote today and was duly overwhelmed by the new features announced. I’m not going to summarise what was said because there’s already a post on the official Power BI blog that does that here:
…but I wanted to pick out a two topics for more in-depth discussion.
Query Power BI Datasets from Excel
For me, by far, the biggest announcement made today was that by the end of this month we’ll be able to connect an Excel PivotTable up to a Power BI dataset and query it as if it was an Analysis Services cube or a local Power Pivot Data Model. In my opinion this a true killer feature. For the last few years my customers have asked me when MS was going to release SSAS in the cloud and I’ve always replied that Power BI is SSAS in the cloud – it’s just tightly coupled with a front-end right now. That makes things simple in some ways but it also causes problems:
- You have to build your data model and reports all in the same file, which is a pain because when you want to update one you need to deploy both
- Other report authors who want to use your model for their reports need to get hold of your .pbix file and edit it
- Everyone has to use Power BI to build reports on data stored in Power BI
This move solves these problems. It makes manageability much easier: you can build a Data Model, publish it, then you and other developers can build reports on it separately. What’s more it means that all those users who prefer to use Excel, either because it’s the tool they are most comfortable with or because it’s the best option for building the reports they want to build (Excel has a lot of great functionality that Power BI reports don’t have right now and will probably never have), to build reports can do so without having to copy the data into Excel.
Even better, as Amir hinted, this isn’t going to just work with just Excel. If this works the way I assume it works you’ll be able to use all the other MDX-capable front-end tools out there, like SSRS or Pyramid, to query your Power BI data, meaning that you have even more choices for consuming data held in Power BI.
SandDance: A symbol of how MS is finally exploiting all of its assets
SandDance is undoubtedly a cool data visualisation tool and will be great for creating ‘wow’ demos. It’s also not, as the hype is suggesting, something new – I saw it on the Microsoft Research site at least two years ago. What is really important about SandDance is that it shows off another area that Microsoft has got right with Power BI: it has created a platform that other developers both inside and outside Microsoft has build on top of. In this case Microsoft Research had some eye-catching data visualisation technology but not something that made sense as a standalone tool (yes, I know it is actually available as a standalone tool but let’s face it, it wouldn’t make sense as a commercial product). Integrate this data visualisation technology with Power BI, though, and suddenly you’re part of a much richer product that is commercially viable. SandDance gets the chance to be used for real-world purposes by a lot more users; Power BI gets another great bit of functionality; it’s a win-win.
The ‘build a platform’ strategy is classic Microsoft. It’s the story of Windows. It has some disadvantages in that the different pieces might not always fit together as smoothly as it should (compare and contrast with Tableau, which could be seen as the Apple of the data visualisation world) but it gets useful, specialised features out there very quickly. Microsoft has a wealth of great technology scattered across its different divisions that, historically, it has struggled to bring together coherently, but it looks like Power BI is managing to buck the trend. The integration with Azure Stream Analytics and Azure SQL DW that exist today, the integration Power Apps that was demoed in the keynote, the use of machine learning in Quick Insights and the integration with Azure ML that I also saw mentioned on Twitter today, are other examples of this happening.
Microsoft also has a strong partner community in the BI space that did good business building solutions on the SQL Server BI stack; Power BI v1 did not offer the same opportunities and was neglected by partners as a result but Power BI today offers partners a lot more opportunities and so the they are back out there building and selling on Microsoft’s behalf again. It’s happening in the Custom Visuals Gallery, it’s happening with the Power BI API and apps like Power BI Tiles, and it’s going to happen in a lot of other ways in the future too.
The question I have is are we going to be able to deploy these models in Azure as we do databases or are we going to be limited to hosting this in the Power BI cloud.
Yes, that is an important question. I guess we will have to wait and see!
exactly! there are quite a few companies out there who would be really excited for the ability to scale up/out on demand w/ SSAS… buying/licensing servers/CPU cores for several days each period is not cost effective.
Chris, the row level security and restricting access by groups/email are the other features that were previously only available in Analysis Services models. It will solve the deployment problem I have with restricting sales reports to only those stores that a manager oversees.
Chris,
I agree with you that the ability to connect an Excel PivotTable up to a Power BI dataset was one of the big announcements of the day. I’ll be curious to see if the refresh will also work in Excel Online.
My experience with refreshing OData connections in Excel Online is that the refresh will work if you have Enterprise Office 365, but not if you have Business Office 365. I assume that the Power BI refresh will work the same, but we’ll have to wait and see. If it does work the same, then Business Office 365 users will just have to keep using Power Update.
Hi folks,
I’m Aviv and I’m the product owner of the Analyze in Excel feature. The feedback here is priceless and I’m thankful that I have access to this blog. Also I want to update that for the first release of this feature we only support sharing workbooks based on an external connection to a Power BI data set in XL online with no interactivity at this stage, i.e. can’t do online refresh. But I do want to let you know that we are aware of the need and have this on our plate, sorry I cannot commit to a date at this stage.
I hope this helps, please feel free to contact me for anything I can be of help with, I hope I can do the same with you, to get more of your needs
Aviv
Thanks for chiming in Avive. Much appreciated!
Thanks for your patience, Analyze in Excel now available for Free users as well. Please test it and let us know if you have any other issues we can be of help with
Awesome! I just tried it and it works!
I actually just opened the old .ODC file that I created before and it worked so the fix must have been on the server.
Will the Export to Excel work for Power BI Desktop users (versus the Power BI service)? I only have access to Excel 2013 at work and have been building models in Power BI Desktop to experiment, but remain chained to the Excel data models, since I can’t get data back to Excel from Power BI Desktop. Without the ability to get data from my Power BI Desktop experiments to pivot tables or the majority not using Power BI desktop, it greatly limits the time I can afford to spend on Power BI models (as much as I would love to stick with bi-directional look ups and all of the new syntax available with Power BI).
Do you mean “Analyze in Excel” rather than “Export to Excel”? If so, no – the officially supported functionality only works if you deploy to the Power BI service. It does sound like what you need, though, assuming you are ok with deploying your data to the Power BI service.
It is actually possible to connect an Excel PivotTable to Power BI Desktop (see https://blog.oraylis.de/2016/02/connecto-pivot-to-power-bi-desktop/ for example) but it’s not supported and you need to have Power BI Desktop installed and open for this to work, so it’s not really practical either.
Chris – Sorry, yes I meant Analyze in Excel. Unfortunately, I don’t have the flexibility to deploy data to the Power BI service (at least not all of the data sources I need). I had been hoping that Analyze in Excel option would be available to Power BI Desktop as well, for those who are going to need to use on premise data sources for some of their work. The Excel Pivot Table to Power BI Desktop option is very interesting, even if a bit awkward.