ODI Incremental Load
How To Check-List
for Oracle Data Integrator 11g
ODI 11.1.1.7.0 with ODI BI Apps 11.1.1.8.1
Purpose : Add a new Dimension which will be also linked to an
Existing Fact and having it work in increment Load, considered
a Category 2 Customization of BI Apps.
Solution by Matthieu Lombard,
Oracle Senior Principal Consultant matthieu.lombard@oracle.com
Final document by Eric Tremblay eric.tremblay@data-warehouse.ca
AK = Alternate Key
PK = Primary Key
Steps :
1. Create indexes on in ODI for the custom tables _D, _F and _A.
These Indexes have to be created, they are a MANDATORY for ODI.
Also verify the Control and Flexfields Settings.
Primary Key Named WC_CONTACT_BU_D on ROW_ID
Control and Flexfields on WC_CONTACT_BU_D
Alternate Key Named WC_CONTACT_BU_D_P1 on ROW_ID
Control and Flexfields on WC_CONTACT_BU_D_P1
Alternate Key Named WC_CONTACT_BU_D_U1 on
INTEGRATION_ID, DATASOURCE_NUM_ID
Control and Flexfields on WC_CONTACT_BU_D_U1
2. Create Primary, alternate and foreign Keys on ODI Datastore (Models)
2.1 On all Custom Dimensions Staging
PK on INTEGRATION_ID, DATASOURCE_NUM_ID
The following Indexes are not needed but are created to improve performance
WC_CONTACT_BU_D_M1 Not Unique Index Selected Columns: X_CIBLE
WC_CONTACT_BU_D_M2 Not Unique Index Selected Columns: X_BU_ID
WC_CONTACT_BU_D_M3 Not Unique Index Selected Columns: ETL_PROC_WID
WC_CONTACT_BU_D_M4 Not Unique Index Selected Columns: DELETE_FLG
WC_CONTACT_BU_D_M5 Not Unique Index Selected Columns: CURRENT_FLG
WC_CONTACT_BU_D_M6 Not Unique Index Selected Columns: X_CONTACT_ID
2.2 On all Custom Facts Staging
PK on INTEGRATION_ID, DATASOURCE_ID
2.3 On all Custom Facts
PK on ROW_WID
AK on ROW_WID
AK on INTEGRATION_ID
DATASOURCE_ID
2.4 On all Custom Aggregates (PLP)
PK on ROW_WID
AK on ROW_WID
AK on INTEGRATION_ID
DATASOURCE_ID
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
;)
