If I have a dimension in Analysis Services where the base table has columns like this:
TransTypeKey TransTypeCode TransTypeDescription TransCategoryCode TransCategory Description
where the description columns are just friendly names for the corresponding 'code,' what's the best way to capture that? Concatenate the code and description when loading the dimension? Keep them separate?
That would depend on what the user wants to see in the final cube. Is the dimension going to be sorted by the concatenated field? Do they normally sort/search by description or the code? If it is both you will need attributes for both versions or concatenate both ways: Code-Description as well as Description-Code.
In any case I'd leave the base table as is then concatenate them in a view if you have access to the source database or in the cube dsv if that is the only choice. That gives you some flexibility going forward.
If the code is unique or can compositely makes up uniqueness, You can assign the code to the member key property and the description to the member name property.
This works really well and keys your key sizes small assuming your codes are simple integers or small character compared to the larger description fields.