Informatica Row Error Logging

Informatica Inbuilt Error Logging feature can be leveraged to implement Row Error logging in a central location.  When a row error occurs, the Integration service logs error information which can be used to determine the cause and source of the error.

These errors can be logged either in a relational table or in a flat file. When the error logging is enabled, the Integration service creates the error table or error log file the first time when it runs the session.  If the error table or error log file exists already, then the error data will be appended.

Following are the activities that need to be performed to implement the Informatica Row Error Logging:

1.  In the “Config object” tab of “Error Handling” option , set the “Error Log type” attribute to  “Relational database” or “Flat File”.  By Default error logging is disabled.

2.  SET Stop On Errors = 1

3.  If the  Error Log Type is set to “Relational”, specify the Database connection & Table Name Prefix

Following are the tables which will be created by Integration service and which will be populated as and when the error occurs.

PMERR_DATA

Stores data and metadata about a transformation row error and its corresponding source row.

PMERR_MSG

Stores metadata about an error and the error message.

PMERR_SESS

Stores metadata about the session.

PMERR_TRANS

Stores metadata about the source and transformation ports, such as name and datatype, when a transformation error occurs.

4.  If the  Error Log Type is set to “Flatfile”, specify the “Error log file directory” and “Error log file name”

Database Error Messages and the Error messages that Integration service writes to Bad File/ Reject file can also be captured and stored in the Error log tables / Flat files.

Following are the few database error messages which will be logged in the Error Log Tables / Flat files.

Error Messages

Cannot Insert the value NULL into column ‘<<Column name>>’, table ‘<<Table_name>>’

Violation of PRIMARY KEY constraint ‘<<Primary key constraint name>>’

Violation of UNIQUE KEY constraint ‘<<Unique Key Constraint>>’

Cannot Insert Duplicate key in object ‘<<Table_name>>’

Row Error Logging Implementation

Advantages

Since the Informatica Inbuilt feature is leveraged, the Error log information would be very accurate with very minimal development effort.

Pitfall

Enabling Error logging will have an impact to performance, since the integration service processes one row at a time instead of block of rows.

Transaction Control Transformation

Transaction Control Transformation

Power Center lets you control commit and roll back transactions based on a set of rows that pass through a Transaction Control transformation. A transaction is the set of rows bound by commit or roll back rows. You can define a transaction based on a varying number of input rows. You might want to define transactions based on a group of rows ordered on a common key, such as employee ID or order entry date.

In Power Center, you define transaction control at the following levels:

• Within a mapping: Within a mapping, you use the Transaction Control transformation to define a transaction. You define transactions using an expression in a Transaction Control transformation. Based on the return value of the expression, you can choose to commit, roll back, or continue without any transaction changes.
• Within a session: When you configure a session, you configure it for user-defined commit. You can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

When you run the session, the Integration Service evaluates the expression for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the target or targets. When the Integration Service evaluates a roll back row, it rolls back all rows in the transaction from the target or targets. If the mapping has a flat file target you can generate an output file each time the Integration Service starts a new transaction. You can dynamically name each target flat file.

Properties Tab:

On the Properties tab, you can configure the following properties:

• Transaction control expression
• Tracing level

Enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression:

IIF (condition, value1, value2) :

The expression contains values that represent actions the Integration Service performs based on the return value of the condition. The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row.

When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction. Use the following built-in variables in the Expression Editor when you create a transaction control expression:

• TC_CONTINUE_TRANSACTION: The Integration Service does not perform any transaction change for this row. This is the default value of the expression.
• TC_COMMIT_BEFORE: The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
• TC_COMMIT_AFTER: The Integration Service writes the current row to the target, commits the transaction, and begins a new transaction. The current row is in the committed transaction.
• TC_ROLLBACK_BEFORE: The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
• TC_ROLLBACK_AFTER: The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.

Mapping Guidelines and Validation:

Use the following rules and guidelines when you create a mapping with a Transaction Control transformation:

• If the mapping includes an XML target, and you choose to append or create a new document on commit, the input groups must receive data from the same transaction control point.
• Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.
• You must connect each target instance to a Transaction Control transformation.
• You can connect multiple targets to a single Transaction Control transformation.
• You can connect only one effective Transaction Control transformation to a target.
• You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.
• If you use a dynamic Lookup transformation and a Transaction Control transformation in the same mapping, a rolled-back transaction might result in unsynchronized target data.
• A Transaction Control transformation may be effective for one target and ineffective for another target. If each target is connected to an effective Transaction Control transformation, the mapping is valid.
• Either all targets or none of the targets in the mapping should be connected to an effective Transaction Control transformation.

Example:

Step 1: Create a mapping with the following transformations

i)                    Source Definition /  Source Qualifier

ii)                   Transaction Control

iii)                 Target Definition

Note : Follow the steps to create the Transaction Control Transformation

• In the Mapping Designer, click Transformation > Create. Select the Transaction Control transformation.
• Enter a name for the transformation.
• Enter a description for the transformation.
• Click Create.
• Click Done.
• Drag the ports into the transformation.
• Open the Edit Transformations dialog box, and select the Ports tab.

Select the Properties tab. Enter the transaction control expression that defines the commit and roll back behavior.

Go to the Properties tab and click on the down arrow to get in to the expression editor window. Later go to the Variables tab and Type IIF(KEY_COL=123,) select the below things from the built in functions.

IIF (KEY_COL=123,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)

• Connect all the columns from the transformation to the target table and save the mapping.

Click OK.

DB procedure for TYPE1 and TYPE2 mappings (SCD)

Type 2

====== 

CREATE OR REPLACE PROCEDURE HR.TYPE2(EMP_ID INTEGER,E_NAME CHAR,SAL REAL)

IS

EMP_NO INTEGER;

C_FLAG CHAR;

REC_CNT INTEGER;

BEGIN

SELECT 'Y' INTO C_FLAG FROM DUAL;

SELECT COUNT(*) INTO REC_CNT FROM TYPE1 WHERE EMPID=EMP_ID;

IF REC_CNT >=2 THEN

DELETE FROM TYPE1 WHERE EMPID=EMPID AND CURRENTFLAG='N';

COMMIT;

END IF;

SELECT EMPID INTO EMP_NO FROM TYPE1 WHERE EMPID=EMP_ID;

IF EMP_NO IS NOT NULL THEN

UPDATE TYPE1 SET CURRENTFLAG='N'

WHERE EMPID=EMP_ID;

END IF;

IF EMP_NO IS NOT NULL THEN

INSERT INTO TYPE1 VALUES(EMP_ID,E_NAME,SAL,C_FLAG);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO TYPE1 VALUES(EMP_ID,E_NAME,SAL,C_FLAG);

END

TYPE2;

/

 ~~~~~~

Type1

===== 

CREATE OR REPLACE PROCEDURE HR.TYPE_HIST(EMP_ID INTEGER,E_NAME VARCHAR,SAL REAL)

IS

EMP_NO INTEGER;

BEGIN

SELECT EMPID INTO EMP_NO FROM TYPE1 WHERE EMPID=EMP_ID;

IF EMP_NO IS NOT NULL THEN

UPDATE TYPE1 SET EMPID=EMP_ID,ENAME=E_NAME,SALARY=SAL WHERE EMPID=EMP_ID;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO TYPE1 VALUES(EMP_ID,E_NAME,SAL);

END

TYPE_HIST;

/

Need to calculate 12 months aggregated data for a given date.

SOURCE
DEPARTMENT_NAME	ANALYST_OFFICE	ANALYST_REGION	MONTH_YEAR	SUM(CNT)
Corporate Ratings	Hong Kong	Asia Pacific	01/01/2009 00:00:00	130
Corporate Ratings	Hong Kong	Asia Pacific	02/01/2009 00:00:00	107
Corporate Ratings	Hong Kong	Asia Pacific	03/01/2009 00:00:00	170
Corporate Ratings	Hong Kong	Asia Pacific	04/01/2009 00:00:00	237
Corporate Ratings	Hong Kong	Asia Pacific	05/01/2009 00:00:00	131
Corporate Ratings	Hong Kong	Asia Pacific	06/01/2009 00:00:00	213
Corporate Ratings	Hong Kong	Asia Pacific	07/01/2009 00:00:00	99
Corporate Ratings	Hong Kong	Asia Pacific	08/01/2009 00:00:00	81
Corporate Ratings	Hong Kong	Asia Pacific	09/01/2009 00:00:00	269
Corporate Ratings	Hong Kong	Asia Pacific	10/01/2009 00:00:00	326
Corporate Ratings	Hong Kong	Asia Pacific	11/01/2009 00:00:00	243
Corporate Ratings	Hong Kong	Asia Pacific	12/01/2009 00:00:00	195
Corporate Ratings	Hong Kong	Asia Pacific	01/01/2010 00:00:00	24
Corporate Ratings	Hong Kong	Asia Pacific	02/01/2010 00:00:00	60
Corporate Ratings	Hong Kong	Asia Pacific	03/01/2010 00:00:00	89
Corporate Ratings	Hong Kong	Asia Pacific	04/01/2010 00:00:00	95
Corporate Ratings	Hong Kong	Asia Pacific	05/01/2010 00:00:00	84
Corporate Ratings	Hong Kong	Asia Pacific	06/01/2010 00:00:00	64
Corporate Ratings	Hong Kong	Asia Pacific	07/01/2010 00:00:00	58
Corporate Ratings	Hong Kong	Asia Pacific	08/01/2010 00:00:00	186
Corporate Ratings	Hong Kong	Asia Pacific	09/01/2010 00:00:00	51
Corporate Ratings	Hong Kong	Asia Pacific	10/01/2010 00:00:00	58
Corporate Ratings	Hong Kong	Asia Pacific	11/01/2010 00:00:00	476
Corporate Ratings	Hong Kong	Asia Pacific	12/01/2010 00:00:00	305
Corporate Ratings	Hong Kong	Asia Pacific	01/01/2011 00:00:00	29
Corporate Ratings	Hong Kong	Asia Pacific	02/01/2011 00:00:00	40
Corporate Ratings	Hong Kong	Asia Pacific	03/01/2011 00:00:00	87
Corporate Ratings	Hong Kong	Asia Pacific	04/01/2011 00:00:00	192
Corporate Ratings	Hong Kong	Asia Pacific	05/01/2011 00:00:00	14
Corporate Ratings	Hong Kong	Asia Pacific	06/01/2011 00:00:00	5
Corporate Ratings	Hong Kong	Asia Pacific	08/01/2011 00:00:00	23

TARGET
DEPARTMENT_NAME	ANALYST_OFFICE	ANALYST_REGION	MONTH_YEAR	SUM(CNT)	SUM(ROLLING_12_MONTHS_CNT)
Corporate Ratings	Hong Kong	Asia Pacific	01/01/2009 00:00:00	130	130
Corporate Ratings	Hong Kong	Asia Pacific	02/01/2009 00:00:00	107	237
Corporate Ratings	Hong Kong	Asia Pacific	03/01/2009 00:00:00	170	407
Corporate Ratings	Hong Kong	Asia Pacific	04/01/2009 00:00:00	237	644
Corporate Ratings	Hong Kong	Asia Pacific	05/01/2009 00:00:00	131	775
Corporate Ratings	Hong Kong	Asia Pacific	06/01/2009 00:00:00	213	988
Corporate Ratings	Hong Kong	Asia Pacific	07/01/2009 00:00:00	99	1,087
Corporate Ratings	Hong Kong	Asia Pacific	08/01/2009 00:00:00	81	1,168
Corporate Ratings	Hong Kong	Asia Pacific	09/01/2009 00:00:00	269	1,437
Corporate Ratings	Hong Kong	Asia Pacific	10/01/2009 00:00:00	326	1,763
Corporate Ratings	Hong Kong	Asia Pacific	11/01/2009 00:00:00	243	2,006
Corporate Ratings	Hong Kong	Asia Pacific	12/01/2009 00:00:00	195	2,201
Corporate Ratings	Hong Kong	Asia Pacific	01/01/2010 00:00:00	24	2,095
Corporate Ratings	Hong Kong	Asia Pacific	02/01/2010 00:00:00	60	2,048
Corporate Ratings	Hong Kong	Asia Pacific	03/01/2010 00:00:00	89	1,967
Corporate Ratings	Hong Kong	Asia Pacific	04/01/2010 00:00:00	95	1,825
Corporate Ratings	Hong Kong	Asia Pacific	05/01/2010 00:00:00	84	1,778
Corporate Ratings	Hong Kong	Asia Pacific	06/01/2010 00:00:00	64	1,629
Corporate Ratings	Hong Kong	Asia Pacific	07/01/2010 00:00:00	58	1,588
Corporate Ratings	Hong Kong	Asia Pacific	08/01/2010 00:00:00	186	1,693
Corporate Ratings	Hong Kong	Asia Pacific	09/01/2010 00:00:00	51	1,475
Corporate Ratings	Hong Kong	Asia Pacific	10/01/2010 00:00:00	58	1,207
Corporate Ratings	Hong Kong	Asia Pacific	11/01/2010 00:00:00	476	1,440
Corporate Ratings	Hong Kong	Asia Pacific	12/01/2010 00:00:00	305	1,550
Corporate Ratings	Hong Kong	Asia Pacific	01/01/2011 00:00:00	29	1,555
Corporate Ratings	Hong Kong	Asia Pacific	02/01/2011 00:00:00	40	1,535
Corporate Ratings	Hong Kong	Asia Pacific	03/01/2011 00:00:00	87	1,533
Corporate Ratings	Hong Kong	Asia Pacific	04/01/2011 00:00:00	192	1,630
Corporate Ratings	Hong Kong	Asia Pacific	05/01/2011 00:00:00	14	1,560
Corporate Ratings	Hong Kong	Asia Pacific	06/01/2011 00:00:00	5	1,501
Corporate Ratings	Hong Kong	Asia Pacific	08/01/2011 00:00:00	23	1,466

Solution:

select ETR_RA_AGG_FACT_KEY,department_name,ANALYST_OFFICE,ANALYST_REGION,month_year,sum(cnt),sum(ROLLING_12_MONTHS_CNT)

from (

select ETR_RA_AGG_FACT_KEY,DEPARTMENT_NAME,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION,sum(NO_OF_RATING_ACTIONS) as cnt,

sum(NO_OF_RATING_ACTIONS)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),1) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

 nvl(lag(sum(NO_OF_RATING_ACTIONS),2) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),3) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),4) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),5) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),6) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),7) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),8) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),9) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),10) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)+

nvl(lag(sum(NO_OF_RATING_ACTIONS),11) over (partition by department_name,ANALYST_OFFICE,ANALYST_REGION order by department_name,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION ),0)

as Rolling_12_months_cnt  from ETR_RATING_actions_AGG_FACT

group by ETR_RA_AGG_FACT_KEY,DEPARTMENT_NAME,MONTH_YEAR,ANALYST_OFFICE,ANALYST_REGION

)

where month_year between to_date('01/01/2009','dd/mm/yyyy') AND TRUNC(SYSDATE)

group by ETR_RA_AGG_FACT_KEY,department_name,month_year,ANALYST_OFFICE,ANALYST_REGION