Working as a consultant has its own challenges which makes me try things at home, which drives me to play more with Oracle and ODI and learn new things. Today a new challenge appeared in front of me. I am working in an upgrade project, where the upgrade from ODI 10g to ODI 12c is already completed and my task is to test the codes in ODI 12c to see if they are still good. (There are some problems but they will be topic of another post.)
So today, our client admitted that they do not have only ODI 10g to upgrade, they also have an ODI 12c repository other than the upgraded one. And they want to merge two 12c repositories together. But there are some problems.
- Projects have the same name
- Some variables have the same name
- Some custom KMs have the same name but different codes.
So we thought of some methods and their possible outcomes. One option was to change the Project Name (and Code off course) but we could not decide how will ODI react to this change. So I decided to give it a try in my training VM tonight.
What I was expecting?
- ODI will recognize and change codes of variables in mappings
- ODI will recognize and change codes of variables in packages (refresh, evaluate, set, declare variable components)
- ODI may recognize the variable usage in procedure options
- ODI will not recognize and change variables in procedures
Let’s see what happened together:
First I created a new project and I created a variable called MY_VAR and put some refresh code in it, which does not matter since I am not going to run any of the objects for this demo.
Second I created MY_PROC with one task TASK 1 and one option MY_OPT. Task has one line of code, which is the MY_VAR itself, just to see if it will change.
Then I created a mapping called MY_MAP, put two random datastores in it. Made a link between them and mapped a column with MY_VAR.
Then I created a package MY_PACK which includes a Refresh Variable, MY_PROC with MY_OPT set to MY_VAR, and finally MY_MAP.
Finally I proceed to change Project Name and Project Code. ODI warns user in this step, that errors in objects may occur especially with project variables.
After I accepted the changing the name and the code, let’s see what happened. Refresh Variable in the pack is changed as I expected. You can see its new path in the lower right corner of the image. (click on image to see it bigger)
Option MY_OPT of MY_PROC did not change.
Code in MY_PROC stays the same after change.
Column mapping in MY_MAP did not change also which is a disappointment for me.
After seeing the results let’s check how many guesses I’ve done right. I will copy/paste the list from above.
- ODI will recognize and change codes of variables in mappings DID NOT HAPPEN
- ODI will recognize and change codes of variables in packages (refresh, evaluate, set, declare variable components) HAPPENED
- ODI may recognize the variable usage in procedure options DID NOT HAPPEN
- ODI will not recognize and change variables in procedures HAPPENED
So I achieved a score of 2 out of 4 in my guesses. And unfortunately ODI achieved to detect only 1 of the four scenarios. So be cautious about changing the name and the code of a project in ODI, actually be careful about changing the code of any othe objects too. (such as models, contexts etc.)
This demo has been done using ODI 22.214.171.124.0 and results may vary depending on the version.
Thanks for reading and please do not hesitate to write your comments or ask questions in section below.