All dimension tables gets loaded first followed by Fact tables in the
Data Warehousing world
As you can see in the above picture, the ETL tool process source data from different sources and load them in the dimension table with the natural key and surrogate key (A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database.)
The
fact table read the same source data and it dose the look-up against the
dimension tables to get the surrogate key for the particular and load them in
the fact table as reference key
Here
is a practical example
Row id
|
Emp First Name
|
Emp Last Name
|
EMP No
|
EMP Dep
|
EMP Sal
|
1
|
John
|
Beck
|
2001
|
A
|
1000
|
2
|
Mike
|
Morry
|
2002
|
A
|
2000
|
3
|
Kevin
|
Peter
|
2003
|
B
|
1000
|
4
|
Steve
|
Morry
|
2004
|
B
|
2000
|
5
|
Jim
|
Chen
|
2005
|
C
|
3000
|
The
employee dimension will be loaded like this
Employee Dimension
|
||||
Emp_Surrogate_Key
|
EMP No
|
Emp First Name
|
Emp Last Name
|
EMP Dep
|
100001
|
2001
|
John
|
Beck
|
A
|
100002
|
2002
|
Mike
|
Morry
|
A
|
100003
|
2003
|
Kevin
|
Peter
|
B
|
100004
|
2004
|
Steve
|
Morry
|
B
|
100005
|
2005
|
Jim
|
Chen
|
C
|
The
employee Fact table will be like this
Employee Fact
|
|
Emp_Surrogate_Key
|
EMP Sal
|
100001
|
1000
|
100002
|
2000
|
100003
|
1000
|
100004
|
2000
|
100005
|
3000
|
The
Employee fact joins to the employee dimension using the Emp_surrogate_key
Select emp_dim* from
Employee Dimension as emp_dim,
Employee Fact as emp_fact
Where
emp_dim. Emp_surrogate_key=emp_fact.
Emp_surrogate_key
0 comments: