Microsoft BI – Power to the User

This past month, Microsoft announced Power BI for Office 365. With this announcement, Microsoft has finally started to bring their BI strategy into a clearer focus. With the “Power” branding it is now easier to identify the user-focused tools for business intelligence or, as they say in their marketing, “Self-service Business Intelligence for all your data.” I am truly excited about what this means as it really puts an emphasis on BI for users. Let’s take a look at the offerings currently included in the Power BI product.

Power Pivot

We have to point out the obvious, there is now a space in the name. It is no longer PowerPivot, but instead Power Pivot. Power Pivot is the foundational product in Power BI. It is a SQL Server 2012 add-in for Excel 2010 and embedded in Excel 2013. The focus on Power Pivot is the modeling of the data for use within the visualization tools – in this case Excel.

Power Pivot

With the addition of the diagram view, the ability to model data is even easier. Power Pivot is the in-memory data storage location which can be deployed to SharePoint or SQL Server Analysis Services – Tabular Model.

While it is the oldest of the Power BI offerings, its capabilities are awesome as it brings the ability to mash up data and to model data to the desktop. The data can come from a variety of sources including relational databases, OData feeds, SSRS reports and more. A drawback of Power Pivot, was that it was limited to pulling in data in the “raw” without the ability to massage the data. We have done many projects with Power Pivot that required that we first move and transform the data to a consumable format for Power Pivot. The next product brings data transformation capabilities to the desktop as well.

Power Query

Power Query, aka Data Explorer, brings the capabilities of ETL solutions and expanded data search to your desktop.

Let’s start with the ETL capability. This is called “data shaping” in Power Query. Once you have connected to a data source (options include SQL Server, web, Hadoop, and many more), you are able to start shaping the results. What is very cool about this is that the query can be saved and when the data is refreshed the defined steps will be executed against the data again.

In the example below, the rows were filtered. Then columns were removed. And, finally, the related data was pulled in and an aggregated column was added. The drop down in the image shows some of the other shaping activities available to the user. Most importantly, this is very easy and intuitive to use. Once you have the data you want, you can add it to your data model which will put it into Power Pivot.

Power Query

Not only will Power Query shape your data, it allows you to search for data. In the image below, I search for favorite ice cream flavors which resulted in search results from Wikipedia. You can hover over the item in the list and see a preview of the data. This search functionality will also interrogate other sources including Bing, the Azure Marketplace, and even corporate data when using the Office 365 version.

Power Query

Power View

Power View is a data visualization tool that was developed by the SQL Server Reporting Services team and released originally as a SQL Server 2012 add-in for SharePoint 2010. This is meant to be a user ad hoc reporting and visualization tool which uses in-memory data stores such as Power Pivot and SSAS Tabular Models as its data source. Power View has since been expanded to include support for SSAS Multidimensional Models. Power View is a highly interactive UI built in Silverlight. Power View was embedded in the Excel 2013 and is now available on your desktop. When used with SharePoint 2013 or Office 365, an Excel 2013 workbook with Power View visualizations can be uploaded and viewed interactively in browsers which support Silverlight.

Power View

Power Map

Power Map is the new name for the Geoflow Excel Add-In that was revealed at the PASS Business Analytics Conference in Chicago this past spring. Like Power View, it provides a highly visual representation of data in terms of geography over time. While it is still in preview, it is still a very cool visualization. (For information on installing and creating your first Power Map visualization check out my blog post

Power Map

The concept of scenes and tours allow for some awesome visualization which move over time and over space as needed. I highly encourage you to check out Microsoft’s latest visualization tool.

Power Q&A

Finally, Microsoft just announced the Power Q&A tool. This is also in preview, but will likely be a great tool as well. Power Q&A is designed to be a natural language query tool which can be hooked up to data within your environment. Users will be able to add questions to a speech bubble such as “show polls which ask about ice cream” and results will be returned as tables, charts, or graphs. The Power Q&A engine will try to determine, based on the results, what would be the best representation for the answers.

Power to the User

All of the Power BI tools released by Microsoft are focused on pushing business intelligence closer to the business. BI in the hands of the users is the most valuable and compelling. This does not mean that there is no longer a place for data marts, data warehouses or similar storage techniques, but it is more likely that these will be created to support Power BI.

Additional Information

For more information on Power BI for Office 365 check out

To see a cool demo of Power BI in action check out

More on how BI is changing: The Changing World of BI: Leading with Microsoft Excel -


© 2014 Magenic, All rights Reserved