Mapping Naming Standards:

Stage to star mappings

1. M_Account_Dim (stage to star)
2. M_Date_Dim (stage to star)
3. M_Employee_Dim (stage to star)
4. M_Market_Dim (stage to star)
5. M_Product_Dim (stage to star)
6. M_Transaction_Details_Fact (stage to star)

Date Dimension Loading:

create the transformation type expression and sequence generator

In expression transformation create the following Expression

Port Name                                                                          Expression

Day_of_week ------> To_Decimal (To_Char(Full_Date , 'D')

Date_Number_In_Month ------> To_Decimal (To_Char(Full_Date , 'DD')

Date_Number_In_Year ------> To_Decimal (To_Char(Full_Date , 'DDD')

Week_Number_In_Month ---> To_Decimal (To_Char(Full_Date , 'W')

Week_Number_In_Year ---> To_Decimal (To_Char(Full_Date , 'WW')

Month_Number -----> To_Decimal (To_Char(Full_Date , 'mm')

Month_Name -----> To_Char (Full_Date , 'MON')

Quarter_Number -----> To_Decimal (To_Char(Full_Date , 'Q')

Year -----> To_Decimal (To_Char(Full_Date , 'yyyy')

Run_Week_Number -----> To_Decimal (To_Char(Full_Date , 'W')

Market Dimension - SedType1:

In expression transformation create a expression for Null handling for all column except INS_UPD_User

Port Name                                                          Expression

Flag_Exception                                               IFF(IS NULL(     ) OR

Double click on router T/R click on Group Port

Port Name

Exp_Correct

Exp_Exception

In this Router T/R , copy the Exp_Exception Ports to Exp_Expression T/R and Exp T/R ports connect to the Excp_Target

• From Router T/R copy the Exp_correct ports to the Expression T/R

From transformation select the LOOK UP transformation

From transformation select the Expression

Port Name                                          Expression

Insert                                                    Iff (ISNULL( True, False )

Update                                                 Iff (NOT is NULL( True, False)

From Transformation select the Router T/R

Double click on Router T/R click on group tab

Port Name

New Flag

Update Flag

From Transformation select the Exp and sequence generator

--> Copy the New Flag ports to the Exp

--> Copy the Update Flag ports to the expression and connect Target

Informatica Interview Questions & Answers

Product - SED Type1:

Note: Write a expression for NULL handling for all the ports except folowing ports.

1. INS_Upd_date
2. INS_Upd_User

Employee Dimension- SED Type 2:

Create a mapping with the name m_Employee_din

Drop the source defination T_Employee

Drop the target defination as a three Instances

Dm_T_Employee_DIm (New record to insert)

Dm_T_Employee_DIm 1 (Update record to insert)

Dm_T_Employee_DIm 2 (Update record to Update,enddate)

Drop the Exception table

                Create the transformation type expression to handle the null values create the I/R type router to pass the correct date to one exp to pass the expression data to another expression I/R

NOTE: define the null handling on all the ports except the following ports

Emp_insert_DATE

EMP_I/p date_DATE

INS_Upd_User

From expression transformation   expression

LOADING ACCOUNT DIMENSION: SED TYPE 2

Note:      In expression T/R write an expression to handle the nulls on all ports except rp-details-last-modified date, rp- details-lost-modified by

From look up transformation click on condition

From properties tab BSR:

From exp T/R

New-flag                                                                   Iff(ISNULL(account- key ), ‘true’, ‘false’

Update-flag                                                               Iff(not is null (account – key) AND

Is null(end-date)And

(Client name ! =src-client) ‘True’                                                                                                                                                     ‘false’

FACT table loading:

There are three sources to load the data into fact table

1. Client order
2. Client allocation
3. Client execution

Creation of source definition:

From tool menu select source analyzer from source menu select create enter the name stg-transaction-detail-fact

Select database type oracle click create and done

Double click the source definition selects the columns tab

• Creating a mapping with the name m-transaction – detail- fact
•  Drop the source and target definition
•  Create the transformation type
•   Double click the SQ transformation selects the properties tab

If you want to enrich your career and become a professional in Informatica, then visit Tekslate - a global online training platform: "Informatica Training" This course will help you to achieve excellence in this domain.

Clicks apply, click ok

Double click on expression T/R select the ports tab UN check the output ports (except client order amount, allocation amount, execution amount)

Create output port to make trim operation

Branch                                                                                                                                      I

D-branch

Similarly all ports this type                                                                                                 C

Trim (RT rim

(C-branch)

Full date                                                                                                                                    O

Trane (full date)

(To (-char) (full date)

Create transformation type expression exp- date- conversion

From expression transformation copy the ports to the expression transformation

Double click on express T/R select the ports tab

Uncheck the output port for a port name 0 – full – date

Create an output port with the name full- date with the following expression

To- date (to-char (full-date), (“mm-dd-yyyy”), “mm -dd-yyyy”)

Click applies and clicks ok

Create a look up T/R which perform a look up on data-dim market-dim, account –dim, product –dim, employee-dim

From look up transformation copy the following ports to expression transformation

Date- key, product-key, account-key, market-key, employee-key

From expression transformation (from source) copy the 13 ports to the expression T/R

Create the transformation type lookup which perform a look on target table (transaction- detail- fact)

From the expression T/R copy the following ports to the look up transformation (Branch, account-no, deport, client-flag, counterparty-flag, full-date, market-code, product-ISIN, EMP-web SSC-ID)

Create the transformation type expression

From source copy the entire source to expression transformation

From look up T/R copy transaction-sequence-ID

Double click on expression T/R, select the port tab

New -flag                                                                O

Iff (is null (transaction-sequence-ID)

‘True’,‘false’

Update -flag                                                            O

Iff (not is null (transactionsequence-ID)

‘True’,‘false’

Click applies, click ok

Create the transformation type router T/R

From the Expression T/R copy all ports to the router T/R

Double click on the router select the group tab

New-flag                                                                                                                  new-flag=’true’

Update-flag                                                                                                             update=’true’

Click applies and clicks ok

Define a new record follow:

Create the transformation type Expression, Update strategy, Sequence generates

Define update follow:

Create the T/R type expression update strategy

SQL Transformation:

                               Create a target table which bellows structure

SQL> create table EMP –SQL

(

EMP no                   Number (4)

EMP name             Varchar2 (15)

Job                           Varchar2 (15)

Dept no                  Number (4)

Hire date               Date

Sal                          Dumber (7)

Comm                   Number (5)

MGR                      Number (4)

• Go to tool menu and click on target designer import the target table

Introduction to Informatica

EMP- SQL:

• Create a mapping with the name m-cmp-SQL
• Drag and drop the source tmp and target (EMP-SQL) to the mapping designer
• Go to transformation menu click on create select the T/R type SQL T/R

Click on create select Query-mode     DB type is oracle click ok

Double click on the SQL transformation click on SQL ports

Port                                                     INPUT PORTS

name	data type	P             S             I     o
Emp no	Number

                                                             OUT PUT PORT 

EMP no	Number
EMP name	Varchar2
Job	Vcarchar2
Dept no	Number
Hire date	date
Sal	number
Comm.	number
MGR	number

Click on SQL query   , write SQL query in that window

Bellow

Select Emp number, E name, job, dept no, hire date, sal, comm, MGR,

Where empno =? empno?

Parameter

Click ok

Click applies and clicks ok

Select emp no from SQ and link to the SQL T/R input ports

Select the all the output column from SQL T/R link to the Target

In session level provide the relation connection information (BSR-Reader) to SQL T/R

For indepth understanding of Informatica, click on

• Rank Transformation in Informatica
• Aggregator Transformation in Informatica
• Expression Transformation in Informatica
• Lookup Transformation in Informatica
• ETL Project Architecture
• Interview Questions