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: 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)



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

Diya said:


Im Importing Tabular model into PowerPivot with one measure"Product count per Customer"(unchecked the Import as text checkbox)

If that customer have 500 products in last month , increased to 600 in current month, now that measure is showing both values when I drag it onto the rows pane in report. but I want to show only latest number.

Thank you!

February 26, 2015 1:18 AM

Marco Russo (SQLBI) said:

You need a filter or include year month in the columns to extract

February 26, 2015 1:25 AM

Diya said:

Yes, got it

Thank you.

February 26, 2015 1:34 AM
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