Ok this one is for all of you who like me have to live and work with different regional settings than English US. This makes sure a lot of software behaves not as expected, PowerPivot also has strange behavoir.
When you import data from SSAS into PowerPivot all column are treated as text. This is a by design feature of PowerPivot:
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.
as we can read on this forum post. Sounds like a solution that makes sense, but it also gives the end user more to do. You have to convert all the columns that are not text (like date and decimals) to the type you want in the PowerPivot window. This might give you a problem when you have different regional settings on the client than on the server. In my case our SSAS server uses the English US regional settings on the server but i have Dutch settings on the client.
When I import data into PowerPivot all seems ok at first:
But notice that our measure is imported as text. To make aggregations you need to covert this to a decimal, doing this give strange results:
As you can see here the decimal sign is just removed and we have one big number now. Thanks to Marco Russo i found a workaround. Because we changed the data type to decimal means PowerPivot treats this column as decimal from now on, also when refresh data. And that is all you have to do to get the correct data. Refresh the data and the numbers are correct again (and will be in the future):
Marco has created a connect item for this to be solved in the next version as well, please vote for it here so they will take a look at it in the next version(s).
I don’t know what happens when you have the same regional settings on the server as on the client, but I expect it to work correct without having to use the workaround.
Another tip: When you change the regional settings of your client machine make sure you restart Excel, only then will it pick up the changed regional settings.