Printed 2017/08/21 03:52PM

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.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.