Loading Dimension and Fact Tables

In Data warehousing dimension table are not related to each other in star schema because all dimension tables are de-normalized but on the other hand in snow flake schema dimension table are related to each other till some extent.

First we load dimension table one by one then fact table.

While loading fact table, dimension surrogate key as foreign key in fact table and dimension table are lookup’ed while loading fact table. We can load dimension table directly no need to use fact table while loading dimension table.

We can load fact and dimension table in one mapping using target load plan or in one session using event wait or using decision task.

While loading fact table you will have to lookup on dimension table to get the relevant attributes.

As Dimension table in star schema are not related to each other it is not common to see them being loaded simultaneously. In such cases workflow would have to start task followed by dimension table mapping in parallel and then decision take place which checks if all these dimension are loaded without errors. If there are no errors then we can proceed to load fact table.

If we are doing SCD-2 the following above step would be helpful to improve performance.