Stage to star mappings
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 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.
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
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 |
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)
EMP- SQL:
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
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
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 in the market.
