Friday, March 2, 2012

My first experience with Power View of SQL Server 2012 (febr 2012)

In the past I blogged about SQL Server Project "Crescent" Denali. This project will come general available in SQL 2012 named Power View. Last week I installed SQL 2012 RC0 to experience the Power View capabilities. First of all I will give some characteristics of Power View which will give you an idea what you can expect from Power View.
  • Power View is a browser-based Silverlight application launched from SharePoint Server 2010 that enables users to present and share insights with others in their organization through interactive presentations.
  • Web-based report designer. So, this will be the fifth report designer after BIDS, Report Builder 1.0 (not sure if RB 1.0 will survive SQL 11), Report Builder 3.0, Visual Studio Report Designer.
  • Reporting experience will become more interactive and similar to Excel PivotTable. The end user can quickly create an interactive report by dragging metadata, and then with a few mouse clicks change the report layout without switching to design mode. This is completely different in comparison to a Reporting Services report which is always static.
  • See next blog post for a perfect demo of Power View.
 To install Power View you need to have 2 installation sets:
  • SQL 2012
  • SharePoint 2012 SP1
Use next blog post to install Power View: Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3). It will explain the sequence to install which components. After completing the installation, I installed the Power View and PowerPivot HelloWorldPicnic Samples for SQL Server 2012 Release Candidate 0 (RC0). The HelloWorldPicnic samples are very useful to explore the new capabilities of Power View. After this I used the Power Pivot sheet on a Globe database I created in the past. This sheet enables me to play with data I know.

Having done this, it is now time to talk about my experiences with Power View. I will start with a list of GREAT user experience examples:
  • You can really 'play' with the data without changing and redeploying your report.
  • Highlight of selected values, see next example

    Left chart is the revenue and margin per year of the company. Right chart is the revenu per employee over the years. Select one employee in the right chart and you will get:

    Now you will see what the amount of Revenue and Margin this employee has made in comparison with the total amount of revenue made by the company.
  • Pop out. Every visualization has an pop out option. This will show the visualization full screen so you can better look to the details. Press on the Pop In button to go back to the report.
  • Tiles. Another way of selecting data. Selected data in the tile applies only to the visualization the tile belongs.
  • Play Axis on a bubble chart. Press on the Play button so the bubbles move, grow, and shrink as their values change over the months. Note that the months display as watermarks in the upper-right corner, and the bubbles move to the right as the months pass and the year-to-date quantity increases.
    Click on the city Assen and you will see how the city 'Assen' moved over the years.
  • Interact in Power Point. You can export the Power View report to Power Point 2010. Each Power View view has been added as a separate slide. In PowerPoint design mode, each view is static. After you open the Power Point presentation in presenter view, you will get a button in the right bottom on your presentation called: Click to interact
    As long you have connection to the sharepoint server on which report is deployed, you can interact with the view in the same way that you can in reading and full screen modes. So you can play with the data in the Power Point presentation itself.
This is the first release of Power View with a lot of interesting interactive reporting features. Next to this nice features I will give a list of possible future improvements:
  • Installation is complex. It is not 'out of the box' like a one click installation.
  • Additional knowledge about Sharepoint 2010 SP1 is needed. A big part of the SQL community do not use Sharepoint 2010. The installation of SQL Server itself is already complex enough for them.
  • Silverlight is needed. This means Power View will not run on the IPad. Power View is, for instance, made for the people in the board room. However a lot of them are using IPad's as of today.
  • Visualizations can not be customized like you can in SSRS. For instance, Fonts, Colors, Borders  etc. It is not possible to apply your style guide.
  • Default colors are sometimes in conflict with dashboard design rules. One example: The use of the color Red. The first color in charts is blue, the second color is red. According to the dashboard design rules, red is used to get attention for something. In next example, it is not intended to give extra attention to the Amount of Margin. Using red it looks like something is going wrong with the Amount of Margin.
  • Automatic alignment of chart horizontal or vertical is not supported.
  • Report is based on a metadata layer. You do not have datasets. To use Power View on a database you first need to develop a metadata layer.
  • Existing SQL 2008 R2 reports (RDL) can't be converted to the Power View format RDLX. You need to build them from scratch. Of course you can use your SQL 2008 R2 RDL's in the traditional reporting service SSRS of SQL 2012.
  • Power View is not available in SQL Azure Reporting. When Power View is available in SQL Azure Reporting we can get rid of the Share Point knowledge need.
Enjoy your Power View journey.


ganesh said...

Interesting post. I have couple of questions.

1) If i develop a report using power view can i use the deployed report on my website just like a 2008 RDL file as "Microsoft.Reporting.WebForms.ProcessingMode.Remote".

André van de Graaf said...


I have not tested this but I think this is not possible. A Power View report file is a completely other format as a RDL file.

GR. André

Anonymous said...

This was good article for basic understanding but sir like all other links there is one thing missing. So sir can u please explain how to deploy , publish and host these reports in sharepoint or the corresponding platform. Meaning how user will receive these reports. i might be asking wrong question.

Anonymous said...

This was good article for basic understanding but sir like all other links there is one thing missing. So sir can u please explain how to deploy , publish and host these reports in sharepoint or the corresponding platform. Meaning how user will receive these reports. i might be asking wrong question.

Anonymous said...

also can i schedule my reports to user. for e.g. The CEO gets reports mailed to him at every 5:00 am in morning

Anonymous said...

Anyways thanks for the article

Chris Brooksbank said...

Its difficult to believe that such a nice tool does not allow export to PDF or Excel or anything except PowerPoint. Thats why we are looking at Tableau ( that and the SilverLight plugin requirement )