New approach for Customization of Out of the Box ODI Interaces

Dt: 14 Sep 2017

Recently I have had the opportunity to work on a OBIA implementation & extension project after a long time.

A few things have changed since  had last seen the OBIA environment.One of the notable changes was the Oracle recommendation for customizing the OOB objects,

When OBIA 11 had launched with ODI for the first time ,Oracle had tried to pick up all features of Informatica and tried to replicate it like fir like in ODI .Even the design principles remained similar to what Informatica would use.

One such design principle was for customizing OOTB objects.You would copy the existing OOTB  mapping folder to the Custom folders. Modify the interface & then create a scenario with name like CUSTOM_SDE_XXXXX or CUSOM_SILOS_XXXXX.

This design meant that you had to follow up the changes with replacing the OOTB scenario  name with Custom Scenario name & regenerate the load plan every time you make a modification to the OOTB interfaces.

To avoid the frequency of load plan generations ..Oracle now recommends that you just change the interface in custom folder and generate a scenario with the same OOTB scenario name but a higher scenario number. This helps you to retain the original load plan & run it as is after modifications to OOTB mappings.

Although this could turn out to be a nightmare for production support teams as thy wont be able to figure out at first look whether the OOTB object failed or the custom one .But  it seems Oracle has ruled in the favor of developers this time .

How wold you like your SCD Type 2 Dimension

Dt: 15 March 2017

Most of you must have designed an ETL extract for a slowly changing type 2 dimension .
However how do you  manage the first load as it will populate all the existing active dimensions.

Today while we were discussing the design we came across an interesting dilemma.What should be the date in the EFFECTIVE FROM DATE columns for the first record (version) of a dimension loaded into the warehouse table.

Typically most ETL tools provide a mapping which will set the EFFECTIVE FROM DATE for the first version  of the dimension as the SYSDATE when the dimension table was first loaded.

Although this is not incorrect but when you read the data in the dimension table it seems as if the first known version of the dimension only existed from the date when you first loaded the warehouse table.

A different approach to this is to update the EFFECTIVE FROM DATE column to an early date( 01/01/1900 ) just to denote that this is the first known active record for this table.

This is what  I would prefer.Let me know your thoughts on this .