Informatica Power Center performance – Concurrent Workflow Execution

=========================================================================== Informatica Power Center performance – Concurrent Workflow Execution

What is concurrent work flow?

A concurrent workflow is a workflow that can run as multiple instances concurrently. What is workflow instance?

A workflow instance is a representation of a workflow. How to configure concurrent workflow?

1) Allow concurrent workflows with the same instance name:

Configure one workflow instance to run multiple times concurrently. Each instance has the same source, target, and variables parameters.

Eg: Create a workflow that reads data from a message queue that determines the source data and targets. You can run the instance multiple times concurrently and pass different connection parameters to the workflow instances from the message queue.

2) Configure unique workflow instances to run concurrently:

Define each workflow instance name and configure a workflow parameter file for the instance. You can define different sources, targets, and variables in the parameter file.

Eg: Configure workflow instances to run a workflow with different sources and targets. For example, your organization receives sales data from three divisions. You create a workflow that reads the sales data and writes it to the database. You configure three instances of the workflow. Each instance has a different workflow parameter file that defines which sales file to process. You can run all instances of the workflow concurrently.

How concurrent workflow Works?

A concurrent workflow group’s logical sessions and tasks together, like a sequential workflow, but runs all the tasks at one time.

Advantages of Concurrent workflow?

This can reduce the load times into the warehouse, taking advantage of hardware platforms’ Symmetric Multi-Processing (SMP) architecture.

LOAD SCENARIO:

Source table records count: 150,622,276

===========================================================================

Posted 13th April 2011 by Prafull Dangore

0 Add a comment 80.

81.

13

Informatica Performance Improvement Tips

We often come across situations where Data Transformation Manager (DTM) takes more time to read from Source or when writing in to a Target. Following standards/guidelines can improve the overall performance.

 Use Source Qualifier if the Source tables reside in the same schema

 Make use of Source Qualifer “Filter” Properties if the Source type is Relational.

 If the subsequent sessions are doing lookup on the same table, use persistent cache in the first session. Data remains in the Cache and available for the subsequent session for usage.

 Use flags as integer, as the integer comparison is faster than the string comparison.  Use tables with lesser number of records as master table for joins.

 While reading from Flat files, define the appropriate data type instead of reading as String and converting.

 Have all Ports that are required connected to Subsequent Transformations else check whether we can remove these ports  Suppress ORDER BY using the ‘–‘ at the end of the query in Lookup Transformations

 Minimize the number of Update strategies.

 Group by simple columns in transformations like Aggregate, Source Qualifier  Use Router transformation in place of multiple Filter transformations.

 Turn off the Verbose Logging while moving the mappings to UAT/Production environment.  For large volume of data drop index before loading and recreate indexes after load.

 For large of volume of records Use Bulk load Increase the commit interval to a higher value large volume of data  Set ‘Commit on Target’ in the sessions

===========================================================================

Posted 13th April 2011 by Prafull Dangore 0 Add a comment

82.

13

What is Pushdown Optimization and things to consider

The process of pushing transformation logic to the source or target database by Informatica Integration service is known as Pushdown Optimization. When a session is configured to run for Pushdown Optimization, the Integration Service translates the transformation logic into SQL queries and sends the SQL queries to the database. The Source or Target Database executes the SQL queries to process the transformations.

How does Pushdown Optimization (PO) Works?

The Integration Service generates SQL statements when native database driver is used. In case of ODBC drivers, the Integration Service cannot detect the database type and generates ANSI SQL. The Integration Service can usually push more transformation logic to a database if a native driver is used, instead of an ODBC driver.

For any SQL Override, Integration service creates a view (PM_*) in the database while executing the session task and drops the view after the task gets complete. Similarly it also create sequences (PM_*) in the database.

Database schema (SQ Connection, LKP connection), should have the Create View / Create Sequence Privilege, else the session will fail.

Few Benefits in using PO

 There is no memory or disk space required to manage the cache in the Informatica server for Aggregator, Lookup, Sorter and Joiner Transformation, as the transformation logic is pushed to database.

 SQL Generated by Informatica Integration service can be viewed before running the session through Optimizer viewer, making easier to debug.

 When inserting into Targets, Integration Service do row by row processing using bind variable (only soft parse – only processing time, no parsing time). But In case of Pushdown Optimization, the statement will be executed once.

Without Using Pushdown optimization:

INSERT INTO EMPLOYEES(ID_EMPLOYEE, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT,

MANAGER_ID,MANAGER_NAME,

DEPARTMENT_ID) VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13) –executes 7012352 times With Using Pushdown optimization

INSERT INTO EMPLOYEES(ID_EMPLOYEE, EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, MANAGER_NAME, DEPARTMENT_ID) SELECT CAST(PM_SJEAIJTJRNWT45X3OO5ZZLJYJRY.NEXTVAL AS NUMBER(15, 2)), EMPLOYEES_SRC.EMPLOYEE_ID, EMPLOYEES_SRC.FIRST_NAME, EMPLOYEES_SRC.LAST_NAME, CAST((EMPLOYEES_SRC.EMAIL || ‘@gmail.com’) AS VARCHAR2(25)), EMPLOYEES_SRC.PHONE_NUMBER, CAST(EMPLOYEES_SRC.HIRE_DATE AS date), EMPLOYEES_SRC.JOB_ID, EMPLOYEES_SRC.SALARY, EMPLOYEES_SRC.COMMISSION_PCT, EMPLOYEES_SRC.MANAGER_ID, NULL, EMPLOYEES_SRC.DEPARTMENT_ID FROM (EMPLOYEES_SRC LEFT OUTER JOIN EMPLOYEES PM_Alkp_emp_mgr_1 ON (PM_Alkp_emp_mgr_1.EMPLOYEE_ID = EMPLOYEES_SRC.MANAGER_ID)) WHERE ((EMPLOYEES_SRC.MANAGER_ID = (SELECT PM_Alkp_emp_mgr_1.EMPLOYEE_ID FROM EMPLOYEES PM_Alkp_emp_mgr_1 WHERE (PM_Alkp_emp_mgr_1.EMPLOYEE_ID = EMPLOYEES_SRC.MANAGER_ID))) OR (0=0)) –

executes 1 time

Things to note when using PO

There are cases where the Integration Service and Pushdown Optimization can produce different result sets for the same transformation logic. This can happen during data type conversion, handling null values, case sensitivity, sequence generation, and sorting of data.

The database and Integration Service produce different output when the following settings and conversions are different:  Nulls treated as the highest or lowest value: While sorting the data, the Integration Service can treat null values as

lowest, but database treats null values as the highest value in the sort order.

 SYSDATE built-in variable: Built-in Variable SYSDATE in the Integration Service returns the current date and time for the node running the service process. However, in the database, the SYSDATE returns the current date and time for the machine hosting the database. If the time zone of the machine hosting the database is not the same as the time zone of the machine running the Integration Service process, the results can vary.

 Date Conversion: The Integration Service converts all dates before pushing transformations to the database and if the format is not supported by the database, the session fails.

 Logging: When the Integration Service pushes transformation logic to the database, it cannot trace all the events that occur inside the database server. The statistics the Integration Service can trace depend on the type of pushdown optimization. When the Integration Service runs a session configured for full pushdown optimization and an error occurs, the database handles the errors. When the database handles errors, the Integration Service does not write reject rows to the reject file.

=================================================================================

Posted 13th April 2011 by Prafull Dangore 0 Add a comment

83.

13

Informatica OPB table which have gives source table and the

mappings and folders using an sql query

SQL query

select OPB_SUBJECT.SUBJ_NAME, OPB_MAPPING.MAPPING_NAME,OPB_SRC.source_name from opb_mapping, opb_subject, opb_src, opb_widget_inst

where opb_subject.SUBJ_ID = opb_mapping.SUBJECT_ID

and OPB_MAPPING.MAPPING_ID = OPB_WIDGET_INST.MAPPING_ID and OPB_WIDGET_Inst.WIDGET_ID = OPB_SRC.SRC_ID

and OPB_widget_inst.widget_type=1;

Posted 13th April 2011 by Prafull Dangore 0 Add a comment 84.

85.

28

How to remove/trim special characters in flatfile source field? -