Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimensional table design dilemma, Aditional column or Xref table

3 posters

Go down

Dimensional table design dilemma, Aditional column or Xref table Empty Dimensional table design dilemma, Aditional column or Xref table

Post  kvkeee 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

Back to top Go down

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

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

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

Back to top Go down

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

Post  beyeguru 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

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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