Lookup table best practice for DWH
Hello I am new to the DWH world.... and may be a very basic question what i want to ask....
If I have two tables on OLTP named as customer and contract such that the data in the contract come for each customer and later I receive repayments agianst that contarct (pay as you go).
I have thought if two ways to implement it
1. What i have done in DWH is that i have created two dimensons such that d_customer containts data about customer and d_contract contains the data for contracts with a customer key in it . Later i have populated the dimension of customer OutStaning balance
based on the repaments OLTP data. so the fact table f_balances contains the keys of d_customer and d_contract and a lookup will see the valid values from d_contarct only as it will already have valid list of values for a specific customer against
a specific contact.
2. i was thinking the other approach in which d_contract will have unique contract data regardless of client (client kwy will not appear in d_contarct table) and in the fact table f_balances will have look up for each of d_client and d_contarct
Please advise which one of the two is valid and best practice keeping in vew the DWH archictiure and designDigitalFM
December 15th, 2010 10:35am