Ideas on Populating the Fact Table in a Data Mart

Question

Hi I am looking for ideas to populate a fact table in a data mart. Lets say i have the following dimensions

1. Physician
2. Patient
3. date
4. geo_location
5. patient_demography
6. test

I have used two ETL tools to populate the dimension tables- Pentaho and Oracle Warehouse Builder. The date, patient demography and geo locations do not pull data from the operational store. All dimension tables have their own NEW surrogate key.

I now want to populate the fact table with the details of a visit by a patient.When a patient visits a physician on a particular date, he orders a test. This is the info in the fact table. There are other measures too that I am omitting for simplicity.

I can create a single join with all the required columns in the fact table from the source system. But, i need to store the keys from the dimension tables for Patient, Physician, test etc.. What is the best way to achieve this?

Can ETL tools help in this?

Thank You Krishna

Answer 1

Each dimension table should have a BusinessKey that uniquely identifies the object (person, date, location) that a table row describes. During loading of the fact table, you have to lookup the PrimaryKey from the dimension table, based on the BusinessKey. You can choose to lookup the dimension table directly, or create a key-lookup table for each dimension just before loading the fact table.

Pentaho Kettle has the "Database Value Lookup" (transformation step) for the purpose. You may also want to look at the "Delivering Fact Tables" section of Kimball's Data Warehouse ETL Toolkit.