This content is part of the Essential Guide: Guide to SQL business intelligence, analytics and data visualization
Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server Power View makes BI reporting look simple

SQL Server 2012’s business intelligence tool puts report building in users’ hands. Read this tip from expert Robert Sheldon to how to get started.

EDITOR’S NOTE: This is the first part of a two-part article about Microsoft business intelligence tool Power View. It walks through steps needed to build a report. Part 2 focuses on how to create different views of data.

With the release of SQL Server 2012 comes Power View, a Reporting Services add-in for SharePoint Server 2010. Power View extends Microsoft’s SQL Server business intelligence (BI) offerings by providing business users with a Web-based environment for building ad hoc reports that offer a rich assortment of data visualizations. Built on the Silverlight application framework, Power View lets users create and interact with multiple views of data in order to facilitate information sharing, decision making and data analysis.

Unlike other SQL Server report building tools, such as Report Builder and Report Designer, creating reports in Power View is similar to building pivot tables and pivot charts in Microsoft Excel. Power View users don’t have to switch between view and design modes to see the results of their changes, and the data they work with is always current. With a few clicks, they can create multiple reports that provide unique views of the targeted data.

Get more information on SQL Server business intelligence

Find out what the right tools are for SQL Server business intelligence for an SMB

What’s in your SQL Server business intelligence toolbox?

Power View data models

To use Power View, users must launch the application from a SharePoint site. That means an organization supporting Power View must run both SQL Server and SharePoint Server installations. In addition, at least one data model must be set up to provide the data necessary for the reports. A data model serves as a bridge between the front-end application and the back-end data sources. The data model hides the source data so users can work with that data without having to know anything about its inherent structure, the servers that host it or the security that governs its use. What users see is a simple list of data entities, as in Figure 1.

Figure 1. The data model is automatically available to Power View users.
Figure 1. The data model is automatically available to Power View users.

The figure shows the Power View design window when it first appears. The data model is located in the upper-right pane. Notice that, by default, only the model’s entity names are listed. But users can expand each entity in order to access its fields. Users need take no other steps to access the data. A data model, in essence, provides the fundamental building blocks for all reports based on that model.

Users always start Power View from a data model. The model can be created in one of two places: a SharePoint Server document library or a PowerPivot gallery, a special type of SharePoint document library. You can build data models in PowerPivot for Excel or in SQL Server Data Tools. The data on which the models are based can originate in such sources as SQL Server, DB2, OData, Oracle and Teradata. Once you’ve set up the necessary data models, users can create reports in Power View.

Power View visualizations

Where Power View wins the most points is in its Silverlight interface. To create reports, users connect to the SharePoint site through a Silverlight-enabled browser, locate a data model from a document library or PowerPivot gallery and launch Power View from that model. When the Power View window appears, they switch to design mode (see Figure 1) and select the fields they want to include in their reports. Selecting a field is simply a matter of expanding an entity from the list of data model entities and clicking the check box next to the field name. Users can also drag the field names to the Fields list or to the design surface.

For example, Figure 2 shows a report I created based on a sample data model for Contoso Schools, a Microsoft example school district (You can access this and other sample models on the MSDNblog).

Figure 2. The data model lets you create a table-based report in Power View.
Figure 2. The data model lets you create a table-based report in Power View.

The report is a basic table with data from the following three fields:

  • Name (in the School entity)
  • School Year (in the Time entity)
  • Average Absences per Student (in the Student Absence entity)

After I added these fields to the table, I filtered the data in the School Year field so it included only the 2009-2010 school year. I then resized the table and columns (simple drag-and-drop operations) to better display the information. The only other step I took was to add the title.

That was all I needed to do to create this report. If this had not been a sample data model, and I had the necessary access to the SharePoint site, I could have saved the report and shared it with other business users. Keep in mind, however, that Power View reports are saved as RDLX files and can be viewed only in Power View. The reports are not compatible with reports created in Report Builder or Report Designer, which are saved as RDL files.

About the author
Robert Sheldon is a technical consultant and the author of numerous books, articles and training materials related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. Check out his blog, Slipstream.

Dig Deeper on Microsoft SQL Server Reporting Services (SSRS)