Power BI

Power BI Preview Review

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.


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.

28 thoughts on “Power BI Preview Review

    1. Hi Chris,

      I have been reading your blog posts for about 2 years now, and am a big fan of your work! This place is where I usually hear it first.

      I have been playing around with the Preview for a week now, and found it very compelling (once you get everything to work). Seeing the Power BI app in action on a touch-enabled device (in my case – a laptop) is nothing less than pure joy! and with the iPad and Android versions looming this finally puts MS in the mobile BI space, which is becoming more of a basic requirement than a nice to have.

      Just a few thoughts:

      1. I do agree that the licensing scheme is confusing and hope it gets resolved.

      2. Would love to put my hands on the Q&A feature and see if it holds water.

      3. Power BI App – Power View reports render beautifully, and this shows that they were really designed to be consumed this way.

      4. Refreshing a workbook in the cloud. Took a bit of time to get to work (using “Cloud Access” method) and feels a bit buggy but I’m sure will be resolved. Not sure whether it would it be possible to use Odata feed to do the same thing?

      As a MS BI guy, it’s simply great to see they are making these crucial investments in their offering. The “stack” is so rich in tools now I find hard at times remembering how all the different components fit together!

      Thanks again and keep up the good work!

      Oren Horovitz

      (and a cheer to the Adatis boys)

  1. I have three clients, all SSAS users, 1 still on ProClarity and 2 on Tableau. I can’t explain the Microsoft BI strategy to them, because I don’t understand it myself. Tableau, a great tool, is becoming more powerful with each release, but with very few new features relevant to SSAS. MS needs to address the SSAS user community or lose it. Also, as a developer/consultant, what are my purchase options (for Office) to get the tools to learn them? I can’t even figure that out easily. My favourite tool is still ProClarity. But I do most work now using Excel Cube Functions and pivot tables. My new stats and graphing methods are Excel and Tableau for eye candy. I’d like to pick up the new MS BI tools, but they’re just not making it easy for me nor my customers.

    1. Hi Chris Gordon, I am using SSAS in my Power BI reports. I do not see any problem… 1. retrieving data from cube, 2. design data model using power pivot, 3. create nice visualizations using power view / power map. 4. Deploying report to Power BI site. 5. Auto Data refresh – this feature is currently not supported for SSAS. But there is a workaround for this. By using linked server connection with SSAS, we can refresh our SSAS data connection. If you want more details like how to retrieve & schedule auto data refresh for on-premise SSAS data, you can look into my recent post here.. http://raghavmaddali.blogspot.com/2014/04/power-bi-schedule-data-refresh-for.html

  2. I see no evidence that Microsoft is out evangelizing Power BI to its customers (the big companies that use Office). And, the use of Power BI by those big companies will require that they make a fundamental change in the way they approach BI and, more importantly, will require then to find employees that have skill sets that fit the Power BI paradigm (aka – Excel-centric data crunchers). Alas, there is a giant disconnect within Microsoft between marketing (the ones that are empowered to do the evangelism) and the technology groups that are making this great stuff. Maybe they should hire you or me so we can explain to them how to do their business (since you are already doing part of their job with this blog entry).

  3. Thanks a lot for this review. I share a lot of your concerns about the marketing side.

    It seems to me that Power BI leverage a lot over the excel data model, covering the Team BI needs in Microsoft terms. But quite interestingly, there is no gateway with Corporate BI. No way to use the data management gateway to use on premise OLAP data (tabular or multidimensional). It seems everything is based on the Excel Data Model, and that’s excluding an external model (as Excel can only have one model). Not even sure you can source the Excel Data Model of another workbook located in Sharepoint Online as it is possible in Sharepoint on premise.

    Power BI for Office 365 can’t use SSAS. BI folks will be happy …

  4. Thanks Chris for another well-written and informative review; I find the comments very helpful too. I haven’t played with the preview yet but have been testing Excel 2013 with and without Power Pivot. I concur that buying a product (like PowerPivot 2013) shouldn’t be made difficult by the seller; that seems like a doomed marketing strategy. And I also think it ‘s overdue for the SSAS user community to get more attention from MS.

  5. Thanks Chris, nice summary post as usual.

    The Power BI pitch is a sharp contrast to Corporate BI and is essentially a “Personal” (or maybe “Team” at best) Self-Service solution… and so there has never been a truer statement “…because self-service BI implementations will live or die by how well the data steward does his or her job”.

    Some of my customers are excited by this prospect and cannot wait to wear that hat – but others are downright worried as they aren’t ready for it or already have a nice Corporate BI solution in play and a poorly considered Power BI implementation will challenge the concept of Single Version of Truth etc.

    Thankfully Power Query will/can offer shared queries – this could be the answer to keep data sources manageable and move Self-Service BI solutions a bit closer to how Corporate BI (ETL) does it.

    I think there may some initial frustrations as customers find out how best to position this with the traditional Power Users.

    Mind you I’m not saying its a “bad” solution – in fact the more I use it the more I like it! 🙂


    1. I’m still waiting for my copy of the preview and am taking the time to learn some helpful techniques with Power Query. This product will have a learning curve and I think the “single version” of the truth will live on prem for most folks; linked up through excel to Power BI. To me the real power of this is reporting and external data sources augmenting existing data in house. Questions like “I know my turn over rate and why they left but how can i predict if a valued employee may be on their way out?” can be answered with more than just time cards and number context switches that occur for an employee.

      Other data sources were always available (job postings,housing prices) but with the Power BI suite I think it will be easier for DBA’s and BI professionals to leverage all these sources in to a comprehensive answer to “Why?”. Thanks Chris for the great post; much appreciated.

  6. Hi Chris,

    I’ve come across a problem with Excel 2013 and Power Maps. It seems they don’t handle dates before 01/01/1900. Do you have any knowledge of this or can suggest any workarounds?

    My dataset is between 1837 and 2007 and this causing a major headache. I would really appreciate any suggestions.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      I think this is because Excel itself (and that includes Power Pivot and Power Map) can’t store dates before 1900. The only workaround I can think of is to store dates as integers, maybe in the YYYYMMDD format, but of course this stops you using any of the specialised date functionality.

      1. Thanks for the prompt reply Chris. Always appreciated.

        I know you sometimes talk to Microsoft wallahs so can you suggest that it is time they resolved this issue. Have been doing more research and it appears this issue has dragged on for years and there are even differences between Mac and PC versions.

        Surely if they want Excel to be a serious analysis tool they should be resolving this sort of thing – the world didn’t start in 1900!!

        Do you know whether this would be resolved by moving the data into SQL Server and then using Power Map or is the latter dependent on the Excel engine?

        BTW Are you or your colleagues planning any in-depth courses covering the Power BI suite and its use with Office and Sharepoint and SQL Server? If so, let me know.

        Thanks again.

      2. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
        Chris Webb says:

        Hi Karl, yes I’ll certainly mention it, though I can’t guarantee that it will have any impact! Power Map only supports data in the Excel Data Model (ie Power Pivot) at the moment, but I know supporting SSAS is something they’re looking at.

        Funny you should ask about Power BI training… I have a Power Query/Power View/Power Map course and a Power Pivot course running back-to-back next year in London:

      3. As an Excel 2010 user who writes VBA macros, I have gotten useful results creating date-time stamps in a string format that sorts well: yyyy-mmdd-hhmmss for dates after 1900. You might even write some macros to do date arithmetic on pre-1900 dates in this string format. Arthur.hudson@verizon.net.

  7. Nice post Chris. Google frequently drives me to your blog and most of the cases I found solution for my problems. Coming to Power BI data refresh. Currently there are limitation to the power BI data refresh and not supporting SSAS data refresh. But I tried some work around to do this and I am successfully refreshing cube data. I tried with linked server connection and data refresh is successful. Here I wrote a detailed blog post, explaining everything related to SSAS data refresh – http://raghavmaddali.blogspot.com/2014/04/power-bi-schedule-data-refresh-for.html

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.