In this post we will discuss how to load a
fact table in a data warehouse using your dimension table and the data staged
in a staging table. We will just show you what is the procedure of loading and
further complexities depends upon your business requirements.
In our example we took THREE dimension tables.
- DIM_BOOK
- DIM_CUSTOMER
- DIM_TIME
All of the dimension tables are SCD-type
implemented other than TIME DIMENSION.
STEPS:—->>>
1) create an connection first as i am using oracle
as a database that’s why i used tOracleConnection.
2) After Successful connection run the
further part.
From Dimension tables you have to fetch the data
using Query Editor and place a condition where
END_DATE is null, if you have implemented SCD
type-2 This will bring all those recordswho have currently validated state.
Now Fetch the OLTP data stored currently in Staging
Table, and put all connections in tMap.
REMEMBER—-your staging table that has to be loaded
must be first linked then only it will work fine and all other dimensions
connected will work as a LOOKUP.
Now in tMap join all the dimensions with your
source data using the keys and fetch down the SURROGATE_KEY and put all those
skeys in the fact table. In my join condition i have used inner join as a join
method.
In tmap component i have use
some calculations to find out what are the percentage in discount and total
value for the order . It may be anything depend upon your requirements.
note:- Just keep in mind data type conversion you
have to keep in mind other wise it’ll give you trouble a lot in my case i just
converted my data types in staging itself.
