Did you ever wish you could skip the many-to-many relationship hassle and just convert the text attribute to a measure? Well, if the attribute is a single letter, the answer is you can.

A client wanted a Grade captured for a cross between two dimensions, Product and Location.  They have a Grade for each combination of Store plus Department, where Department is an attribute of the Product dimension.  I started out by adding a dimension to the cube called Store Dept, and then a hidden measure group that only had the count in it, so that I could create the many-to-many relationship in the Dimension Usage tab.  This worked, but performance was very slow in retrieving the letter grade from the Store Dept dimension for the combination of Store Dept.  

I wondered how I could make this text attribute into a measure, since that’s really what is was.  SSAS will not allow text as a measure.  My first thought was to make A = 1, B= 2, etc feed it into the cube as a measure and then decode it on the report side.  But this is clunky since I’d have to decode every possibility  of 26 letters both on the way in and on the way out.  There has to be a much cleaner way to do this – and there is!

Convert the Letter Grade into it’s ASCII character value

1. Create the table as a FACT table containing the Location Code, Department Code, Store Dept Grade, and convert the letter grade into it’s ASCII integer value.

SELECT
Location_Code,
Department_Code,
Store_Dept_Grade,
ASCII(Store_Dept_Grade) as Store_Dept_Grade_ASCII  FROM tablename

2. Add the table to your cube data source view, connecting Location Code and Department Code to the appropriate tables.

3. Add a new Measure Group based on the FACT_Store_Dept_Grade table, and add the measure “Store Dept Grade ASCII”.  I like to keep ‘ASCII’ in the description so report builders will remember to convert it to a letter.  Set the AggregationFunction in the measure Properties window to “Min”. 

4.  Rerun the cube and go to the browser tab.  When you select Location Code and Department Code you will get the correct ASCII value of the Store Dept Grade.

5. Build your report query including the Store Dept Grade ASCII measure.  In your report right click on the textbox in which you would like to place the Store Dept Grade.  Enter the following Expression. The IsNothing bit will keep you from getting ‘NaN’ if it is null.

=Iif(ISNOTHING(Fields!Store_Dept_Grade_ASCII.Value)=TRUE,"",Chr(Fields!Store_Dept_Grade_ASCII.Value))

6.  Review your report.  The ASCII value of the Grade has been converted back to the correct letter.

This is a very clean workaround to making a text value into a measure for use in reports. No funky hard-coding on the front end or the back end.  Just interpret the letter as an integer into the cube and then interpret it as a letter on the report side.  You could even add a time dimension, if this is useful in your scenario.  You could aggregate as Last Child.  This wasn’t needed in this particular scenario, but this is a good starting point for other similar situations.