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.