Are Ramakrishna Rao

• Aug 14th, 2007

 

Late arriving Fact table:

        This is rarely happens in practice.

For example there was a credit card of HDFC transaction happened on 25^th Mar 2005, but this record we received on 14^th Aug 2007. During this period there is a possibility of change in dimensional data.

There are two table one is transaction fact table and customer dimensional table

Transactional fact table is having two time stamps 1. Transaction date 2. Load date

Customer dimensional table is having two time stamps 1. Enter date (when customer provided the details) 2. Load date

In case of late arrive fact records we have get the proper key from the dimensional tables as these tables are maintaining the history.

In above scenario we have to extract the right key by using transaction date <= enter date from customer dimension table for the particular customer.

Late arriving Dimension table:
   This is very general practice in consumer finance projects

Some times we will get the transactional data without dimensional date. In this practice we will follow two methods.

1. Create one dummy record in dimensional table and join with fact table. Once we receive this data the dummy record in the Dim table will be updated using type SCD1
2. Place the transactional data in staging table till we receive the dim records. Re keying processed will be used to get the key from dim table

Re keying process: Generally in ETL process first we will load dim records and then go for facts. At the time of loading fact records keying will be done with dim tables. The unkeyed records will be kept in staging table and remaining data will be stored in target table. Next day at the time of keying process the unkeyed data will also used for keying is called as re-keying. Re-keying process will be continued till we get the key from dimensional tables

Sukanya Das

 Profile Answers by Sukanya Das 

• Jun 4th, 2011

 

Late arriving Fact table:
For example there was a credit card of transaction happened on 25th Mar 2005, but this record we received on 14th Aug 2007. During this period there is a possibility of change in dimensional data.
There are two table one is transaction fact table and customer dimensional table
Transactional fact table is having two time stamps 1. Transaction date 2. Load date
Customer dimensional table is having two time stamps 1. Enter date (when customer provided the details) 2. Load date
In case of late arrive fact records we have get the proper key from the dimensional tables as these tables are maintaining the history.
In above scenario we have to extract the right key by using transaction date <= enter date from customer dimension table for the particular customer.


Late arriving Dimension table:
This is very general practice in consumer finance projects
Some times we will get the transactional data without dimensional date. In this practice we will follow two methods.
1. Create one dummy record in dimensional table and join with fact table. Once we receive this data the dummy record in the Dim table will be updated using type SCD1
2. Place the transactional data in staging table till we receive the dim records. Re keying processed will be used to get the key from dim table

Kindly provide few more updates if you have on this.