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: Measures Are Imported as Text Columns

Every measure imported from a SSAS cube is imported as a Text column instead of as a Numeric column. This is very annoying because users are used to use SSAS data with rich metadata, and a measure is usually numeric. Moreover, the standard aggregation used by PowerPivot for a Text column is Count - whenever the measure is a real count (thus, an integer number), this is completely misleading because the user doesn't recognize it is returning a completely wrong value.

Moreover, there is a wrong use of Locale settings and conversion to decimal value gives an initial error whenever the local settings doesn’t use “.” as decimal separator. However, refreshing data everything is updated correctly, but the problem is that end users might stop their work before trying to refresh data after they changed the data type of each measure column to the right type.

Thus, this is the check-list to follow whenever you import measures from an existing SSAS cube to PowerPivot:

  • Change column type to Numeric (all measures are imported as Text by default)
  • Refresh data – initial conversion of decimal and thousands separator might be wrong (because the string is converted to a numeric by PowerPivot, and differences in Locale settings might be the issue in this conversion), but after you fixed the data type, the following refresh query the cube again and returns the right value.

UPDATE 28 MARCH 2011: check another workaround in this more recent post.

Published Monday, August 30, 2010 9:05 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

 

SQLBI - Marco Russo said:

Several months ago I wrote about the issue importing measures from an Analysis Services cube in PowerPivot.

March 28, 2011 6:42 AM

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