How to use a multidimensional cube with Power View

The requirements for Power View state that it can only use tabular models as data sources.  So if you want to use Power View against a multidimensional cube, you are stuck.  But there is a work-around:

Create a PowerPivot for SharePoint workbook that is using a SSAS multidimensional cube as a data source, and publish this to a PowerPivot Library.  Then create a Power View report that uses this workbook.  Save the Power View report.  Then schedule an automatic refresh of the data in the PowerPivot for SharePoint workbook.  After an automatic data refresh of the PowerPivot for SharePoint workbook is run, open the Power View report, and you will see that it uses the refreshed data in the PowerPivot for SharePoint workbook.

This is possible because a Power View report generates a new query every time you open it.  Only the bitmap you see in the PowerPivot Gallery is not automatically updated (it is updated when you save the Power View report in the PowerPivot Gallery).

So in essence, this is a “work-around”, with the caveat that the Power View report would not be real-time but instead be dependent on how often you refreshed the data in the PowerPivot for SharePoint workbook.

Note this is just a work-around until Microsoft releases an upgrade that will make SSAS Multidimensional queryable with DAX – at that point you will be able to query your cube directly from Power View.  Microsoft has not set a date for this, but it will be before the next release of SQL Server.

About James Serra

James is a big data and data warehousing solution architect at Microsoft. Previously he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 25 years of IT experience.
This entry was posted in Power View/Project Crescent, SQLServerPedia Syndication. Bookmark the permalink.

Comments are closed.