Edw - Project Which Dealt with the Surrogate Key Generation.

There were there layers in EDW:

1. Audit Layer

2. Base Layer

3. Package Layer

Audit Layer: Audit layer is the staging area 1 where the data from all different sources like oracle, sqlserver, flatfile etc are brought to the same database that is oracle with initial level of cleansing of data. Here the audit timestamp is recorded by entering the sysdate when the data is pulled into the audit layer.

Base Layer: Base Layer comprises of SK and LRF mapping concept.

SK Mapping :( Surrogate Key Generation)

SK mappings are developed mainly to capture the recent updates to the target. In other words SK mapping here in this context are developed to capture the slowly changing targets. The slowing changing dimension type 1 and type2 jointly used in APEX. We define a SRC_PK_VAL for a particular source and for that value we generate a surrogate key value using a sequence generator in an Informatica Mapping.The SRC_PK_VAL is the

Combination of those columns that have possibilities of updates.The update else insert logic is applied by bringing in target table as lookup. Every time the mapping runs it will check the src_pk_val and if it is a new record then it is inserted with a new surrogate key and if updates if the record already existed. While SK generation, the PK_HIS_SEQ value is set as 1 and the Deltachecksum value is set as X.

LRF Mapping :( Load Ready File Generation)

In a LRF Mapping we load the data required for the base tables to a flat file. The data comes from the same source as that of the SK mapping. The Fields for which the changes are to be captured are concatenated and passed to the deltachecksum field using MD5 hash alogorithm. This value is compared with that of the value inserted using the SK generation. When both these values are different (it means the data is not there in the base table) so the data passes thru the filter used in the mapping and flows to the target flat file. While doing so, using an Update strategy we update the...