Answered by:
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!
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
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.


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 
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

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 commadelimited list.
The following link shows how can you pivot string column into a commadelimited 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
