Power View: Business Intelligence for Power Users

Power View is new with SQL Server 2012 and is part of SQL Server 2012 Reporting Services (SSRS). However interestingly it only works when SSRS is in SharePoint Integrated mode and so you need to have SharePoint installed as well to use it.

Power View is designed to easy to use, allowing users to create their own reports and to slice and dice data without help from a professional developer. However, being easy for the user does not necessarily mean that it is equally straightforward to install. My first experience with Power View was to install SQL Server 2012 and SharePoint 2010 and then to look around looking for Power View, without finding it. So I took it upon myself to work out how to install and configure SQL Server and SharePoint for use with Power View.

While I am sure that there are a many of ways to get Power View ready to use, these are the steps that I followed:

1. Install SQL Server 2012, including all features (except for Analysis Services)

2. Install SharePoint 2010 and create a web application and site collection based on the Business Intelligence Site Template

3. Return to SQL Server Setup and add the features:

·         Reporting Services - SharePoint

·         Reporting Services Add-in for SharePoint Products

4. Return to SQL Server Setup again and install SQL Server PowerPivot. Power View uses either PowerPivot or Analysis Services in Tabular Mode as data sources:

5. Run the PowerPivot Configuration Tool, and complete the prerequisites it requests:

6. Download and install Silverlight. Power View is based on Silverlight

7. Start SharePoint 2010 Management Shell and run two commands to install the SharePoint Application Services for Reporting Services:

·         Install-SPRSService

·         Install-SPRSServiceProxy

8. Start SharePoint 2010 Central Administration:

·         Select the Services On Server menu  and verify that the Power Pivot and SQL Server Reporting Services Service are running

9. Select the Manage Service Applications menu

·         Select the New > SQL Server Reporting Services Service Application to create a new Reporting Services application

10. Navigate to the SharePoint Business Intelligence site created earlier and use the Site Settings > Site Collection Features menu to verify that the Power View and Reporting Server Integration features are installed:

11. Create a new site in the Business Intelligence site using menu Site Actions > More Options… and site template PowerPivot Site:

12. Download and install Microsoft® SQL Server® 2012 PowerPivot for Microsoft® Excel® 2010

13. Start Excel, select the PowerPivot menu and then click the PowerPivot Window button and create a data source based on the AdventureWorks.Production.Product table in SQL Server. Then save this as a file called PowerPivotExample.xlsx:

14. Navigate to the newly created PowerPivot site, select the PowerPivot Gallery library and upload file PowerPivotExample.xlsx into it. This is the source for the Power View report:

 15. Click the Power View icon in the top right of the new data source to invoke the Power View designer. Then carry out the following steps to  add a matrix of average product price by product colour and class:

·         Drag ListPrice onto the page, select it with the mouse and then select the Matrix option from the Design toolbar

·         Drag Class and Color onto the Row and Column Groups

16. Then add two slicers to allow easy filtering:

·         Drag Class onto the report page, select it with the mouse and select the Slicer option in the Design toolbar

·         Repeat the above using Color

17. Then click Full Screen in the Home toolbar to view the report. Selecting one or more colours or classes in the slicers redisplays the matrix:

18. Finally click File and Save to save the report back into the SharePoint Library for future use

In summary, there are a quite few steps for the administrator to get everything in place before it’s possible to start using Power View. But once it’s all there, it’s very easy for users to slice and dice their data and produce reports of their own.