Dimensional table design dilemma, Aditional column or Xref table

View previous topic View next topic Go down

Dimensional table design dilemma, Aditional column or Xref table

Post  kvkeee on Mon Sep 28, 2009 12:24 pm

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.

kvkeee

Posts : 2
Join date : 2009-07-09

View user profile

Back to top Go down

Re: Dimensional table design dilemma, Aditional column or Xref table

Post  alex.caminals on Mon Sep 28, 2009 6:24 pm

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,
avatar
alex.caminals

Posts : 15
Join date : 2009-02-25
Age : 41
Location : Barcelona (Spain)

View user profile

Back to top Go down

Re: Dimensional table design dilemma, Aditional column or Xref table

Post  beyeguru on Tue Sep 29, 2009 1:00 am

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.

beyeguru

Posts : 5
Join date : 2009-08-03

View user profile

Back to top Go down

Re: Dimensional table design dilemma, Aditional column or Xref table

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum