ODI 12c: Renaming a Project

Hello all,

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.

  1. Projects have the same name
  2. Some variables have the same name
  3. 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?

  1. ODI will recognize and change codes of variables in mappings
  2. ODI will recognize and change codes of variables in packages (refresh, evaluate, set, declare variable components)
  3. ODI may recognize the variable usage in procedure options
  4. 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.

Project Creation

Project Creation

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.

MY_VAR

MY_PROC

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.

MY_MAP

MY_MAP

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.

MY_PACK

MY_PACK

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.

Rename

Rename

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)

Refresh MY_VAR

Refresh MY_VAR

Option MY_OPT of MY_PROC did not change.

MY_OPT

MY_OPT

Code in MY_PROC stays the same after change.

MY_PROC

MY_PROC

Column mapping in MY_MAP did not change also which is a disappointment for me.

MY_MAP

MY_MAP

After seeing the results let’s check how many guesses I’ve done right. I will copy/paste the list from above.

  1. ODI will recognize and change codes of variables in mappings DID NOT HAPPEN
  2. ODI will recognize and change codes of variables in packages (refresh, evaluate, set, declare variable components) HAPPENED
  3. ODI may recognize the variable usage in procedure options DID NOT HAPPEN
  4. 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 12.1.2.0.0 and results may vary depending on the version.

Version

Version

Thanks for reading and please do not hesitate to write your comments or ask questions in section below.

ODI 11g : Results of Deleting a User

One of my customers had a high turnover rate. Sometimes we ran into some ODI objects which are locked by a developer who has gone. That triggered a question. What if I delete an ODI user while it has locks on objects. So I gave it a try, and while trying this other cases also pop-up. I also tried them on my VM.

Case 1: Deleting a user with locked objects

My expectation was being unable to delete a user. I was expecting there should be a FK between user and lock tables in repository. But I was able to delete. Then I thought about two scenarios,
a) object will be unlocked
b) object will stay locked and when I try to visualize lock, ODI will crash since there will be nothing returned from lock query.

I was wrong. You may find the screenshot about how ODI responds to deleting a user with locks.

Locked Object by deleted user

Locked Object by deleted user

 

I can open object, ODI will pop-up “This object is locked by DEVELOPER. You can not edit” dialog. You can view everything in the object. But to begin using object again, you need supervisor to unlock it.

Case 2: Deleting a user who created some objects

In that case I was expecting to find “Created By:” text box in object’s version tab to be empty. But I was wrong again. ODI just worked fine. When I checked the repository, I saw that ODI holds usernames for these text box not the user ID’s.

Case 3: Deleting an online user

 

After first two cases and failures of my expectations, now I had nothing to expect. I would just try and see. I created a user, open another ODI instance, connect with new user and deleted the user. Then I tried to take some actions in ODI. Some I was able to, some I was not.

 

 

Create Scenario

Create Scenario

Things I was able to:

  • View operator logs
  • View topology definitions
  • View models
  • Create scenarios
View interface

Save an open interface

Things I was not able to :

  • View interfaces
  • Run interfaces or scenarios
  • Selective Reverse Engineering a model
  • Edit topology definitions.

 

 

Exevute scenario

Exevute scenario

View interface

View interface

 

 

If you have some other cases for me to try please write in the comments area.

Thanks for reading, dont forget to share & comment.

Test your SQLs online

Nowadays I am trying to answer questions about SQL, PL/SQL and Oracle on StackOverflow . You can see my profile card below :

 profile for Canburak Tümer at Stack Overflow, Q&A for professional and enthusiast programmers

Recently I am seeing a new web site link in answers, while I am navigating through as much questions as possible. That site called SQLFiddle where you can create an online database and try your codes.

Screenshot from web site

Screenshot from web site

As you can see above it is pretty simple, and it is really easy to use. There is different products and different versions for some products you can choose between. While I am writing this post, exact list was like below:

  • MySQL 5.5.30
  • MySQL 5.6.6 m9
  • MySQL 5.1.61
  • Oracle 11gR2
  • PostgreSQL 9.1.8
  • PostgreSQL 9.2.1
  • PostgreSQL 8.4.12
  • PostgreSQL 8.3.20
  • SQLite (WebSQL)
  • SQLite (SQL.js)
  • MS SQL Server 2008
  • MS SQL Server 2012

Jake Feasel, creator of SQLFiddle, explains why he built the site as :

I found JS Fiddle to be a great tool for answering javascript / jQuery questions, but I also found that there was nothing available that offered similar functionality for the SQL questions. So, that was my inspiration to build this site. Basically, I built this site as a tool for developers like me to be more effective in assisting other developers.

How to use

So let’s talk about how to use SQLFiddle,

First of all, you select the product you want to use from combo box on top-left corner, next to site logo. Then you should create your schema, in left editor you can create tables and insert data to your tables. When you are done with coding click Build Schema button to run your code. Now you have your tables and data. You can write your queries in the right editor and run them. Results will appear below editors.

Limitation 

There is a limitation with MySQL, that Jake Feasel explains in About page. On the right editor you can only use select if you are using MySQL, with other products feel free to use all DML operations.

Login

There are around 12 different log-in options like G+ and OpenID. When you login to site, you can see your Fiddle history, and your favorite fiddles. There is no other advantages of logging in yet.

That’s all for today. Do not hesitate to comment.

P.S. I am preparing new ODI post but I am really busy with my work these days. So follow my blog for updates.