none
Measures treated as strings BUG ARRRGGHH! RRS feed

  • Question

  • If we use SSAS as a datasource, my measures are always treated as strings, meaning the only thing that works on them is a COUNT.  If I want to sum or avg or any other reasonable aggregation I have to convert the column.  My users shouldn't have to deal with data types or obscure error messages about strings.  I built a ODS, DW, and SSAS database to smooth out all the nastiness of dealing with this data, and all of this work is muddied up by this problem that I have to solve repeatedly on a case by case basis.  Is this a known bug that is going to be fixed?
    Friday, July 1, 2011 4:38 PM

Answers

  • AFAIK It is a "bug ny design" :)

    Moreover, since values are loades as strings, pay particular attention to locale settings. Using commas instead of periods in any number, for international companies, might become a real pain for all the PowerPivot users. Maybe they will fix it in the next release, but nothing has been granted for sure up to now, everybody's waiting for the incoming CTP which might contain some useful information about this.

    Moreover, keep in mind that the same problem applies with dates (DD/MM/YYYY or MM/DD/YYY). I know, it is a pain in the neck, but it is better to know in advance that you might encounter problems than to blindy come into them. :)

    You might consider creating SQL views that query the OLAP cube and expose correct metadata to your users, for some very specific queries. But, in general and for now, the problem has no solution.


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    Tuesday, July 5, 2011 9:52 AM

All replies

  • AFAIK It is a "bug ny design" :)

    Moreover, since values are loades as strings, pay particular attention to locale settings. Using commas instead of periods in any number, for international companies, might become a real pain for all the PowerPivot users. Maybe they will fix it in the next release, but nothing has been granted for sure up to now, everybody's waiting for the incoming CTP which might contain some useful information about this.

    Moreover, keep in mind that the same problem applies with dates (DD/MM/YYYY or MM/DD/YYY). I know, it is a pain in the neck, but it is better to know in advance that you might encounter problems than to blindy come into them. :)

    You might consider creating SQL views that query the OLAP cube and expose correct metadata to your users, for some very specific queries. But, in general and for now, the problem has no solution.


    Alberto Ferrari
    http://www.powerpivotworkshop.com
    Tuesday, July 5, 2011 9:52 AM
  • I'm trying hard to understand what you mean by "bug by design."

    I have, for example, a measure coming over from a cube that, in my DSV, is tagged System.Decimal.  On the cube side, in the measure group, its type is 'Inherited' and I have it formatted as currency.  This would seem a common enough scenario.

    PowerPivot 10.x refuses to import this measure as a numeric data type.

    I understand the behavior of CTP3 is identical.

    Is this behavior really by design, or am I not understanding something?  When I use the cube directly in an Excel pivot table, without going through PowerPivot as an intermediary, everything works as expected.

    Richard Goerwitz

    Saturday, October 15, 2011 7:31 PM
  • It might be working with RTM of SQL Server 2012 (formerly codename Denali). I've seen on Connect the bug should be fixed... but we have to wait that release. Marco
    Marco Russo http://www.powerpivotworkshop.com http://sqlblog.com/blogs/marco_russo
    Sunday, October 16, 2011 8:09 PM