Certification Project – Create Retail Datamart using Informatica PowerCenter

Problem Statement.
The purpose of this solution is to explain how to create a retail data warehouse / Datamart using PowerCenter.

Description:
Transaction and master data from OLTP (online transaction processing) systems is loaded into the data warehouse as Fact tables and Dimension tables respectively. In this project, we consider a Sales Order Processing system as a source with the following tables

Solution:
Please find the mappings/workflows with required screen shots.

Mapping Name:- m_load_dim_channels
Source Table:- CHANNELS
Target Table:- DIM_CHANNELS
Action:- Update/Insert
Mapping Type:- SCD type 1 mapping
Transformations:-
Connected Lookup: – To check whether the record exist on target
Expression: – To compare new & existing records and identify changes.
Filter1:- Process ONLY new records
Filter2:- Process ONLY existing records
Update Strategy1:- Insert target table as Insert (For New records)
Update Strategy2:- Update target table as Update (For existing records)
Sequence Generator: – To Generate Primary Key

Mapping Name:- m_load_dim_countries
Source Table:- COUNTRIES
Target Table:- DIM_COUNTRIES
Action:- Update/Insert
Mapping Type:- SCD type 1 mapping
Transformations:-
Connected Lookup: – To check whether the record exist on target
Expression: – To compare new & existing records and identify changes.
Filter1:- Process ONLY new records
Filter2:- Process ONLY existing records
Update Strategy1:- Insert target table as Insert (For New records)
Update Strategy2:- Update target table as Update (For existing records)
Sequence Generator: – To Generate Primary Key

Mapping Name:- m_load_dim_promotions
Source Table:- PROMOTIONS
Target Table:- DIM_PROMOTIONS
Action:- Update/Insert
Mapping Type:- SCD type 1 mapping
Transformations:-
Connected Lookup: – To check whether the record exist on target
Expression: – To compare new & existing records and identify changes.
Filter1:- Process ONLY new records
Filter2:- Process ONLY existing records
Update Strategy1:- Insert target table as Insert (For New records)
Update Strategy2:- Update target table as Update (For existing records)
Sequence Generator: – To Generate Primary Key

Mapping Name:- m_load_dim_customers
Source Table:- CUSTOMERS
Target Table:- DIM_CUSTOMERS
Action:- Update/Insert
Mapping Type:- SCD type 2 mapping
Transformations:-
Connected Lookup: – To check whether the record exist on target
Expression: – To compare new & existing records and identify changes.
Filter1:- Process ONLY new records
Filter2:- Process ONLY existing records
Update Strategy1:- Insert target table as Insert (For New records)
Update Strategy2:- Update target table as Insert (For existing records)
Update Strategy3:- Update the Effective end date to Target (For Existing Records)
Sequence Generator: – To Generate Primary Key
Expression1:- To uniquely assign primary key (For New Records)
Expression2:- To uniquely assign primary key (For Existing Records)

Mapping Name:- m_load_dim_products
Source Table:- PRODUCTS
Target Table:- DIM_PRODUCTS
Action:- Update/Insert
Mapping Type:- SCD type 2 mapping
Transformations:-
Connected Lookup: – To check whether the record exist on target
Expression: – To compare new & existing records and identify changes.
Filter1:- Process ONLY new records
Filter2:- Process ONLY existing records
Update Strategy1:- Insert target table as Insert (For New records)
Update Strategy2:- Update target table as Insert (For existing records)
Update Strategy3:- Update the Effective end date to Target (For Existing Records)
Sequence Generator: – To Generate Primary Key
Expression1:- To uniquely assign primary key (For New Records)
Expression2:- To uniquely assign primary key (For Existing Records)

Mapping Name:- m_load_fact_sales
Source Table:- SALES
Target Table:- FACT_SALES
Action:- Update/Insert
Mapping Type:- SCD type 1 mapping
Transformations:-
Connected Lookup: – To check whether the record exist on target
Expression: – To compare new & existing records and identify changes.
Filter1:- Process ONLY new records
Filter2:- Process ONLY existing records
Update Strategy1:- Insert target table as Insert (For New records)
Update Strategy2:- Update target table as Update (For existing records)
Sequence Generator: – To Generate Primary Key
Un-connected Lookup1:- Populate Product Key from DIM_PRODUCTS table
Un-connected Lookup2:- Populate Time Key from DIM_TIMES table
Un-connected Lookup3:- Populate Promotion Key from DIM_PROMOTIONS table
Un-connected Lookup4:- Populate Channel Key from DIM_CHANNELS table
Un-connected Lookup5:- Populate Customer Key from DIM_CUSTOMERS table

Well done !!!. You have successfully completed the certification project. Kudos !!