Mapping Naming Standards:
Stage to star mappings
- M_Account_Dim (stage to star)
- M_Date_Dim (stage to star)
- M_Employee_Dim (stage to star)
- M_Market_Dim (stage to star)
- M_Product_Dim (stage to star)
- 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
Product - SED Type1:
Note: Write a expression for NULL handling for all the ports except folowing ports.
- INS_Upd_date
- 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 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
- Client order
- Client allocation
- 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
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 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 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 | |
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