Microsoft Power View

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.

Figure 1: Understanding how Power View fits into Microsofts overall BI strategy

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.

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.

 Figure 2: Launching Power View from a SharePoint library

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.

Figure 3: Building a report in Power View

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.

Figure 4: Adding a line chart within the tile to display economic growth by calendar quarter

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.