Populating Product dimension

Hi,

I have a Product dimension table which I need to populate. There are 2 columns in this dimension 'Product Code' and 'Product Description'.

The Product Code comes from a concatenation of 2 columns from my staging table (which forms the business key) eg: ABC(concatenated with) 123 -> ABC123
There is a 3rd column in this staging table that is used to map to the Product Description, however the 'Product Description' comes from a business rule mapping in the functional specification (as opposed to coming from a staging table).
The business rule mapping goes something like this:
If 3rd column is 'XX' then Product Description = 'Desc1'
If 3rd column is 'YY' then Product Description = 'Desc2'
If 3rd column is 'ZZ' then Product Description = 'Desc3'
etc etc.....

So my staging table looks like this:
Col1 Col2 Col3
----------------------
ABC 123 XX
DEF 456 XX
GFC 333 YY
HHH 000 ZZ
KOL 243 YY
etc etc

So my question is, how do I model this 'business rule mapping' in order to get my product description? I am thinking of just creating a simple 2 column mapping reference table 'Col3 and Description' and then just joining to this reference table in my MERGE query in MS SQL to merge data into my product dimension. The business would have to advise me if there are changes to this mapping table in which case I will have to update it manually.
Is this approach advisable?

Thanks for your advise.

I'm a relational guy at heart, so creating a table sounds like a reasonable approach to me. Essentially, you are creating a separate Product Description table, with Col 3 being the key to find a business-supplied description. I would see if you can have someone on the business side be responsible for keeping this table current. You can create a simple .NET front end for them, or there are tools that you might use, like Microsoft's Master Data Services in 2008 R2. Have your ETL system look for new Product Codes, add them to the Product Description table, with a default value of "Missing Code! Contact [owner] for correct value." Then, the ETL system should email the owner to let them know they have to make a correction. You could even send them the code(s), or have the .NET front end automatically list the ones that have the default value for the description.

By the way, I usually think of the product description as a unique description of the product. In other words, it would have a one-to-one relationship to the source system key. In your case, you have multiple product keys, as defined by Col 1 and Col 2 concatenated, that map to the same product description. As long as the business users understand that what you are calling Product Description is really a combination of multiple individual rows in your product table, this will be OK.

Thanks for your advice warrent.