none
String Measures RRS feed

  • Question

  • Hello everyone,

    i have a requirement where i need to show some column as a measure but it is of String type. when i add this in the cube, the cube throws an error saying STRING measures are not allowed. How do i do this? however i do not want to aggregate them,  Kindly help!

    Thursday, March 25, 2010 10:20 AM

Answers

  •      As Darren has said - if you can't aggregate it in some way it is likely not a measure.  Other technologies allow you to be sloppy with what you call a measure (Bus Obj), but SSAS is very precise in this aspect.

         Having said that - if you have a requirement to put text in the measures area, this is one way to do it.  I'll use an example:

    Rating     RatingAggregatable

    A            10

    B            5

    C            0

    Create a calculated measure on RatingAggregatable that is the average (Sum/count).  Use this calculated measure to create another measure like:

    CASE
    
     WHEN [Measures].[Yourmeasure]>= 7.5  THEN 'A'
    
     WHEN [Measures].[Yourmeasure]>= 2.5  THEN 'B'
    
     WHEN [Measures].[Yourmeasure]>= 0  THEN 'C'
    
     ELSE 'NR'
    
    END

     

    • Marked as answer by Ashvini_Rao Friday, March 26, 2010 4:01 AM
    Thursday, March 25, 2010 1:06 PM

All replies

  • Hi,

     

    Measures accepts only digits, (int, real, double..) so only countable values.

    If you have an column to show that is not aggretable, put it in an dimention that is connected to the fact table. Let the CUBE wizzard generate the cube and dimention for you.

    Thursday, March 25, 2010 11:21 AM
  • If you can't aggregate it, then it's probably not a measure. Why not just model it as a dimension attribute?
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Thursday, March 25, 2010 11:32 AM
    Moderator
  • If you want to show String data  - within tables for example - somehting like this:

    Name Company Location

    Isaac       EFG LTD   US

    Ahmed     Sparta     Canada

     

    Then you might benefit from SSRS Reports which are based on SQL Queries that can run directly over your Data warehouse or MDX ones over your Cubes.

     

    Regards,


    omar
    Thursday, March 25, 2010 12:02 PM
  •      As Darren has said - if you can't aggregate it in some way it is likely not a measure.  Other technologies allow you to be sloppy with what you call a measure (Bus Obj), but SSAS is very precise in this aspect.

         Having said that - if you have a requirement to put text in the measures area, this is one way to do it.  I'll use an example:

    Rating     RatingAggregatable

    A            10

    B            5

    C            0

    Create a calculated measure on RatingAggregatable that is the average (Sum/count).  Use this calculated measure to create another measure like:

    CASE
    
     WHEN [Measures].[Yourmeasure]>= 7.5  THEN 'A'
    
     WHEN [Measures].[Yourmeasure]>= 2.5  THEN 'B'
    
     WHEN [Measures].[Yourmeasure]>= 0  THEN 'C'
    
     ELSE 'NR'
    
    END

     

    • Marked as answer by Ashvini_Rao Friday, March 26, 2010 4:01 AM
    Thursday, March 25, 2010 1:06 PM
  • If you have a table with columns Year (dimension) Country (string measure - where the company had sales that year), the equivalent of SUM numeric aggregate is comma-delimited list.

    The following link shows how can you pivot string column into a comma-delimited list:

    http://www.sqlusa.com/bestpractices/training/scripts/commadelimitedlist/

    Let us know if helpful.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner SQL Programming Using Microsoft SQL Server


    • Edited by Kalman Toth Saturday, October 20, 2012 9:02 AM
    Thursday, March 25, 2010 1:11 PM
  • Thanks everyone for the replies, it is helpful. I shall try them out.
    Friday, March 26, 2010 4:02 AM