Today, I’m going to present you a way to use Power View with a direct connection to Multidimensional Cube. In this case the Adventure Works EE Multi-Cube. I’m sorry for the German screenshot, but I don’t have another version currently available. I’ll translate the important things and you can have a look at the language independent icons.
With Excel 2010 and Excel 2013 Microsoft made the Plugin PowerView available to design dashboards in an easy way. When the source data is within the Excel file or you do have a connection to an Analysis Services Tabular Cube you can directly connect to your model and insert a Power View Page.
The Screenshot below shows the last part of the Wizard to import data from SSAS Multidimensional. The selected radio button is „PowerView-Report“.
When I now click on „Ok“, I get the following message:
Power View: Power View in Excel cannot open a connection to this data source. Possibly it’s a multidimensional data source, which Power View does not yet support. Please try a connection to another data source.
Using another data source is not very helpful if you only have this cube available.
For Excel 2016 Microsoft presented in the „what’s new“ part: https://products.office.com/en-us/whats-new-office
„PowerView over an OLAP connection
Microsoft PowerView is now added as a reporting option for an Online Analytical Processing (OLAP) cube connection. Just connect your Excel spreadsheet to an OLAP cube and select to view this data in your workbook as a Power View Report. In PowerView you can now build reports with KPI’s, hierarchies, calculations and table data coming from the OLAP cube.
Okay, this is a great advantage. So let’s try:
Excel => Data => From Other Sources => From Analysis Services
Enter in the wizard the server name and maybe the port.
Choose the database and the cube you want to use:
Edit the properties of the Connection file:
Okay, here we are: The same wizard step: I included a bit more of the Excel screen to show how Excel 2016 looks like:
AND: Here we go. It’s working. We can use PowerView with a Multidimensional Cube connection: