Informatica

SCD1 in Informatica

SCD1 means Slowly Changing Dimension Type 1, Which is used to store current data. Here no need to store historical data. It will update the old data with the new data in dimension table. 
The SCD1 process is 
  • Identifying the new record and insert  in to the table.
  • Identifying the changed record update it in to the table.
Implementation of SCD1 with Example
  • Create source  DEPT table and drag it in to mapping designer.
  • Create target DEPTT with same structure and drag them in to the  designer for two times one for insert and the other for update. 
  • Create a lookup transformation on target table. Add port src_DEPTNO to lookup table
  • Add lookup condition DEPTNO=src_DEPTNO
  • Create an expression transformation connect port from lookup transformation and connect source ports from source qualifier transformation.
  • Add two new ports and write expression on editor.
                  Port1: NEW_REC_IND=IIF(ISNULL(DEPTNO),1,0)
                  Port2: CHG_REC_IND=IIF(NOT ISNULL(DEPTNO)ND(DNME!=src_DNAAME),1,0)
  • Create  filter transformation, connect source ports from source qualifier to filter and also NEW_REC_IND from expression to filter.
  • Write filter condition NEW_REC_IND
  • Connect filter to update strategy transformation and connect source ports in filter to update strategy transformation. 

  • Create  filter transformation, connect source ports from source qualifier to filter and also CHG_REC_IND from expression to filter.
  • Write filter condition CHG_REC_IND
  • Connect filter to update strategy transformation and connect source ports in filter to update strategy transformation. From update properties give a condition DD_UPDTE. 
  • Connect first updte strategy to first target, It is for insert.
  • Connect second update strategy to second target, It is for update. 
  • The final mapping will be like below.
Note
Here we also use Router Transformation in place of Filter Transformation by creating two different groups in Router.
Powered by Blogger.