SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

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.

James Serra's Blog

James is a big data and data warehousing technology specialist at Microsoft. He is a thought leader in the use and application of Big Data technologies, including MPP solutions involving hybrid technologies of relational data, Hadoop, and private and public cloud. 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 30 years of IT experience. James is a popular blogger (JamesSerra.com) and speaker, having presented at dozens of PASS events including the PASS Business Analytics conference and the PASS Summit. He is the author of the book “Reporting with Microsoft SQL Server 2012”. He received a Bachelor of Science degree in Computer Engineering from the University of Nevada-Las Vegas.


Leave a comment on the original post [www.jamesserra.com, opens in a new window]

Loading comments...