THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement