Power BI’s First Year in Review

Since 2014 was the year Power BI was released, a lot of changes have been made, that I thought it might be interesting to review. For all who saw this headline and thought, Well technically Power BI is not really a year old because it was released in February, allow me some creative license to perform some rounding. Listing the number of changes released points to the beauty of the iterative release schedule which allow changes to be rolled out whenever Microsoft is done with them, rather than at fixed intervals, which means changes happen often. There were more updates made than I listed here, but space and TL:DR limited me, and so only selected items are reviewed.


January 6th – Microsoft releases pricing info for Power BI.  This is one of the final steps taken before the product was released

February 10th – Power BI is released. Since Power BI was revealed and available for preview on July 8, 2013, the release wasn’t a surprise.  Having a released product meant that the previews people had been playing with for free for a while would be going away soon.

March 3rd – There were a lot of little updates made in this release.  The one that I thought was had the most impact were the changes made to HTML5 rendering, which got a lot better.  Q&A features were also notably improved. Microsoft touted the new connectivity to Business Objects, for those people who wanted it, that is probably quite useful.

April 1st –SQL Server 2014 is released.  I realize that this isn’t directly related to Power BI, but the products are designed to work together, which is why I selected to include it.

April 18th – Microsoft announced that they were not going to remove Power Map from Excel in Office 365. In January, when Microsoft announced  that Power BI was going to be released, they also made this announcement “On May 30, 2014 if you have the Power Map Preview installed, it will no longer work in any non-Office 365 subscription version of Excel”.  As you might guess, this announcement made people unhappy, and they complained to Microsoft. These complaints were heard and the policy was changed.  There is a perception sometimes that when you complain, no one hears you.  At least this one time, complaints were heard and changes made.  Here’s the caveat for Power Map for Excel users, you don’t get any of the new Power Map upgrades. The cool features added later can only be used by Power BI users, like the new feature of the ability adding sound, a new Power Map feature in this release.

May 8th – Forecasting in PowerView was demo’d at the PASS Business Analytics Conference.  Forecasting added the Predictive Analytics to Power BI.  What was nice about the new algorithm, not data mining, is that it also included Hindcasting to validate the predictions.  More information on this feature can be found here.

May 29th  – The Power Query Updates here made it easier to Query information from the Azure Marketplace as well as remember what you queried recently, which was most helpful. Q&A was also updated to have a wider number of natural language queries too.

August 19th – Skipping over the summer release stuff, the data source changes included in this release were really cool.  Now you can schedule a refreshes from a number new sources.

September 9th – Custom maps were added in this update.  Now instead of maps being limited to the world, you can map inside of buildings.  I know of a casino operation that really wanted this earlier.  This is another cool feature not available to Office 365 excel users.

September 30th – The Power Query update can be summarized as Seeking and Spamming. Finally Microsoft made it possible to not get updates when you didn’t want them.  The data source settings can now be sorted and searched, which is really helpful when you have a lot of sources.

October 13th  – Microsoft is making nice with the juggernaut SalesForce by adding Power Query support for data within it  Since it seems SalesForce is everywhere, being able to query the data will definitely be handy.

November 13th – Updates to Power Map mean that you can now play individual scenes instead of playing through the entire tour. This is a most helpful feature when editing a PowerMap.

December 19th – New Dashboards and Native PowerBI for the iPad.  Microsoft now has an Apple app so that you can see your data visualizations on Apple, just in case you wanted to do that.  While still in their infancy, the new dashboards are the first break PowerBI is really making from Excel as you don’t need it anymore to create visualizations.  I wrote about that new feature here.

New Year, New Updates

Looking back at everything that has happened this year, I can only imaging what next year will bring.  Given the current preview release status of the new dashboarding feature, it would be logical to assume that more changes will be added to it.  To be sure, when new things are added to PowerBI, you will be able to find more about them here.

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.  Since 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