Power BI Licensing and Constraints

Licensing is a topic which most people reflexively ignore. They just scroll down until they see the I agree button and click it so they might attain the object of their desire, software with which to amuse themselves.  With respect to Power BI, now might be the time to read the fine print.  The license dictates how much data you can display, which you need to evaluate to see if this is the best tool for your environment. Let’s go to the authoritative source regarding such matters, Microsoft.  Here’s a link to their Power BI release and pricing page .  If you didn’t know, as of right now, Microsoft is only licensing Power BI with Office 365.  Based on their page it appears if you don’t have Office 365, you can’t have Power BI.  If you have been playing around with Power BI, you know that’s not really the case.  

Power BI Components

To ensure that we are all starting from the same place, here’s a summary of the Power BI Preview components. Power BI consists of four Excel add-ins, Power View, Power Pivot, Power Query and Power Map.  Power windows are not included.  Together these tools are used to create a Power BI document to be shared on to the Power Cloud, SharePoint on Office 365.  The add-ins Power Query and Power Map were created for the Power BI preview, because two powers are just not enough you need a quad. If you want to be able to deploy a Power BI report to your tablet or smart phone with HTML 5 or use the natural language query feature Q&A to manipulate the data, both of which are part of the preview, you can only do this from Office 365 SharePoint.  If you desire to distribute your Power BI creation to a mobile device such as a smart phone or tablet, you will then need to send the app to the Windows Store so you can distribute your report app to said devices. 

Help! I don’t have Office 365

If you have loaded Office 2013 or Office 2010 service pack 1 to your computers can you still use Power BI? This is where licensing gets tricky. Sure, you can do it, at least at present. Power BI is in Preview Release mode, and as part of the preview you can download the preview add-ins for both of those versions of Excel. Can you use the add-ins you have once it goes into full release?  This remains unclear.  Excel 2010 has Power Pivot included and Excel 2013 has Power View also so both of those components don’t require a Power BI license.  But Power Map and Power Query add-ins both say they are Preview Editions. What happens after the preview is over? Yet another ambiguity.

If you have seen Power View back when SQL Server 2012 was released you may think that you might have all of the same features of Power BI without having Office 365, but that isn’t the case. Power View was released as part of SQL Server 2012, so if you have SQL Server 2012 you can use Power View with SharePoint 2010 or 2013.  Power View for SharePoint has a map feature as well but the feature set is not the same as Power Map. What about Power Query on SharePoint? Power Query is part of Power BI and right now it can’t be loaded on your current SharePoint server.     

Preview Edition

As of this writing, Microsoft has yet to offer Power BI for sale to end users, although you can easily obtain a preview of it.  If you do decide to give Power BI a whirl, you will be granted a preview license.  The preview license terms differ in significant ways from the proposed release version.  Keep this in mind when you noodling around with Power BI.  If you want to load a file to the Cloud (aka SharePoint on Office 365) the maximum file size is 10 MB.  I divined that tidbit here .

Implications of Excel File Size or Why my Power BI won’t load

You may have learned, like I did in an online presentation, that the maximum size for a Power BI Excel file is 250MB. .  I discovered the file size is dependent on your license, which creates some interesting things to consider. The 250 MB file size does not apply to the preview license version, which seriously dampened my enthusiasm. I was rather chagrined to discover this inconvenient limitation, after my carefully crafted 45 MB presentation file failed to load.  I started looking around for the size that would load, as it was clear mine was too large, making it clear the 250MB limit isn’t true,  as the preview license limits you to 10 MB.  When you buy Office 365 you pay for how much space you have in SharePoint and there are limits which Microsoft reveals to you here . This website states that licensees only have 500MB per user regardless the plan for Office 365.

Power BI is meant to be the tool for all business users to query their data. If you are designing a model for use in Power BI your space is limited.  Given it’s current configuration, it is assumed your entire operational data store could not possibly exceed 250 MB.  If you have a large tabular model, you may find using Power View in SharePoint will provide a better solution as these data model constraints don’t exists there. Power BI is great tool, but the scalability limitations cannot be overlooked.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Power Query and Power Pivot – two ways to load up Excel with data

If you’ve have messed with pivot tables in the past, you know that adding data to an excel spreadsheet is nothing new.  You now can add data three ways, because you can now add data with Power Pivot and Power Query.  The question is which tool should you use when?  Well if you have Office 2013 and didn’t sign up for the Power BI preview, you are limited to using Pivot Tables.  Since I signed up for the Power BI preview I was left with another question.  Which tool is the right one to use for loading data?  Well it’s the same answer you always get when working with computers.  It depends. 

Power Query was designed to readily provide access to data, any data.  Microsoft on their site promotes the fact that you can include, or as they like to say “Mash” data from Wikipedia or other sources together with your business data.  I can see where this feature would be very useful.   I work with a concrete company who has a lot of KPIs that they have for daily production.  Well, when it rains they don’t meet the performance metrics because no one wants to pour concrete in the rain as it affects the way it cures and the concrete can end up bumpy instead of smooth.  If they correlated the weather together with the KPIs they could definitively determine the effect of rain on their business. Power Query also provides a way of merging or appending data together, which makes sense since you may be selecting data from a variety of sources

If you are pulling together internal data so that you can create a Power View report on it, Power Pivot would be the best way to proceed.  Yes, you can do this in Power Query, but Power Query doesn’t provide the same level of filtering that Power Pivot does.  Power Pivot also provides the dangerous feature of allowing users to update data in the database by modifying the data in Power BI a spreadsheets, a feature which most of the time I believe will be turned off. 

With Power Pivot, not only do you load up the data, but you also build the relationships between the data.  It works the same way if you user Power Query for half the tables in your database and Power Pivot for the other half.  I felt compelled to check that out when I was playing around to see if it would make a difference.  Of course Power Pivot can do a lot more than just relationship management of data.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

Do I really have the Power?

Is just it me or does anyone else keep on hearing that stupid Power song by the one hit wonder Snap whenever they are hear about Power BI? Now if you cannot recall this catchy tune this should send it rattling around into your skull It’s the new Rick Roll.

Is it possible that adding the word Power is supposed to be a subliminal message to people who’s lives are spinning out of control and have been sapped of their personal essence? But then again I could be wrong. You add data by Power Query, then manipulate it in Power Pivot, then make a Power View and add a Power Map. And naturally this task should never be attempted at home and only by Professional Power Users. I feel that by using it I have increased my power exponentially by a factor of 365 to complete this task. On the other hand, I could be totally wrong. Power BI is available as a preview edition, which means for now anyone can load it up as it has not been officially released. Still though the more time I spend with it the more I generally like it.

Although you could argue that it might have made more sense for Microsoft to build a reporting app than bloating up Excel, it’s perfectly understandable why they did it. Based upon what I have seen, there is a real reticence on the part of the IT staff to install anything else on a computer. Given the things end users wish to install on their computers, one could hardly blame the IT staff. The idea of self-service BI is for business users to use it, and Excel has been the staple of ad-hoc data storage and analysis for decades. With the significant importance of Excel in data analysis, enthusiasts generally opt for Excel training courses (like excel classes in Denver) to enhance their skills and knowledge. On the contrary, Power BI is a series of task-specific bolt-on accessories to the ubiquitous Microsoft spreadsheet; using it isn’t the most intuitive thing in the world either. It is sort of awkward to click here and there to get the tool to work, but once you figure that out, in the end it turns out to be a cool tool. It has changed quite a bit since it was first introduced. Microsoft’s blog from July includes a walk through of Yelp data which will no longer works as written because the expand feature has been removed.

Power BI really makes Microsoft competitive in an area where they had nothing but a gaping hole which their competitors, namely Tableau, have driven their truck through. In less than two years they have come up with an application which provides self-service BI to business users, uses in-memory analytics, can be viewed securely on the web, and can be viewed on tablets and phones. Since Microsoft is slowly but surely getting rid of Silverlight to follow everyone else to HTML5, you can view your Power BI reports on whatever phone and tablet you happen to own. Ok, the caveat is that the application that was created for the Microsoft phone is better, but you are not shut out if you have an iPhone or Samsung tablet either.

From a marketing perspective, Microsoft has provided perfectly sensible reason that you might want to actually get Office365. Personally Power BI was the reason that I decided to move to Office 365. Let’s face it, for most people Office 365 was just another way to load up office. The whole cloud thing was not really much of a selling feature for end users who didn’t want to use a skydrive when they could easily use a hard drive ensuring your data isn’t spread all over the known universe. Hosting SharePoint in the cloud provides a feature people might actually use. There is an argument to be made as to whether or not it is any less annoying to administer SharePoint via a cloud rather than locally, but it is one less server IT needs to monitor. The price isn’t terrible. Here’s the info which Microsoft has released on pricing

I am giving a talk at SQL Saturday in Albuquerque on Power BI where I will delve the technical features of Power BI in greater detail.

Yours Always,

Ginger Grant

Data aficionado et SQL Raconteur