Customizing Stored Lookups and Adding Indexes

Learn about how this information applies to all categories of customization in Oracle BI Applications.

How Dimension Keys are Looked Up and Resolved

By default, dimension key resolution is performed by the Oracle Business Analytics Warehouse in the load mapping. The load interface uses prepackaged, reusable lookup transformations to provide pre-packaged dimension key resolution.

There are two commonly used methods for resolving dimension keys. The first method, which is the primary method used, is to perform a lookup for the dimension key. The second method is to supply the dimension key directly into the fact load mapping.

If the dimension key is not provided to the Load Interface through database joins, the load mapping performs the lookup in the dimension table. The load mapping does this using prepackaged Lookup Interfaces. To look up a dimension key, the Load Interface uses the INTEGRATION_ID, the DATASOURCE_NUM_ID, and the Lookup date, which are described in the following table:

Port	Description
INTEGRATION ID	Uniquely identifies the dimension entity within its source system. Formed from the transaction in the Source Adapter of the fact table.
DATASOURCE_NUM_ID	Unique identifier of the source system instance.
Lookup Date	The primary date of the transaction; for example, receipt date, sales date, and so on.

If Type II slowly changing dimensions are enabled, the load mapping uses the unique effective dates for each update of the dimension records. When a dimension key is looked up, it uses the fact's primary or canonical date to resolve the appropriate dimension key. The effective date range gives the effective period for the dimension record. The same entity can have multiple records in the dimension table with different effective periods due to Type II slowly changing dimensions. This effective date range is used to exactly identify a record in its dimension, representing the information in a historically accurate manner.

There are four columns needed for the load interface lookup: INTEGRATION ID, DATASOURCE_NUM_ID, and Lookup Date (EFFECTIVE_FROM_DT and EFFECTIVE_TO_DATE). The lookup outputs the ROW_WID (the dimension's primary key) to the corresponding fact table's WID column (the fact tables foreign key).

Adding an Index to an Existing Fact or Dimension Table

Dimension and Fact Tables in the Oracle Business Analytics Warehouse use an ETL Index for Unique/Binary Tree index, and Query Index for Non-Unique/Bit Map Index.

To add an index to an existing fact or dimension table:

In ODI Designer, display the Models view, and expand the Oracle BI Applications folder.
Expand the Fact or Dimension node as appropriate.
Expand the table in which you want to create the index.
Right-click on the Constraints node, and select Insert Key to display the Key: New dialog.
Display the Description tab.
Select the Alternate Key option, and update the name of the Index in the Name field.
Display the Column tab.
Select the column on which you want to create the index.
Display the FlexFields tab.
Use the settings to specify the index type, as follows:
- For Query type indexes (the default), define the index as an Alternate Key for unique indexes and as Not Unique Index for non-unique indexes.
- For ETL type indexes, clear the check box for the INDEX_TYPE parameter and set the value to ETL. In addition, set the value of the IS_BITMAP parameter to N and define the index as an Alternate Key for unique indexes and as Not Unique Index for non unique indexes.
Save the changes.