Skip to content

Datawarehousing – Things to Consider – Reference Tables

Summary: Reference Tables in a Datawarehouse

Reference Tables in a Datawarehouse  are very important. These are the tables which have at a minimum – 2 columns. CODE_TYPE and CODE_DESCRIPTION. The business codes and their descriptions are stored in these tables. There is normally more than 1 reference table in a datawarehouse.These CODE_TYPE values  are then used in the  child tables. The normal practice is to ensure that only those codes that exist in the reference tables are  used in the child tables. Child tables are other tables like Fact tables and other Dimension tables or any other tables which hold the core datawarehouse business data.

Should we Use Foreign keys between Reference Tables and the Child Tables?

If the Reference Table supports Historization then it will be difficult to implement foreign key relationships.Historization means the valid_from and valid_to columns present in the Reference Table.So you could have values like

CODE_TYPE    CODE_DESCRIPTION                    VALID_FROM   VALID_TO

EUR                     Euro Currency Description old      01-01-2015             31-03-2015

EUR                     Euro Currency Description new     01-04-2015            31-12-9999

So we see that the CODE_TYPE column cannot be the primary key since  the EUR value is repeated more than once .Hence it cannot be a foreign key in the child table. ETL Tools like Informatica hence use logic to use the reference table as a lookup table to ensure the referential integrity is implemented through the informatica workflows and not through foreign key relationships.

There is an Alternative solution to use numerical surrogate keys . In this case however we need to consider additional logic to apply on the valid_from and valid_to dates to ensure we get the correct code descriptions.

S.Key       CODE_TYPE    CODE_DESCRIPTION                    VALID_FROM   VALID_TO

1                 EUR                     Euro Currency Description old      01-01-2015             31-03-2015

2                 EUR                     Euro Currency Description new     01-04-2015            31-12-9999

 

Child Table

PRODUCT_NO   PRODUCT_QUANTITY PRODUCT_PRICE CURRENCY_CODE  VALID_FROM     VALID_TO

P100                               5                                       23.50                                                1                     01-01-2015        31-12-9999          

 

We see that the product table uses the  currency_code value 1 . This points to the old currency description.This column can be used as the foreign key since the values are incremented sequentially by using an oracle sequence for example. However when we run the report on April 9th  we should be able to see the new currency description.So our query needs additional logic to consider the date when the report was run and then displaying the description that is valid for that date.We can alternatively have a product table based on snapshot_date where every day the table is inserted with new rows and will contain values valid for that particular day.The reporting tools would then read this new product table so the valid currency descriptions are already filled in.

In our project the decision not to implement foreign keys at the database level was taken. This is because of the presence of historization columns and the complexity arising from the surrogate keys mentioned in the above option.The referential integrity in the datawarehouse would then be handled by the Informatica ETL Flows.

 

 

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.