It is a conceptual description. I was not able to put each and every peace of code while writing this.
There can be many ways to load SCD Type 1 (slowly changing dimension) dimension table in data warehouse. Here is an attempt to describe one of them.
Read if you are new to analytic data processing. Dimension table load is key to ETL system and in fact most interesting thing to do apart from system design.
There are many types of dimensional tables exist as per their ability to impact fact data analysis. SCD Type 1 (slowly changing dimension) is one such Slowly changing Dimension table type wherein the dimension attributes will be overwritten if change occurs and the fact data is always associated with the current value. Now the questions is how can we create he mapping that can be used to load a SCD Type 1 dimension in Informatica.
We have taken the example of a printer manufacturing company ABC which sells Toner, Ink Cartridge under Printer Accessories product group. This company has expanded its products list by acquiring other IT / Hardware companies and now the company ABC is more interested to have a Accessories product group so it slashed Printer Accessories product group permanently. Now the orders and shipments is very well connected to the update product group.
Product Dimension – Earlier Before loading
Product Dimension – New After loading
Mapping to apply these changes in Informatica
Below is the pictorial representation of mapping to load SCD Type 1 (slowly changing dimension) table.
Transformation design considerations
- The mapping is to be designed based on the source data for the SCD Type 1 (slowly changing dimension) table.
- If the source data is expected to have multiple rows for one product key, go with dynamic lookup in the mapping.
- If the source data will always have one row for a given product key, you can count on using static lookup.
- Lookup condition will be old.prod_key = new.prod_key (Consider old, new are the tables where old & new data is preserved)
- Output new data from the lookup transformation.
- Label the rows in Update strategy transformation
- If Output row from lookup has null value for Prod_key, label it as Insert
- If output row from lookup has not-null value for prod_key label it as Update
- The logic looks similar to this – IIF(new.prod_key is null, DD_INSERT, DD_UPDATE)
- Use Newlookuprow column if you are using Dynamic lookup in this mapping
- Ensure the session properties for Target are set appropriately – Data Driven / Insert & Insert else Update (default)
- The mapping is on
Hope it was simple enough !! But you cannot ignore the level of impact analysis to be done before you categorize your dimension table as SCD type 1. At the same time, the SCD type 1 changes demands a huge refresh of BI data and cubes if there is any performance metric defined on the column(s) that are subjected to change in SCD type 1 dimension table.
Do let us know if you wish to get added something here ….