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.[/warning]
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=<ServerNameInstanceName>;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![/warning]
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”.

About the author

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

17 comments

Leave a Reply to Dorsaf Mouhli Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • 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

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

  • 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

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

  • 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?

      • 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

  • 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?

      • 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

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

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

  • My understanding is that this works the way it does because it works *THROUGH* a reporting services reporting object in SharePoint (yikes!). This is supposed to ‘tabularize’ the results so PowerView can consume the output. What a slow, clumsy work around from MSFT.

  • My understanding is that this works the way it does because it works *THROUGH* a reporting services reporting object in SharePoint (yikes!). This is supposed to ‘tabularize’ the results so PowerView can consume the output. What a slow, clumsy work around from MSFT.

Bradley Schacht

Bradley Schacht is a Principal Program Manager on the Microsoft Fabric product team based in Jacksonville, FL. Bradley is a former consultant, trainer, and has authored 5 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. As a member of the Microsoft Fabric product team, Bradley works directly with customers to solve some of their most complex data problems and helps shape the future of Microsoft Fabric. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me