SSAS Measure defaults to text datatype not numeric.

Question

I have brought a simple set of data from SSAS (2008 R2) which contains 2 dimensions (Product, Customer) and one measure (Volume). I brought these in via the PowerPivot table import dialog from the SSAS cube - imports about 80K records, no issues.. 

when I open pivot table on this data, if I double-click on the volume measure  field,  it goes to the Row Lables  not to the Values, and if i drag it to Values it defaults to  "Count of" MeasuresVOLUME. 

In checking the table that was imported, it defaulted the measure column to text datatype. When i selected the column and updated the datatype to number (decimal),  then reset the field in the pivot-table  it now has the sum behavior I would have expected... 

What am i missing here - shouldn't these measures be defaulting to the meta from the SSAS cube?

Answer 1

This is a by design behavior in the current version of PowerPivot. The reason behind is due to the fact PowerPivot does not support Variant data type, while MDX does. The cell value from a MDX query may potentially return different data type, for instance, in a IIF statement IIF (condition, expression1, expression2), expression1 and expression2 can return different data type. In order to support all these scenarios inside PowerPivot, we convert the values to string during import. You would have to explicitly change the column data type after the import as you have already observed.

Hope this helps.

Thanks,
Lisa

Answer 2

Is it possible for PP to detect that what it's importing is a measure - or to allow a user to mark this fact when importing (easily)?

Right now the behavior is confusing, particularly for business users.  After searching forums for words like "analysis services," "numeric," "string," and "data type" I found an answer to my question.  But I can't imagine a business user doing this.  They'll just call me, if they get that far, and ask what's wrong, if, that is, they get that far.

---

Richard Goerwitz Carleton College