Power View was a new reporting option added in the SQL Server 2012 release as part of the SharePoint integrated mode of Reporting Services. From the beginning it was limited to only reporting off tabular models (and therefore PowerPivot models as well). Power View later made its way into Excel 2013, still maintaining support only for Tabular. That all changed when Microsoft announced at the 2012 PASS Summit that Power View reporting was coming to multidimensional models in a later update. A CTP dropped in January of 2013 and the “later update” was finally released on May 31, 2013.

Here are the pre-requisites to use Power View with multidimensional models before we hop into the install.

• Microsoft SQL Server 2012 Analysis Services (Multidimensional) with SP1 and Cumulative Update 4
• Microsoft SQL Server 2012 Reporting Services in SharePoint Integrated Mode with SP1 and Cumulative Update 4
• One of the following two versions of SharePoint
  • SharePoint 2010 SP1 Enterprise Edition
  • SharePoint 2013 Enterprise Edition

Now for the installation (which is pretty standard) and then how to access Power View for Multidimensional.

Step 1: Download the Cumulative Update (CU) from here.

Step 2: Download and run the file called 464142_intl_x64_zip.exe

Step 3: Run the resulting file called SQLServer2012-KB2833645-x64-PowerViewForMultidimensional.exe

Step 4: Click Next after all the checks have run on the Install a SQL Server 2012 Update screen.

Step 5: Accept the licence terms and click Next.

Step 6: Check the box next to all instances you would like to apply the CU to. I happen to have several instances on this virtual machine. Be sure to apply this to all servers with the Reporting Services – SharePoint feature as well as any instance running multidimensional analysis services you would like to create Power View reports against.

Step 7: Click Next on the Check Files In Use screen. If there are files in use a restart may be required. In my case I did not have to restart the server even though there were files in use.

Step 8: Click Update.

Notice that each instance I checked off in step 6 is getting the update applied to it one at a time.

Step 9: Click Close.

Check the version number to ensure the instance has been updated. The new version number will be 11.0.3368 My update was going from SP1 (11.0.3000 in the the first image) to SP1 with CU4 (11.0.3368 in the second image).

Before:

After:

To ensure the new features are functioning properly head over to SharePoint and open up a library that has Report Data Source content types enabled. I have a library with the reporting services content types where I will create a new connection.

Fill out the new data source form. Select Microsoft BI Semantic Model for Power View from the Data Source Type drop down list. This was available previously but would not allow a Power View report to be created unless the connection string pointed to a Tabular model.

In the Connection String box enter the connection string in the following format:

Data Source=<ServerName\InstanceName>;Initial Catalog=<SSAS Database Name>;Cube=<SSAS Multidimensional Cube Name>

If using the default deployment of the Adventure Works sample multidimensional cube that would look like this:

Data Source=localhost;Initial Catalog=AdventureWorksDW2012Multidimensional-EE;Cube=’Adventure Works’

The screen should look something like the image below. Click OK.

After the new data source is created click the ellipsis next to the name and locate the option labeled Create Power View Report. Again, this option was present on the reporting services data sources previously but would not function properly unless the data source was to a Tabular model.

Power View will launch and you will get the list of measure groups and dimensions inside the multidimensional cube. Just start clicking on items in the field list to create a report!

First of all, no, that is not the default text on the message that I just forgot to change.

Second, you will notice that the connection string included the cube name. This is because analysis services databases can contain multiple cubes and this one actually contains several cubes and perspectives. If you do not specify the cube name the following error will pop up when attempting to create a Power View report. This means that if a cube name changes you need to remember to update this data source.

As mentioned previously this functionality is currently limited to SharePoint. Even though Excel 2013 has the ability to create Power View reports at the time of writing it does not support Power View against multidimensional models, only tabular models.

One final note, in Excel you can select a measure group and filter the field list to only the related measures and dimensions. At this time that is not an option in Power View. You can however enter the name of a perspective in place of the cube name. The connection string should still say “cube” but replace “Adventure Works” with a perspective name such as “Channel Sales”.