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.