Power Tools – Microsoft’s Business Intelligence Toolbox

These days, looking at the business intelligence section of Microsoft.com can make a person feel as if they stumbled into the web site for the local hardware store. Everything is “Power” this and “Power” that. All of that “Power” can get a bit confusing. I’ll try to clarify so you can choose the right Power tool for the job.

For purposes of this article, I’m going to divide the business intelligence process into three areas:

Gather and Model

Visualize and Analyze

Share

Microsoft has Power tools for all of these areas.

Gather and Model

For ease and speed of analysis, data is gathered from various sources and placed in data model. The data model serves two purposes. First, it presents the data to the business user in a manner that they are familiar with. No weird table and field names to decipher. No complex language required to query the data. Second, the data model stores the data in such a way that it can be retrieved and navigated quickly.

Power Query

Power Query makes it possible to extract data from a wide variety of locations and assemble it into a data model.  Along the way, the data can be manipulated from its native form into a format that is more conducive for analysis. Power Query can extract data from a wide range of sources, including:

Once the data is extracted, it can be transformed into a format appropriate for analysis. Transformations include:

The transformed data can be loaded into an Excel spreadsheet or into a PowerPivot data model. (See the section on Power Pivot.)

As an example, Figure 1 shows U.S. state population data as found on the Internet. The data is exactly what is needed, but the format of the data does not work well for the type of analysis desired.

Figure 1

A series of data manipulation steps is applied to change this data to the desired format as show in Figure 2. The data manipulation steps can be seen in the Applied Steps window on the right.

Figure 2

Power Query is an add-in for Excel. It requires Office 2010 Professional Plus with Software Assurance, Office 2013 Professional Plus, Office 365 ProPlus, or Excel 2013 Standalone.

Power Pivot

Power Pivot is the data model architecture available within Excel. Power Pivot also has its own capability to extract data from external sources. However, Power Pivot cannot access quite the variety of sources that Power Query can. Power Pivot is also limited as to the data manipulation it can do while loading data. That said, if little data manipulation is required, it is possible that Power Query will not be required for a given data model and Power Pivot will be able to perform the data extraction

Power Pivot is extremely powerful, once the data is loaded and the modeling process begins. Millions of rows of data can be stored in a format that allows aggregation and retrieval in seconds. Aggregation in Power Pivot is accomplished through measures. Measures know how to sum, average, or count elements while paying attention to any filtering or grouping the user applies to the data during analysis.

Figure 3 shows the state population data in a table inside a Power Pivot model.

Figure 3

The Population item, which is selected, is a measure defined within this table. The formula for the Population measure is show in the formula area below the ribbon. More complex measures, which can provide additional analysis such as year over year comparisons and year-to-date values, can be created using the DAX expression language.

Power Pivot is an add-in for Excel. It requires Office 2010, Office 2013 Professional Plus, or Office 365 ProPlus. Power Pivot can also be used within SharePoint 2010 Enterprise Edition or SharePoint 2013.

Visualize and Analyze

Once the data is available in a Power Pivot model, it is ready for analysis and exploration. This analysis can be done using pivot tables and pivot charts in Excel. We will not, however, cover those tools here as they are not part of the “Power” platform.

Power View

Power View is a data exploration tool which creates presentation-ready visualizations with just a few mouse clicks. Power View is easy to use and produces high quality output. All of the authoring is done by selecting data from the Power Pivot model. Formatting is controlled by making selections from the Power View ribbon.

This simplicity of operation enables decision makers to quickly explore the data for insights without the technology slowing the process. This analysis is aided by the fact that everything in a Power View report is linked. Selecting a data item in one visualization on the report will not only filter or highlight the data in that visualization, but it will have the same effect on all of the other visualizations on the report.

Figure 4 shows a report created from the state population data model using Power View.

Figure 4

In the Figure, the New England data item is selected in the column chart. This causes Power View to highlight the New England data in the bar chart. It also causes Power View to filter the data in the table and the line graph to only show data for New England.

Power View is available in Excel. It requires Office 2013 Professional Plus or Office 365 ProPlus. Power View can also be used within SharePoint 2010 Enterprise Edition or SharePoint 2013 Enterprise Edition.

Power Map

Power Map provides a tool for creating animated explorations of geographic data. Power Map enables us to examine geographic data from various points of view. It is also very good at show how the data changes through time. These visualizations and animations are then put together into a video presentation. We can even add a soundtrack.

It should be noted that Power View also has the ability to present data on a map. However, where Power View provides a static representation of the data for analysis, Power Map creates a dynamic view of the data geared toward allowing the data to telling a story.

Figure 5 and Figure 6 show a Power Map presentation being created from the state population data.

Figure 5

Figure 6

Power Map is an add-in for Excel. It requires Office 2013 Standard, Office 2013 Home and Student, Office 2013 Home and Business, Office 2013 Professional Plus, Office 365 ProPlus, or Excel 2013 Standalone. Power Map can also be used within SharePoint 2013 Enterprise Edition.

Power Q&A

Power Q&A provides a natural language query environment for visualizing data in a Power Pivot model. The business user enters a question in plain English and Power Q&A selects an appropriate data model, retrieves the requested data from the model and creates a visualization of that data.

All of the Power tools discussed up to this point are available within Excel. Power Q&A is a different animal. It is only available through the Power BI web site (See the Power BI section for more information on the Power BI web site.)

Figure 7 show an example of a Power Q&A query. The English question entered by the user is shown in the text box. The automatic translation of that question is show in blue below the text box.

Figure 7

Share

Business intelligence is most effective when it can be easily shared with others. The Power BI platform facilitates this type of sharing.

Power BI

Power BI is a web interface designed specifically for working with Excel files containing Power Pivot models and related content. As noted earlier, Power Q&A is only available through the Power BI web site. Power BI provides a way for multiple users to interact with this content in a secure manner. Power BI also provides a way to automatically refresh the data loaded into a Power Pivot model.

Once an Excel file is uploaded to the Power BI site, a user can interact with any pivot tables, pivot charts or Power View reports in that file within a browser window. Excel is not required on the workstation. Excel is required to modify the content of an uploaded Excel file.

Figure 8 shows a Power BI site containing Excel files. Not the thumbnail of the first spreadsheet displayed for each Excel file.

Figure 8

Power BI is part of Office 365. Sliverlight is required for some Power BI features.

Power BI App

In addition to accessing Power BI content through a browser, users can interact with the Power BI site using the Power BI mobile app. Like the web interface, the Power BI mobile app allows users to work with any pivot tables, pivot charts, and Power View content in the Excel files loaded to a Power BI site. The Power BI mobile app does not provide access to Power Q&A.

Figure 9 shows an example of the Power BI mobile app.

Figure 9

The Power BI mobile app is currently available for Window 8.1 and Windows RT.

Conclusion

“Power” is the right word for this set of tools from Microsoft. Together they provide a powerful environment for performing all aspects of business intelligence. But like all good users, we want more - more features and more tools. You can be sure that Microsoft will respond with more Power in the future!