Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server 2012 Power View: Self-service BI reporting for SharePoint

The SQL Server Power View tool enables solution providers to deliver self-service business intelligence reporting to a wide range of customers.

When Microsoft released SQL Server 2012, it included Power View for SharePoint Server in its suite of business intelligence (BI) tools. Power View is an interactive reporting application that lets business users explore, visualize and present data entirely through their browsers. For solution providers, Power View represents an important opportunity for adding value to their SharePoint offerings. Providers can now deliver a self-service BI solution that brings ad hoc reporting to a wide range of customers.

SQL Server 2012 Power View is a Silverlight application that offers a highly customizable, browser-based interface where business users can create reports that meet their specific needs. They can add tables, matrices and a variety of charts that they can arrange in a free-form fashion across the report surface. Power View also lets users filter, slice, sort and highlight data. They can then save their reports to SharePoint so that others can view and edit them or export their reports to PowerPoint.

A Power View report is always presentation-ready. That means what users see when working on the report is what the report actually looks like. They do not have to switch between design mode and preview mode, as is the case with other Microsoft reporting tools. Power View reports are fully interactive, with every change instantly visible.

Why go with Power View?

With the number of BI reporting tools that Microsoft now has available to the SharePoint environment, the addition of Power View might seem confusing at first. At the top of the BI stack is SQL Server Reporting Services (SSRS), which includes the Report Designer development environment for creating reports. However, Report Designer is a sophisticated interface geared for developers and IT pros. When users view SSRS reports in SharePoint, they're viewing the result of work done behind the scenes by professionals, and there's little they can change once a report is deployed.

Microsoft also offers Report Builder, a standalone counterpart to Report Designer, aimed at nondevelopers. Report Builder has an interface that is more Office-like and user-friendly than Report Designer. But like Report Designer, Report Builder reports can be deployed to an SSRS server and made available to SharePoint. However, even this tool requires a level of tech and reporting savvy that limits the number of qualified users, and once the reports are deployed, there is little the SharePoint end user can do, other than view the reports. Of course, those using Report Builder to create their reports can modify and redeploy them as necessary, just as developers using Report Designer can redeploy their reports.

Another Microsoft reporting option is Excel, with its pivot tables and pivot charts. For the power user, Excel can serve as a helpful tool for building certain types of reports, if users know what they're doing. However, workbooks published to SharePoint via the Excel Services are fairly fixed in terms of their layout and interactivity, making collaboration and personalization more difficult. Even with the advent of PowerPivot, a data-crunching Excel extension, the limitations inherent in Excel remain.

SharePoint Server also offers another reporting option: PerformancePoint Services. PerformancePoint lets users create BI dashboards that include custom reports and key performance indicators for monitoring and analyzing an organization's data. Unfortunately, the tool usually requires advanced users or IT professionals for design and deployment. Even then, there's limited flexibility when defining formats and layouts, and users do not have an easy way to save their personal views.

SQL Server 2012 Power View addresses the limitations of the other reporting tools and adds features of its own. Customers can work directly through their browsers to view, edit and share reports. They do not need a developer or IT pro to develop the reports for them. They don't need to install any additional components outside of Silverlight. They don't have to master a complex application in order to build a single report. They are not limited to the Excel environment or any other external tools. With Power View, your customers now have an intuitive, interactive environment that provides them with direct access to the data they need without having to jump through a lot of internal hoops to get there.

The Power View experience

Power View is available in SSRS when installed in SharePoint mode. In addition, it requires the SharePoint 2010 Enterprise edition, Service Pack 1 (SP1) or later. SQL Server 2012 SP1 also makes it possible to run Power View on SharePoint 2013. Plus, Power View is now available to Excel 2013.

SQL Server Power View reports are based on specific data models -- collections of tables and their relationships -- that provide a user-friendly abstraction layer between the reports and their data sources. A data model acts as a bridge that hides the complexities of the underlying data source. Power View supports two types of data models: those created with PowerPivot for Excel and those created with SQL Server Analysis Services (SSAS) 2012 in tabular mode.

Because data models can be created in SSAS, everything that solution providers need to offer Power View to their clients is in SQL Server 2012 and SharePoint 2010 Enterprise edition or later. If these products are already part of the solution, Power View is a value-added tool ready to be implemented. And if SQL Server or SharePoint upgrades are under consideration, Power View should be part of the discussion, for it addresses what has until now been a missing link in the Microsoft BI stack. In fact, one could say that many of Microsoft's BI reporting efforts have been aiming at exactly this type of solution. Power View puts customers back in control of their own data, in a way that's intuitive and accessible and that many are calling just plain "fun."

Robert Sheldon is a technical consultant and freelance technology writer.

Dig Deeper on Database software management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.