With SQL Server 2012, Microsoft continues its investment in self-service business intelligence (BI) with the introduction of Power View, an interactive, web-based data exploration, visualization, and presentation technology. I'll discuss the underlying architecture of Power View and how it fits into Microsoft's overall BI strategy. I'll then walk you through how to install it and take advantage of its capabilities.
Do We Need Another BI Tool?
Figure 1 illustrates how Power View fits into Microsoft's overall BI strategy. With a BI platform that already includes Microsoft Excel, Excel Services, SQL Server Reporting Services (SSRS), and PerformancePoint Services, you might be wondering why Microsoft introduced another BI tool. Although it does share similarities with these existing technologies, Power View extends ad-hoc reporting to a broader audience.
In most organizations, there are a small number of end users who are comfortable building Excel PivotTables and PivotCharts. Excel Services makes PivotTables easy to consume, but the workbooks published to Excel Services are fairly fixed in terms of layout and interactivity. The number of users who can design a report in SSRS is very small, even with tools such as Report Builder. PerformancePoint Services provides intuitive charts and grids, but their design and deployment is usually done by an IT professional. Plus, there's no easy way for end users to save personal views. Power View not only addresses these important usability gaps but also provides its own unique capabilities.
Installing Power View
Power View is a component of SSRS 2012. Specifically, it's part of SSRS installed in SharePoint integrated mode. To install Power View in your environment, you need SharePoint 2010 Enterprise Edition SP1 or later. To learn more about configuring SSRS in SharePoint integrated mode, see the MSDN article "Installing Reporting Services SharePoint Mode Report Server for Power View and Data Alerting."
Power View is designed to work against an intermediate, business-friendly tabular data model. This tabular data model can be either a PowerPivot workbook that has been deployed to a SharePoint document library or a database in a SQL Server Analysis Services (SSAS) 2012 instance running in tabular mode. Microsoft plans to support connectivity to SSAS running in multidimensional mode in a future service pack.
Getting Started
Power View reports are created in a web browser. Because Power View leverages Microsoft Silverlight, there's no desktop application for report authors (or consumers) to download, other than the Silverlight 5.0 runtime. Figure 2 shows an example of how to launch Power View from a SharePoint library, using a PowerPivot workbook as the data source. Power View can also be launched by clicking a Report Data Source (RDS) file or a Business Intelligence Semantic Model (BISM) file, a new type of data connection. The RDS or BISM file is stored in a SharePoint document library and points to either a PowerPivot workbook or a tabular mode database.
Once launched, the authoring environment looks and behaves like a cross between Excel, Report Builder, and PowerPoint. Similar to an Excel PivotTable, you can check fields (or drag and drop fields) in a table list to begin building a report, as shown in Figure 3. Another similarity to a PivotTable is that there's no need to switch between design mode and preview mode. Every change made to the report is instantly visible. Note that behind the scenes, Power View communicates with a tabular data model by issuing Data Analysis Expressions (DAX) queries. DAX was initially introduced as an expression language in PowerPivot. For more information about DAX, see the sidebar "A New Tool, a New Query Language."
Like Report Builder, Power View has many types of visualizations from which to choose. In my report, I wanted to analyze various economic indicators by presidential term. I selected the Tiles visualization so that each tile represents a president, and I converted the initial table to a card view (which displays each row from a table in a format similar to an index card). A tile acts as a container in that the card and any additional tables and charts will automatically be scoped to the selected tile (in this case, a particular president). Like a traditional reporting tool, Power View lets you arrange different tables and charts in a free-form manner. For example, in Figure 4, I added a line chart within the tile to display economic growth by calendar quarter. In addition to the line chart, Power View supports matrix, column, bar, and scatter charts.
Saving and Sharing Power View Reports
To save a Power View report, you simply click Save on the File menu. The report is saved as a single file, with an .rdlx extension. A report file can be saved locally, but it must be saved to a SharePoint document library in order for others to view it.
Viewing a saved report is straightforward. An end user simply clicks the report from a document library, and it's rendered in the browser. (Note that a user will need both Read and View permissions to view a Power View report.) From here, the user can print, export, or edit the report. Any user who is able to view a report is also able to edit it, but the user must have at least Contribute permissions to save any changes.
Creating BI Presentations
Power View has a few things in common with PowerPoint, one of which is the ability to create multiple views within a single report (analogous to multiple slides with a presentation). As shown in Figure 5, you select New View, then Duplicate View on the Home ribbon to create an additional view.
Another similarity is the concept of presentation mode. In Power View, you can click the Full Screen button on the Home ribbon so that the report screen takes up the entire computer display. A small set of navigation buttons lets you navigate between views, just like navigating between slides.
You can export a Power View report to PowerPoint by selecting the Export to PowerPoint option on the File menu. Each view is converted into a slide. While in presentation mode, you can select the Click to Interact option to interact with the view directly within PowerPoint. End users who manually copy and paste charts and grids into presentations will really appreciate this capability. Note that in order for the interactivity feature to work, the original report must be saved in a SharePoint library and accessible from the computer where the PowerPoint presentation is stored.
Filtering and Visualizing Data in Scatter Charts
An overview of Power View wouldn't be complete without a discussion of two unique capabilities: filtering and scatter charts. In Figure 6, I created a view that shows spending and debt information across four different charts. Each one of these charts, in addition to the presidential images, can act as a filter for the view.
For example, in Figure 6, the Democratic column in the Deficit – Percent of GDP by Party chart is selected. Notice how the other three parties' columns in this chart (Federalist, Republican, and Whig) are grayed out. On the left, the list of presidential images is now filtered to include only Democratic presidents, and the other three charts are filtered and recalculated as well. This might seem like a straightforward feature, but by interacting with this one view, you can get answers to several questions, such as:
- How much did a particular president spend? How much did a particular set of presidents spend?
- How much did a particular presidential party spend, and how much did it spend by function or subfunction?
- What is the effect on deficits if I remove a certain spending function or subfunction?
You might be familiar with scatter charts, because they've been available in Excel for quite some time. Then again, since Excel scatter charts can't be created from PivotTable data, they aren't as popular as other chart types. Power View makes scatter charts easy to create and adds an interesting level of interactivity through something called a Play Axis. For example, in Figure 7, I analyzed the economic growth of several countries (along the x-axis) along with the government debt as a percentage of Gross Domestic Product (y-axis), using the size of the bubble to measure the debt in US dollars. I added Calendar Year as the Play Axis, which lets me animate this visualization and watch the different measurements as they change year over year. In Figure 7, I selected the United States, Japan, and China to show what this animation looks like when you use the Play Axis.
Giving Users a Great Power View Experience
Power View connects to a tabular data model to provide an intuitive, ad-hoc reporting experience for business users. (I'll be writing about the tabular data model in more detail in an upcoming article.) When building this data model, there are a few tips, tricks, and limitations to be aware of:
· Default field sets and table behavior. When a default field set is defined, an end user can simply click a table name in Power View to automatically select a set of fields. In my model, both the USPresidents and WorldEntities tables have a default field set. In a model, you can also specify a default label (which is used as the primary label field for a card or chart) and a default image (which will be automatically selected in a tile view).
- Formatting. Numeric values can be formatted in Power View, but you can save end users a lot of time (and potential confusion) by explicitly formatting measures and numeric columns in the data model.
- Key Performance Indicators. KPIs can be defined in a tabular model, but Power View doesn't expose KPIs in its Table list. Although the measure representing the actual value of the KPI will be visible, the target value will only be visible if it's based on an unhidden measure, and not an absolute value.
- Hierarchies. Tabular models support user-defined hierarchies, but Power View doesn't expose these hierarchies in its Table list. Consider using friendly names that clearly identify the hierarchy levels. For example, in my USGDP_Accounts table, I've chosen a naming standard of GDP_Level1, GDP_Level2, and so on, for each column.
Exploring Power View Further
Because Power View is a very interactive and visual experience, I encourage you to view my accompanying video that showcases the full Power View experience. In addition, you can download the sample Power View report I created for this article (and the PowerPivot workbook on which it's based) from the Understanding the United States Debt website.
An Easy-to-Use Tool
During a recent demonstration of Power View, one of my customers said, "This tool looks like a lot of fun." If "fun" translates into an easy-to-use tool that helps people make sense of their data, then Microsoft has delivered another strong BI product. For customers already using PowerPivot and PowerPivot for SharePoint, Power View is a no-brainer for quickly delivering ad-hoc visualization capabilities to existing environments.
A New Tool, a New Query Language
If you use SQL Server Profiler to peek behind the scenes, you'll discover that Power View uses Data Analysis Expressions (DAX) rather than MDX queries to communicate with a tabular data model. Here's an example of a DAX query that returns all columns and rows from the USPresidents table, ordered by term:
EVALUATE ( USPresidents ) ORDER BY USPresidents[Term]
DAX queries make it easy to retrieve a flat (i.e., tabular) list of data and, based on my testing, tend to perform better than MDX queries when querying a large number of rows.
The introduction of DAX queries, however, doesn't mean that MDX is going away. Other Microsoft business intelligence (BI) tools such as PerformancePoint Services and SQL Server Reporting Services (SSRS) still communicate with a tabular data model via MDX. And MDX is the only mechanism for querying a SQL Server Analysis Services (SSAS) database running in multidimensional mode. In the future, there might be a blending of these two languages. For now, consider DAX another tool in your arsenal for querying and analyzing data.
