SSAS Many-to-Many Dimension Attribute: Single Letter Grade

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.