Self Service Reporting with Power View and SQL Server 2012 Analysis Services

Power View is generating quite a bit of excitement, promising to finally hand the power of self-service BI to Microsoft customers everywhere.

The first caveat is that it doesn’t yet work with multi-dimensional data. That means if you want to use SSAS as your data source then you will need to install an SSAS instance in Tabular mode. Unfortunately you can’t leverage any multi-dimensional instances you have installed already, it has to be a brand new instance.

This seems like a big limitation, although you should consider whether the new Tabular model is anyhow more suited to your scenario. Very general (and not conclusive) guidelines are:

  • You want a model that is easier to build and maintain than Multi-Dimensional cubes
  • You want a model that is easier for your end users to understand and query
  • You have many different data sources, and you don’t want to build a Data Warehouse to consolidate the data before modelling
  • You do not require ‘write back’ functionality to the model
  • You do not require advanced Multi-Dimensional functionality
  • Your data sets are large, but not extremely large

To get started, create the Adventure Works Tabular Model:

  1. You can be a diligent student and set it up following these instructions: http://msdn.microsoft.com/en-us/library/hh231691.aspx
  2. Or you can just download it: http://msftdbprodsamples.codeplex.com/releases/view/55330

 

Using Microsoft Excel, you can quickly attach to this Tabular model and check that it is working:

 

Then using PowerPivot for Excel, you can create a Pivot Table using the following MDX query: (You can use the visual designer to achieve this)

SELECT NON EMPTY { [Measures].[Reseller Total Sales], [Measures].[Reseller Total Gross Profit] } ON COLUMNS, NON EMPTY { ([Employee].[Last Name].[Last Name].ALLMEMBERS * [Geography].[Geography].[City].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Model] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

 

 

I can now upload this into my SharePoint PowerPivot Gallery. Now I can can click on the icon ‘Create Power View Report’.

 

I can now design a corresponding Report in Power View:

 

Other users can now produce whatever kind of reports they like. In the example below we have created a ‘sales and profit by country’ bar chart:

 

The end users can now save this as a new report to the Gallery.

 

Conclusion:

I’m concerned that the inability of Power View to use Multi-Dimensional cubes will result in a lot of IT departments not even taking a look as the power it provides. If you have a tabular model then it only takes a few minutes to try out, but almost no-one is going to have a tabular model today! Hopefully using Power View to query existing relational data will at least give a good taste of this functionality, and therefore convince people to give the Tabular model a go.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s