«

»

Jun 11

Installing Power View for Multidimensional Models

 

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.

Warning!

This update only affects reporting services in SharePoint mode, it does not modify the functionality of Power View in Excel 2013. Currently Excel 2013 still only supports Power View for tabular models.

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

PowerViewMultidimensionalInstall01

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

PowerViewMultidimensionalInstall02

Step 5: Accept the licence terms and click Next.

PowerViewMultidimensionalInstall03

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.

PowerViewMultidimensionalInstall04

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.

PowerViewMultidimensionalInstall05

Step 8: Click Update.

PowerViewMultidimensionalInstall06

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

PowerViewMultidimensionalInstall07

Step 9: Click Close.

PowerViewMultidimensionalInstall08

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:

PowerViewMultidimensionalInstall09

After:

PowerViewMultidimensionalInstall10

 

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.

PowerViewMultidimensionalInstall11

 

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.

PowerViewMultidimensionalInstall12

 

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.

PowerViewMultidimensionalInstall13

 

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!

PowerViewMultidimensionalInstall14

 

Warning!

Hey! Take a look at me! I’m an important note!

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.

PowerViewMultidimensionalInstall15

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”.

6 comments

2 pings

Skip to comment form

  1. sam

    i am getting the following message: “A connection cannot be made. Ensure that the server is running” i can connect .

    Enviroment:
    I’m running sharepoint 2013 on the same box with sql 2012 sp 1 (11.0.3368) and that’s my connection string:
    Data source=;initial catalog=HR-EE;cube=’hr’

    any ideas.
    thanks

    1. Bradley Schacht

      Did you make sure to add the server name into the connection string after data source? I know it’s missing in the comment just wanted to be sure that you have it in there on the actual connection.

  2. Nutan Patel

    hi,, i have downloaded and installed 464142_intl_x64_zip.exe. after extracting file i didn t get
    file named “SQLServer2012-KB2833645-x64-PowerViewForMultidimensional.exe”. the was different. i have successfully installed that update but i am getting this error “Discover CSDL is not allowed on a server working in OLAP mode.”. Can you please me out?

    1. Bradley Schacht

      Did you make sure to apply the update to your SSAS instance as well? The update must be applied to both Analysis Services and Reporting Services.

      1. Dorsaf Mouhli

        I had the same error but the problem is that I can’t apply the file named “SQLServer2012-KB2833645-x64-PowerViewForMultidimensional.exe” but only on the powerpivot instance

  3. Wveimar López

    Bradley, what a great article. Congrats. Thanks a lot for your effort writing this post. The information is so useful if anybody wants to know what can and what can’t do with PowerView. I am a newbie in the subject, and now I understand I can use PowerView without necessity of installing SharePoint if I want to access my OLAP data sources: Multidimensional or Tabular.

    Obviously, you lost a lot of functionality if you don’t use SharePoint (Libraries, collaboration, gallery), but for those who can’t afford to buy the SP license, it is possible to consume data available in an Analysis Services tabular instance directly from PowerView or data available in Analysis Services multidimensional instance through PowerPivot. In both cases, you can also create a tabular model with data from diverse sources and then use it from PowerView.

  1. Setting Up a SharePoint 2013 BI Farm :Jen Underwood BI, Predictive Analytics & Excel Blog

    […] 17. Install SQL Server 2012 CU4+ for Power View on Multi-dimensional Models DAXMD TIP! Bradley Schact has a nice blog on this step. […]

  2. Setting Up a SharePoint 2013 BI Farm : Impact Analytix: Business Intelligence, Predictive Analytics & Excel

    […] 17. Install SQL Server 2012 CU4+ for Power View on Multi-dimensional Models DAXMD TIP! Bradley Schact has a nice blog on this step. […]

Leave a Reply

%d bloggers like this: