Mapping Naming Standards in Informatica

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

Column namea	Data type	Precision	Scale	Not null	Key type
Branch	Varchar 2	4
Account number	Varchar 2	10
Deport	Varchar 2	2
Client-flag	Varchar 2	3
Counter party-flag	Varchar 2	3
Full date	date
Emp-web-SSO-ID	Varchar 2	200
market	Varchar 2	50
Product-ISI	Varchar 2	200
Client-order-amount	number	20
Allocation-amount	number	20
Execution	number	20
Pending-amount	number	20

• 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.

Transformation attribute	Value
SQL query	In live query to join

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