Getting the Most Out of Microsoft Power View

Microsoft Power View was introduced as part of SQL Server 2012. I'll provide you with six tips for getting the most of out of Power View. I'll also highlight some of the recent enhancements made to Power View in SQL Server 2012 SP1. (If you're unfamiliar with Power View, see "Introducing Microsoft Power View" for more information.)

Tip 1: How to Easily Connect to Power View

Power View reports use a business-friendly data source, which is either a PowerPivot workbook or a SQL Server Analysis Services (SSAS) database in tabular mode. An easy way to start using Power View is to click a PowerPivot workbook's Power View icon within a PowerPivot Gallery, as Figure 1 shows.

Figure 1: Opening Power View in a PowerPivot Workbook

Power View can also be launched by clicking a Report Data Source (RDS) file or a Business Intelligence Semantic Model (BISM) file. Typically, these files are published to a SharePoint data connection library.

Setting up a BISM connection is pretty straightforward. An RDS file can be used in place of a BISM file in situations where you want to customize the connection string or credentials used to connect to the tabular source. If you decide to use an RDS file, make sure that you set the Data Source Type property to Microsoft BI Semantic Model for Power View. Otherwise, Power View will launch but will not be able to connect to the data source. Note that this property can't be set in the Report Designer environment. It must be set in the SharePoint data source editor, as shown in Figure 2.

Figure 2: Setting the Data Source Type Property 

Tip 2: How to Easily Change Data Sources

You might occasionally need to change the data source used by a Power View report. If the report is deployed in a PowerPivot Gallery, you first need to switch the Library View from Gallery to All Documents. Next, click the drop-down arrow next to the report's name and select the Manage Data Sources menu item, as Figure 3 shows. You can then edit the data source accordingly.

Figure 3: Changing the Data Source for a Power View Report 

Tip 3: How to Customize the Display of Power View Reports

Power View reports are saved as .rdlx files in a standard SharePoint document library or PowerPivot Gallery. The most common way to view a report is to simply click the report name hyperlink in the Document library. (In the case of using the Gallery view in a PowerPivot Gallery, the equivalent action is to click the report thumbnail image.) The hyperlink opens the Power View report by passing arguments to a built-in SQL Server Reporting Services (SSRS) web page named AdHocReportDesigner.aspx.

In some cases, you might want to display the rendered report on a specific SharePoint web page or a PerformancePoint dashboard page. However, unlike standard SSRS (.rdl) reports, there isn't a specific Web Part available to display Power View (.rdlx) reports. This means you need to use either the generic Page Viewer or the Silverlight Web Part to wrap around the built-in SSRS web page.

In addition, it currently isn't possible to pass in parameters to dynamically change report filter values or slicer selections. However, there are a few parameters that you can use to customize the display of a report, including:

Tip 4: How to Easily Monitor Power View Usage

Every time a Power View report is opened, a record is logged to the SSRS ExecutionLog table, which is part of the Report Server database. You can either query this ExecutionLog table directly or periodically extract records into a reporting database for historical analysis. I explain how to do both in my article "Monitoring SQL Server and SharePoint BI Components." If you're planning to monitor Power View usage by means of historical extracts, make sure you download the article's sample code. The older CodePlex community sample (which used to be part of SQL Server Books Online -- BOL) doesn't work with SQL Server 2012. The article's sample code creates usage reports that provide a wealth of information, including usage by users across time and performance information related to report processing, rendering, and execution.

Tip 5: How to Get Power View on the Desktop

Power View reports are created and viewed within SharePoint. However, a Power View report can be exported to Microsoft PowerPoint so that the report can be viewed on the desktop. Each view within the Power View report is converted to a slide within the PowerPoint file. The view is an embedded object that looks and behaves like a static picture. In presentation mode, the view becomes interactive if the user clicks the Interact button, which is shown in the lower right corner of Figure 4. Note that in order for the interactivity to work, connectivity to SharePoint and the original report is required.

Figure 4: Using the Interact Button 

There are some additional options for customizing the interactivity options and display of Power View within PowerPoint, but be warned -- implementing this tip isn't supported by Microsoft. So, try it at your own risk.

While in design mode, if you right-click a slide view and then select the Properties menu item, you'll see several properties for the view object, as shown in Figure 5.

Figure 5: Reviewing the Properties for the View Object

The InitParams value points to the original Power View report in SharePoint. If a report gets renamed or deleted, you can adjust the ItemPath as needed. In this case, the ItemPath is:

ItemPath=http://SomeServer/MobileDemo/PowerPivot
Gallery/World_Economic_Analysis.rdlx,ReportServerUri=
http://SomeServer/_vti_bin/ReportServer,
ViewMode=CachedPreview,
ReportSection=ReportSection2,
AllowSectionNavigation=False,Fit=True,
PreviewBar=False,BackgroundColor=White,Border=True,
AllowEditViewMode=False,AllowFullScreenViewMode=False,
Trace_HostApplication=Powerpoint

Notice that the ItemPath includes the parameters previously discussed (e.g., ViewMode), which I've put in bold. If you adjust these parameters, it's possible to do things like show the Preview Bar, set a custom background color, and even have a report open in edit mode.

Looking ahead, the best (and supported) method of getting Power View on the desktop is to use Office 2013. I'll discuss that option later. 

Tip 6: How to Make the Most of Screen Real Estate

A Power View report has a 4:3 display ratio. Modern computer monitors have a 16:9 display ratio, similar to a high definition television. This means a Power View report will leave unused space along the sides of the screen. One way you can reclaim a bit of this space is to use filters rather than slicers. A slicer is placed in the report and takes up needed space. Filters can be displayed alongside the report, saving more space for the report. Filters also have the added benefits of:

New SP1 Capability: Drillable Charts

SQL Server 2012 SP1 introduces several new features in Power View. I'm most excited about the drillable charts. For example, the Power View report in Figure 6 contains the chart Outlays – Percent Of GDP by L1_Superfunction. Notice that several columns have been placed on the Axis section of the chart. Although you can still click a bar to make it act like a filter, you can now double-click a bar to drill down from one axis column to the next. Figure 6 shows the progression of what this chart looks like as I double-click from Superfunction to Function, and then from Function to Sub Function. You can drill up at any time by hovering over the chart and clicking the Up Arrow icon in the top right corner of the chart.

Figure 6: Drilling from Superfunction to Function to Sub Function 

Drillable charts don't require explicit hierarchies to be developed ahead of time. You can select and place columns in any order on the chart axis. Drillable charts work across column, bar, scatter, and pie charts.

New SP1 Capability: Map Control

With SQL Server 2012 SP1, Power View now includes a Map control that leverages Microsoft Bing Maps. This control requires Internet connectivity on the client (not the server) to function. If you have geocoded data (i.e., latitude and longitude coordinates), Bing Maps can use this information for precise plotting. Columns from a data model (e.g., country, province, zip code), however, do not have to be geocoded in order for the map integration to work.

When a column is placed on the Map control, Power View sends the column values through a secured web service for dynamic geocoding. For example, in Figure 7, all of my MapReference columns, with the exception of the Middle East, were accurately geocoded.

Figure 7: Using the Map Control

Most of the time, Bing Maps does a good job of matching common names. Note that PowerPivot for Excel 2013 and SSAS 2012 SP1 tabular databases also let you specify a column's Data Category value (e.g., address, city, county) to improve matching speed and accuracy.

The maps created with the Map control are drillable. For example, in Figure 7, I placed both MapReference and Entity in the LOCATIONS section. (Note that a location is similar to a chart axis.) If I double-click one of the MapReference pie charts, the Map control will automatically redraw the entities that roll up into the selected MapReference.

Other New SP1 Capabilities

There are a few more important new Power View capabilities introduced in SQL Server 2012 SP1:

For a complete list of the new capabilities, see "What's new in Power View in Excel 2013 and in SharePoint Server."

Power View Built Into Excel 2013

Power View is built into Excel 2013, which means users can create Power View reports without having to start in SharePoint. (PowerPivot is also a built-in component in Excel 2013.) A Power View report can be sourced from an Excel data range or table, an embedded PowerPivot model, or a tabular instance of SSAS.

Note that Power View is still a Silverlight-based technology, so users will be prompted to download Silverlight if necessary. Also note that although PowerPivot in Excel 2013 exposes an API for extensibility purposes, Power View does not.

One caveat to watch out for with respect to Excel 2013 and Power View is the upgrade path. You need to make sure that you upgrade the server-side components ahead of time. See the sidebar "Plan Your Upgrade Strategy Carefully" for more information.

More Power View Enhancements Ahead

Power View was released in March 2012, with a significant set of enhancements delivered soon thereafter in the November 2012 release of SQL Server 2012 SP1. Now, Power View is built into Excel 2013. And Microsoft is also planning to introduce a version of Power View that's compatible with devices that can't use Silverlight (e.g., Apple iPad). If you haven't done so already, I encourage you to get familiar with Power View. It's a tool both IT pros and business users can leverage for better data analysis and visualization.

Plan Your Upgrade Strategy Carefully

Microsoft Power View and PowerPivot are technologies that now exist both on the desktop and on the server across Microsoft Office, SharePoint, and SQL Server. As such, you need to carefully consider when, and in what order, you will upgrade the desktop and server components. For example, you can publish an Excel 2010 workbook with a PowerPivot model to SharePoint 2013 without issue. However, if you publish an Excel 2013 workbook to SharePoint 2010, the PowerPivot and Power View functionality won't work. This is similar to trying to restore a SQL Server backup -- it's usually possible to restore to a newer version of SQL Server without any problems, but it's impossible to restore to an older version.

A simple rule of thumb is to upgrade in this order:

By following this approach, end users will have a predictable experience as they publish and extend their workbooks with Power View.