• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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:

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

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

Mapping Naming Standards in Informatica

–> Copy the New Flag ports to the Exp

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

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 following ports

Emp_insert_DATE

EMP_I/p date_DATE

INS_Upd_User

From expression transformation   expression

LOADING ACCOUNT DIMENSION: SED TYPE 2

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
Interested in mastering Informatica Training? 
Enroll now for FREE demo on Informatica Training Online.
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

D-branch

Similarly all ports this type                                                                                                 CScreenshot_14

Trim (RT rim

(C-branch)

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)

‘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                                                                                                            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 generate

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

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

 Screenshot_14

                                                             OUT PUT PORT 

EMP no

Number

 Screenshot_14

EMP name

Varchar2

 Screenshot_14

Job

Vcarchar2

 Screenshot_14

Dept no

Number

               Screenshot_14          

Hire date

date

 Screenshot_14

Sal

number

 Screenshot_14

Comm.

number

 Screenshot_14

MGR

number

 Screenshot_14

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

Summary
Review Date
Reviewed Item
Mapping Naming Standards in Informatica
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

2 Responses on Mapping Naming Standards in Informatica"

  1. pravallika says:

    This is a good piece of info.

    Kindly also provide some real time scenarios, proj based.

    Thanks
    Pravallika

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.