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

Source Tables: Create tables as mentioned below.

CUSTOMERS – Master Table
COUNTRIES – Master Table
PRODUCTS – Master Table
PROMOTIONS – Master Table
CHANNELS – Master Table
SALES – Transaction Table

Target Tables : And we consider the following table as target tables for building data warehouse:

DIM_CUSTOMER – Dimension Table
DIM_COUNTRIES – Dimension Table
DIM_PRODUCTS – Dimension Table
DIM_PROMOTIONS – Dimension Table
DIM_CHANNELS – Dimension Table
DIM_TIMES – Dimension Table
FACT_SALES – Fact Table

This OLTP table is loaded into the data warehouse using PowerCenter mapping by using the following mappings:

Problem Solution

So this is the problem statement. Based on the above source/target details you need to create mappings to load the Dimensions & Fact tables to create the Datamart. This is one of the certification project provided by Edureka. Are you ready to start this project ? if yes…

1. Download source/Target database script & mapping specification documents from HERE.
2. First create & load the source tables using the script provided.
3. Create & load the target tables using the script provided.
4. Review & understand the mapping specification document before you start the development.
5. Everything is ready to start the development. Good luck !!!

I will give you the mapping & transformation details here. If you want the screen shots Please do send an e-mail to kvtinformatica@gmail.com.

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

If you need the complete problem solution documents, Please do send an e-mail to kvtinformatica@gmail.com.

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