SQL11UPD06-TSCRIPT-07

This wiki is a transcript of a previously recorded video.

Related content assets:


Exploring SQL Server 2012 Power View

Welcome to this presentation, Exploring SQL Server 2012 Power View. My name is Peter Myers. I’m a SQL Server MVP and a Mentor with SolidQ.

In this presentation we’ll introduce Power View, and the second part of the presentation, we’ll actually explore what’s required to optimize the Power View experience. It’s the topic of optimizing a tabular BI Semantic model for this experience. Then we’ll cover on some developer opportunities.

Power View, part of the SQL Server 2012 Reporting Services (SSRS) offering, is an interactive data exploration, visualization and presentation experience. It delivers highly visual design experience with rich metadata-driven interactivity and the product team at Redmond were guided by the principle that it should be presentation-ready at all times. They were also guided by the principle that there should be minimal clicks involved in creating very impressive interactive reports. So it provides intuitive ad hoc reporting for business users such as data analysts, business decision makers and information workers. Note that ordinarily a Power View report needs to be based on a tabular BI Semantic Model that has been optimized specifically for the reporting tool.

Let’s be clear about this, that when we talk about Reporting Services and the authoring of reports there are three supported authoring tools that Microsoft provides. Report Designer, targets the IT professionals, is about producing libraries of managed reports, checking them into source control and deploying direct to the server. Report Builder provides almost similar capabilities but it targets the business user and focuses on a single report at a time. It’s more of an intuitive interface particularly for those users who are familiar with Office and working with the ribbon. These two tools still exist in SQL Server Reporting Services 2012. They’re not being removed, they’re not being replaced. Power View, here, is to complement the authoring experience with a very different experience. It uses a web browser and Silverlight experience to create rich, interactive reports based on a tabular BI Semantic Model. It is there to complement, not to replace, the functionality that is supported by Report Designer and Report Builder.

Here’s an example of what a Power View report could look like – in fact, it is the report created by the lab, should you have the opportunity to do it. What you see is very impressive graphics and layout. If I were to count the clicks that were involved to create this report it would probably total at 30 or 40, or perhaps 50. I could have produced this report within a matter of a minute or less, knowing what I’m doing of course.

For Power View there are system requirements. On the server we need SharePoint Server 2010 Service Pack 1 Enterprise Edition and SQL Server 2012 Reporting Services in SharePoint mode. On the client they simply need a web browser supported by SharePoint. That includes Internet Explorer, FireFox and Safari, and Silverlight 5 is a requirement also.

Data modeling requirements. The Power View report must be based on a deployed tabular BI Semantic Model, either a published PowerPivot workbook in a SharePoint library or a tabular database hosted in an Analysis Services in Tabular mode. Be aware that Power View uses DAX Query to query the model, and that explains why multidimensional BI Semantic Models are not supported in this release. Ordinarily the model needs to be optimized for Power View and we’ll explore that in the next part of this presentation.

Creating Power View reports. The way that users do this, there are three approaches to create the report. Note that the report is defined in an .RDLX file, which is different from a conventional report, which is an .RDL file. The .RDLX for the Power View report can be based either from a BISM Connection File, a PowerPivot workbook that has been published to SharePoint, or a Reporting Services shared data source. Reports can consist of multiple views, so in the same way that a PowerPoint presentation can have multiple slides, a Power View report may have multiple views. Each view can be filtered independently. Reports may be printed, saved to SharePoint libraries or even exported to PowerPoint. What is very cool is that once you’ve exported to PowerPoint, providing you have connectivity and permission to the service, you can then turn on an interactive mode and interact with the Power View report from inside of the PowerPoint presentation. In SharePoint, clicking on a report in the library will open it in preview mode and, should the user have permission, they can switch to edit mode.

For the design experience, it consists of a ribbon, which is very Office-like. The Filter area is where we define filters for our views. The Field List, then, located on the right side, exposes the tabular model in terms of tables and fields, those fields being either columns or measures. Then the Layout Selection is for the currently selected region on the report and it’s the layout and configuration of the report itself. The report may be viewed in preview or even full screen mode, which is like a presentation in PowerPoint. To exit this we simply press Escape. Visualizations can be added to the canvas and configured using that layout selection. The visualizations consist of matrices, charts, cards, tiles, scatter and bubble charts.

There is a demonstration that explores Power View in some good detail. There will be a separate recording that accompanies this presentation that will let you see that demonstration.

The next topic then concerns itself with the optimization of a tabular BI Semantic Model to get the most out of Power View. Ordinarily this BI Semantic Model needs to be optimized for the Power View experience. This is required to exploit the unique capabilities of the reporting tool by supplying hints and directives. Note that optimizing a model for Power View may de-optimize it for OLAP clients, as we’ll consider in this topic.

Be aware that the following model resources are not available in Power View. Hidden tables, columns, and measures, of course – they’re hidden and won’t be available. Hierarchies, these multi-level structures that we can now develop in tabular models, are not surfaced in Power View. Implicit measures, and they are measures defined using the PowerPivot Field List in Excel, are never surfaced in Power View. Key performance indicators are not surfaced – however, the measure that the KPI is based on will be surfaced, if it’s visible. Be aware that we can define perspectives in tabular models, but only the default perspective may be used in Power View.

The model can be optimized by providing friendly names for tables, columns and measures, being aware that the column and measure names will appear as the headers in our reports. Hiding unnecessary tables, columns, and measures ensures that we don’t clutter up the field list or enable users to introduce irrelevant or unnecessary data. Setting appropriate formats for columns and measures will ensure that those formats flow through to the Power View experience. Defining descriptions for tables, columns and measures will then surface as tool tips in the Field List, and will provide explanatory information or further supplementary information relevant to the report author. Adding columns that contain images. So the new binary data column will then provide images in Power View. Be aware that this may not necessarily be efficient, particularly if those images files are large or the volume of rows that contain images is large as well. An alternate approach and perhaps a preferred approach is that you can store as a column in your tabular model the URL to an image, perhaps in a virtual directory on a web server. Then you would configure that column to be a URL image, and then Power View, providing it has permission of course, will extract those images and make them available in your Power View report.

There may not be the need to define measures. Let’s be aware that measures are important for aggregating the numeric values in our model, and there are some things to understand about Power View when it comes to defining measures. They are detailed here in this slide. By default, Power View will express numeric columns that are using a data type of decimal or currency as measures. This is by default. You don’t even need to create an explicit measure in your model. The advantage to this approach is that users can then modify the aggregate function of a Power View expressed measure. However, a distinct disadvantage is that this optimization will actually de-optimize for OLAP clients, that if you don’t create an explicit measure, then a PivotTable, for example, will not expose that a measure is available to you. There is a property known as SummarizeBy. What SummarizeBy does for a column is that it determines how it should be summarized in Power View or a reporting tool. By default the value for SummarizeBy is default. What that means for a column of Decimal or Currency data type is that it will default to Sum. You can change this to any of the available aggregate functions. Note that for a numeric column that is of data type Whole Number, this will not serve as a measure automatically. So default for a column of Whole Number data type actually means don’t summarize. So you might choose, if it’s appropriate, to set the SummarizeBy property for these whole number column as Sum.

What we see in here is that the Field Lists on the left hand side showing us some measures in the Reseller Sales table. The Profit measure uses that calculator icon. That denotes that it’s an explicit measure defined in the model. The Sales measure in contrast uses the sigma symbol, which denotes that it’s an automatic measure expressed by Power View.

For the Sales measure in the layout on the right side, we get to see that the user can modify the aggregate function. Note that this is one of the advantages of an automatic measure. The explicit measure will not support changing aggregate function.

Some additional reporting properties, these can also be configured in Power View or the tabular project. Note that they are available through the Advanced tab, so this must be turned on if using the PowerPivot Window.

For the reporting properties we have the Default Field Set. When you configure this at the table level, what you’re configuring are the columns or measures and the order of them that will be available by simply single clicking the table name in the Field list. For example, when I click the Product table, I would like to see added to the canvas a report that consists of the Category, Subcategory, Product and Photo. By configuring those four columns as default fields in that order we have introduced that convenient functionality. The table behavior then allows us to configure a number of properties. The Row Identifier essentially is, what is the primary ley, what is a column that is unique that uniquely therefore identifies each row in the table. Keep Unique Rows are columns that relate directly to the row identifier. For example, in our product table we have a ProductID column that’s the row identifier. But the Product we would say Keep Unique because there is a one-to-one relationship between each product name and its key. Default Label is the default label that will behave as the user-friendly label for the table. The Product, for example, would be the default label, and of course if we have a photograph of the product we would specify that it is the default image. As described earlier, perhaps a recommended practice for images is that you actually host them externally and you use the URL in the model to connect and reference those images. So for a column that contains the URL to an image, you need to specify by using the checkbox. We see that here – image URL checkbox that the column does contain an image URL.

That concludes the topic of optimizing a Tabular BI Semantic Model for Power View. Developer opportunities then consist of developing, optimizing and deploying tabular models to provide visibility to the application data that you have stored. Create SharePoint libraries of BISM Connection Files to facilitate accessing and creating Power View reports based on tabular models. Develop SharePoint libraries of Power View reports to provide intuitive, interactive and presentation-ready experiences. Do note that Power View reports cannot be embedded into solutions using the ReportViewer control. Power View can only be available through the browser experience and the SharePoint library, exported as images or as PowerPoint workbooks.

In summary, Power View provides intuitive and visually impressive ad hoc reporting targeted at business users. It complements the existing report authoring tools we have of Report Designer and Report Builder. Reports must be based on a deployed tabular BI Sematic Model, be it a published PowerPivot workbook or a tabular database. Ordinarily the model author needs to optimize the data model by configuring the reporting properties. Power View is only available with SharePoint Server SP1 Enterprise Edition.

I’ll leave you here with some resources. Some "Abundantly Crescent", some great demonstrations that were shown at TechEd North America 2011. Be aware there were many enhancements to Power View since the CTP3 release that was used at TechEd North America in May of 2011. There is information available to you in the Reporting Services team blog. There is an overview of Power View in at TechNet that includes many useful links to related topics. We have a great Hands-On Lab that then prepares a tabular BI Semantic Model by optimizing it. That lab should be available wherever you found this presentation and then we have a second lab that explores the optimized BI Semantic Model using Power View. Thank you very much for watching this presentation.


Return to SQL Server 2012 Developer Training Kit BOM (en-US)