Posted by : Arjun Lagisetty Monday, April 10, 2017

How add and maintain indexes in OBIA using ODI?
Monday, April 10, 2017
2:05 PM
If you look at the ODI code base for OBIA (all 11.X versions) before every SDE mapping step or SIL mapping steps. We will see two sets of steps.
  1. Initialize steps (Before the map is run)
    1. Indexes are dropped
  2. Finalize steps (After the map is run)
    1. Indexes are created
    2. Stats are gathered

When we add a new index to a table in DW schema or when we delete an index, we do not have to add CREATE INDEX, DROP INDEX or GATHER STATS steps to the ODI code base. There are hooks in the code to take care of these steps irrespective of the number of indexes. Index maintenance steps can be achieved by a couple of simple steps in Designer without having to write a single line of code.

Let's start by understanding what are the steps involved in adding an index to a table.
Step 1: Create indexes on the database
Step 2: Reverse engineer the model (typically named Oracle BI Applications)
Customized Reverse-Engineering using OBIA specific RKM
Step 3: Modify options on the index.
Step 4: Execute EXEC_LOAD_INDEX_METADATA scenario under BI Apps Project > Components > DW >  Oracle > Load Index Metadata > Load Index Metadata > EXEC_LOAD_INDEX_METADATA Version 001 (to load W_ETL_ALL_INDEXES table). If you are running domain load this step can be skipped.

There are three main changes to be made to the index (Constraint object).
  1. Choose "Key or Index Type" under "Description" Tab
    1. Not Unique Index: If the index is not unique
    2. Primary Key: If the Index is Primary key
    3. Alternate key: If the index is unique but not the primary key.
  2. Choose "OBI Index Type" under "Flexfields" tab
  3. Choose "OBI Bitmap Index" under "Flexfields" tab

Most indexes can be categorized into following types. Let's evaluate what are the options to choose for each type of index as mentioned in Step 3

  • Primary key index (BTREE)
    • "Key or Index Type": Primary Key
    • "OBI Index Type": ETL
    • "OBI Bitmap Index": N

  • Alternate key index (BTREE)
    • "Key or Index Type": Alternate Key
    • "OBI Index Type": ETL
    • "OBI Bitmap Index": N

  • Non-Unique Index (BTREE )
    • "Key or Index Type": Not Unique Index
    • "OBI Index Type": ETL
    • "OBI Bitmap Index": N

  • Non-Unique Index (BITMAP) 
    • "Key or Index Type": Not Unique Index
    • "OBI Index Type": QUERY
    • "OBI Bitmap Index": Y

{ 1 comments ... read them below or add one }

  1. The Jade's Infa2ODI is fine tuned for OBIA as well as custom mappings conversion with minimal defects and manual work. The tool also has the "Bulk" conversion facility. To know more about Informatica to ODI migration visit


Popular Post


Blog Archive

Copyright © ODI Pundits - Oracle Data Integrator - Maintained by Arjun Lagisetty