Dimensional table design dilemma, Aditional column or Xref table

Here is my situation.

We have a currency dimension table which stores the ISO currency codes (e.g. USD for US Dollar) and a surrogate key, currency description along with some maintenance columns (insert date, update date etc..).

We are trying to introduce a new source system which doesn't use ISO currency codes. They have their own 3 digit numeric code for every currency. And here is the dilemma on how to incorporate the new codes in to the system.

1st option: Add one column to the existing currency table, name it as NEW_SOURCE_CURRENCY, do a one time manual entries in to that column. Use this new column to lookup the surrogate key. Going forward, when a new code comes in from any system, new record will be inserted.

2nd option: Maintain a separate cross reference (XREF) table which shows the mapping between the new source system codes and the ISO codes. Do a lookup first on the XREF table to get the ISO code and then use the ISO code to lookup on the Currency table to get the key.

I invite all your thougths to discuss pros and cons of both the options.

My recommendation is that you have a dimension table only for currencies. In your case, a new column needs to be added with the primary key of your new data source. The problem then is to match the rows of both systems. This can be a one-off exercise for the initial load. For the new rows (new currencies appearing in new countries, for instance), this may be more complicated. However, this is not a situation you are going to face very often. I think that, if it's not possible to change the new data source to include the ISO code, you should have a mapping table between the two data sources codes so that whenever a new currency is created, you only need to manually add a row to this mapping table. So a blend of your two approaches would be the solution.

I hope this helps.

Best regards,

To make the right choice here I would think of keeping it simple for the end user, the one reporting on the data. I would think that a user is more likely to use the description of the currency than the code. In this case, I would maintain a mappiing table to map the 3-digit code to the ISO codes but use this table in the ETL process. The final table, in my opinion, need only house one of the two codes.