Oracle Business Intelligence Application Architect
☎ +91 9994883085
Don't miss

Monday, 9 September 2013

Source Pre-load and Source Post-Load in Informatica


By on 03:01:00




We can write the stored procedures to carry some action before the Source Qualifier executes and after the load is done on the Dimension table.

Example: Creating an Index on the Source table before the load and dropping the same index after the load is done on the target.

In our example, we have created two proceduresin the source database SCOTT – first one to create an index on the EMPNAME column of the source table EMP, and the second to drop the same index.

CREATE OR REPLACE PROCEDURE P_IN_EMP_NAME
AS
BEGIN
   EXECUTE IMMEDIATE 'CREATE INDEX IND_EN ON EMP (ENAME)';
END;

CREATE OR REPLACE PROCEDURE P_D_IND_EMP_ENAME
 AS
 BEGIN
     EXECUTE IMMEDIATE 'DROP INDEX IND_EN';
 END;

Note: Do not execute these procedures. We’ll do this task in Informatica by calling these procedures one after the other.

Steps:

Define a mapping   M_SRC_PRE_POST_LOAD.

Drag Source (EMP),Target  DIM_EMP to the mapping designer.

Create a SP Transformation T_SP_PRESOURCE_LOAD.

Import the stored procedure P_IN_EMP_NAME

Source Pre-load and Source Post-Load in Informatica
Source Pre-load and Source Post-Load in Informatica



Edit the transformation. There are no ports.

In the Properties set the Connection Information to MEEN_SRC.

Give the Stored Procedure Type as Source Preload.

Assign the call text property as P_IN_EMP_NAME ()

Source Pre-load and Source Post-Load in Informatica
Source Pre-load and Source Post-Load in Informatica
Note: Give the execution order if there are more than one SPs in the Pre Source load.



Similarly Create a SP Transformation T_SP_POSTSOURCE_LOAD

Import the procedure P_D_IND_EMP_ENAME.

Source Pre-load and Source Post-Load in Informatica
Source Pre-load and Source Post-Load in Informatica

Edit the Transformation.

No ports.

Set the Properties:

Connection Information   MEEN_SRC

Call Text P_D_IND_EMP_ENAME ()

Stored Procedure Type Source Post Load

Source Pre-load and Source Post-Load in Informatica
Source Pre-load and Source Post-Load in Informatica


Project the required ports from SQ to the TGT DIM_EMP.

Source Pre-load and Source Post-Load in Informatica
Source Pre-load and Source Post-Load in Informatica
Save the repository.

Define Workflow.

Assign the connections (SRC,TGT,Transfn.)

Source Pre-load and Source Post-Load in Informatica
Source Pre-load and Source Post-Load in Informatica

Save the repository.

Start the work flow.

Observe the Session Log.

0 comments:

Post a Comment

Blog Archive

Labels

1z0-482 Dumps (2) 1Z0-525 Dumps (1) BI Apps Installation (1) BI Publisher Interview Questions (1) BICS (2) Business Intelligence (1) DATA WAREHOUSE ADMINISTRATOR CONSOLE (18) Data Warehousing (48) E-BIZ R12 (1) E-BIZ R12 INSTALLATION (1) Essbase (1) hmailserver (1) Hyperion (1) Hyperion Essbase (2) Hyperion Essbase Interview Questions (1) Hyperion financial management (1) Informatica (163) Informatica Installation 9.6.1 (2) Informatica Interview Question (47) Informatica Online Training (1) informatica scenarios questions (1) Informatica Training (1) Informatica Training Chennai (1) JAVA (1) linux (1) Normalization in Oracle (1) OBIA 11.1.1.7.1 (1) OBIA 7.9.6.3 (3) OBIA Installation (33) OBIEE (2) OBIEE 10G (8) OBIEE 11G (138) OBIEE 11g dumps (2) OBIEE 11g Interview Questions (1) OBIEE 12c (20) OBIEE 12c Architecture (1) OBIEE 12C TRAINING (1) OBIEE certification (2) OBIEE Interview Question (1) OBIEE Publisher Interview Questions (1) OBIEE TRAINING CHENNAI (1) OBIEE TRAINING ONLINE (1) ODI (Oracle data integrator) (2) odi 11g certification (1) ODI dumps (2) Oracle Administrator (20) Oracle Business Intelligence Application (3) Oracle Data Integrator (2) Oracle Data Integrator Interview questions for experience (1) Oracle Database (3) Oracle Database 12c Installation steps (3) Oracle Database Installation in Linux 6 (2) Oracle E-Business R 12.3 (1) Oracle Hyperion Planning and Budgeting Questionnaire (2) oracle Joins (1) Power BI (5) RCU INSTALLATION (1) scorecard in obiee 11g (1) SQL (3) SQL Interview Questions (1) sql server 2008 (2) Sql server installation (1) WinSCP (1)