ODI Pundits - Oracle Data Integrator

This Blog has been in the cooking pot for long time almost half a decade, but a recent event triggered my attention to this post.

We have a very complex interface with a extensive logic in each mapped column, which uses some other columns which are already calculated.

Here are some of the requirements (These are not actual requirements but they are tuned down to ETL folks to make a point):

Requirements

1. Calculate the base price using a complex calculation encompassing 10 different columns

CASE
  WHEN SOME_CONDTION
UNITPRC * UNITCOST * CONV_FACTOR
WHEN SOME_OTHER_CONDITION
  ANOTHER_FORMULA ETC..

2. Use the above base price to calculate other 10 measures like royalty, etc..

Though these calculations are typically not done in BI, we needed to do them as a projections for booked sale.

Take 1:

One of the design approaches for this was to write the case statement for the base price directly into the mapping and when the base price is used in another mapping copy/paste the base price formula and apply additional logic on top of it.

Here was my first red flag, I am always wary of copy/pasting. If, I ever encounter a need to do that I evaluate my design.

Take 2:

We needed to reuse the base price in another calculations so, we took the most basic measure i.e. the base price and encapsulated it in a function, this function did not need any parameters

ufnc_baseprice
{CASE
  WHEN SOME_CONDTION
UNITPRC * UNITCOST * CONV_FACTOR
WHEN SOME_OTHER_CONDITION
  ANOTHER_FORMULA ETC..}

Note: When you use a function in mapping, ODI replaces the function call with the actual text of the function.

Map base_price to  unfc_baseprice()

Call the unfc_baseprice() when you need to use base_price measure

Example:

Map Royalty to unfc_baseprice() * royalty_rate

This lends to clean readable code and encapsulation of logic in one place.