THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Using SSAS 2005/2008 as PowerPivot Data Source: Dimensionality Lost

This topic is general and not related to a specific feature of PowerPivot. In the way it is designed today, when data are imported from a SSAS cube into PowerPivot, the dimensionality of the original cube is lost and a flat table is imported as the result of a single MDX query. This approach should work in PowerPivot considering the way data are compressed. However, whenever the user want to import data from several cubes that shares some dimensions (despite the fact they are on different servers), it would be better if original shared dimension would have been imported in PowerPivot as separate tables.

Thus, it would be useful having a wizard to rapidly import existing dimensions as single tables, and then another feature to easily related measures data imported from an MDX query with other tables already existing in Analysis Services. The actual hiding of attribute keys information is a major drawback to achieve this goal, even if a user would try to do the job manually.

The only workaround possible today is to write a separate MDX query for each dimension, and then an MDX query for each measure group. All these queries have to show keys that allows to create relationships between fact and dimension tables in PowerPivot.

Published Tuesday, August 31, 2010 9:09 AM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement