Informatica best practices

PDF

ltrim and rtrim

Use ltrim & rtrim while loading the records from the flat file to the staging table itself. This will avoid adding them in the Joins while going for subsequent Mappings

Foreign Key Constraint

In set mode, manipulate foreign key constraints so that rows are inserted faster while still preserving referential integrity.

Inline View Technique for faster loading

OWB automatically detects the common scenario where one or more custom transformations are applied to multiple, joined tables that are located in a remote database. First join the tables in the source database and then apply the transformation functions on the (much reduced) result set in the target database. OWB forces the join on the remote database by means of an inline view. On average this feature brings a fivefold performance increase.

Bulk Binding

In row mode, use bulk binding for reads and writes. Bulk binds improve the performance of PL/SQL statements. This feature significantly increases loading performance.

Partition Exchange Loading

Partition Exchange Loading enables parallel loading of data into partitioned tables, and avoids implicit index maintenance by loading a temporary table and then switching it with a partition. It is particularly efficient at loading data into a target table that already holds a large amount of data. This technique can be up to ten times faster than conventional partition loading.

Automated Bitmap index creation

Bitmap indexing increases performance by enabling star query transformation. Warehouse Builder automates the process of creating bitmap indexes to speed up the design of a performance index scheme.

Repository installation

It’s always a good practice to install the OWB repository and Run Time Audit tables in different schemas for better administration.

Hardware

The server hosting INFOMATICA gives good performance when minimum 128 MB RAM is used. The same is the case for the client side also. Refer to suggestions from vendor in this regard.

init.ora configuration

In init.ora file on the server, the value of max open cursor should be more than 300.

Projects

If the staging and target tables are residing in two different instances, then it’s better to have two different projects to handle this logic.

Set Based Inserts

Wherever set-based inserts are involved, direct path insert should be invoked using the /*+ APPEND */ optimizer hint. Direct-path insert is one of the most effective tools the Oracle ETL developer has available. By avoiding logging of the inserts, transformation runtimes can be reduced.

Trailing Null cols

Use trailing null cols while invoking the SQL*Loader otherwise it will give an error” Record Not Found” till the end of the file.
 
Home Informatica Informatica best practices

BI & DW - Interview questions | Insomnia articles | Job interview questions with answers