skip to Main Content

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

Power View Business Inteligence For Power Users 1

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:

 Power View Business Inteligence For Power Users 2

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

Power View Business Inteligence For Power Users 3
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

 Power View Business Inteligence For Power Users 4

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:

Power View Business Inteligence For Power Users 5

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

Power View Business Inteligence For Power Users 6

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:

Power View Business Inteligence For Power Users 7

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:

 Power View Business Inteligence For Power Users 8

 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

 Power View Business Inteligence For Power Users 9

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

Power View Business Inteligence For Power Users 10

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:

Power View Business Inteligence For Power Users 11

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.

By Geoff Ballard, Chief Technical Officer

Have a comment? Let us know below, or send an email to [email protected]

About the Author

As Co-Founder of Ballard Chalmers, Geoff is the company’s CTO, directing technical strategy, overseeing technical consultants, managing larger development projects and ensuring technical delivery quality standards.

Education, Membership & Awards

Geoff graduated from the University of London with a BSc (Hons) in Mathematics and computing and a Masters degree in Database Management Systems. Additionally, he is a certified SQL Server Development, Microsoft Certified IT Professional (MCITP) and Microsoft Certified Technical Specialist (MCTS).

His professional memberships include Charted Engineer (Software), Member of the British Computer Society (MBCS), Member of European Federation of Engineers (FEANI) and Fellow of the Institute of Analysts and Programmers (FIAP).


Back To Top
Contact us for a chat