String Measures

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!

Answer 1

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.

Answer 2

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

Answer 3

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

Answer 4

     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

 

Answer 5

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

Answer 6

Thanks everyone for the replies, it is helpful. I shall try them out.