Mapping Naming Standards in Informatica

Blog Author


Published Date

8th October, 2020




  • Share this blog :

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:

Mapping Naming Standards in Informatica

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



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

Mapping Naming Standards in Informatica

--> 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:

Mapping Naming Standards in Informatica

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

  1. INS_Upd_date
  2. INS_Upd_User

Mapping Naming Standards in Informatica

Employee Dimension- SED Type 2:

Mapping Naming Standards in Informatica

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_I/p date_DATE


From expression transformation   expression


Mapping Naming Standards in Informatica

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:

Mapping Naming Standards in Informatica

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                                                                                                                                      IScreenshot_14


Similarly all ports this type                                                                                                 CScreenshot_14

Trim (RT rim


Full date                                                                                                                                    OScreenshot_14

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)


Update -flag                                                            O

Iff (not is null (transactionsequence-ID)


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                                                                                                            Screenshot_14      new-flag=’true’

Update-flag                                                                                                           Screenshot_14  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


  • 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


data type

      P             S             I     o     

Emp no



                                                             OUT PUT PORT 

EMP no



EMP name






Dept no



Hire date












Click on SQL query   , write SQL query in that window


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

Where empno =? empno?


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

About Author


Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills .

Related Blogs

Write For Us