- Back to Home »
- Use Case for "User Functions" in ODI.
Posted by :
Arjun Lagisetty
Tuesday, September 6, 2016
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):
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.
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.
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
Map Royalty to unfc_baseprice() * royalty_rate
This lends to clean readable code and encapsulation of logic in one place.
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 parametersufnc_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.
how to write a case statement in odi expression editor( what is the syntax?)
ReplyDelete