ODI Nuggets

ODI is an ELT tool which is now widely used for data integration and is essentially used where ever oracle products are integrated. In this article I would not like to cover the basics of ODI, but will try to see some of the interesting and different approaches to development in ODI using different knowledge modules and utilities provided from within ODI itself.

For any interface that is developed to load data from table to table, When LKM was not selected for cases where staging area different from target, direct insert from source table to I$ table happens. This would succeed if the source and integration staging area are same schema. This is same as specifying staging area to be same as target.

Just a point to mention that for steps of creating c$ or I$ table, if the table already exist the ODI session doesn’t fail due to “Ignore Errors” being enabled for these steps.

As you will see data is directly getting inserted into I$ table from source table.

 

If we select the LKM, additional steps for creating the work table(C$ tables) and loading data to C$ table is carried out.

If distinct option is enabled for the LKM, distinct values are selected in the ODI step while loading the work table.

For target properties inside an interface, selecting values for option of IKM (target area) will disable that particular step to be executed. For eg. Setting coming option to false will cause the commit step to be not executed.

Disabling the flow control option by setting it to false as shown below will cause the check steps to be not executed.

As you can see in the image above, steps to create SNP_CHECK_TAB and E$ is not executed.

 

Detection strategy in ODI when selecting IKM as Oracle incremental update:

Detection strategy is used to export data that are actual change and discarding values from source or work table which are not changed at all based on each row comparison.

Options available are :

- MINUS: MINUS clause is used when populating flow table in order to exclude records, which identically exist in target.

- NOT_EXISTS:NOT EXISTS clause is used when populating flow table in order to exclude records, which identically exist in target.

- POST_FLOW: all records from source are loaded into flow table. After that an update statement is used to flag all rows in flow table, which identically exist in target.

- NONE: all records from source are loaded into flow table. All target records are updated even when target records is identical to flow table record.

Based on the detection strategy selected, ODI step for insert and update to target table is generated.

 

ODIFileWait utility:

This utility is used to check for arrival of file in a particular location at a specified interval of time specified as pollint interval till the timeout for the polling is reached. Setting the timeout to 0 will cause the utility to wait indifinitely. Upon arrival of files inside the source file location i.e, where the polling agent polls for the file and  providing a target location and filename, will cause all the files matching the source file mask pattern to be merged into one file named target filename and the file to be moved to target file location.

Keep first header-1 will cause only header of first file matching the mask pattern to be retained in the target merged file.

To handle any error situation like no file being reecived, nofile_error value of yes can be set and a proper actipn can be taken. Default value for this is ‘no’ which means no error is generated if the file is not received.

ODIStartScen Utility:

One of the utility to increase the parallelism is to use ODIStartScen which triggers scenario in a separate session. Using asynchronous mode for each of the ODIStartScen causes the session to be started parallely and ODIWaitforChildSessions will cause the parent session to wait for other child sessions spawned to execute and complete.