DEV EPM

Hi all!

Today we will be talking about how we can export any object from ODI in a dynamic way. But first, why would we want to do that? One good example to do this is to figure out which ODI objects changed during a period range and export their xml to be stored in a code versioning repository. Another one could be to export all ODI scenarios with a certain marker, or from specific projects/folders in an automated way. Exporting Load Plans: Few people realize it, but there is no easy way in ODI to export several Load Plans at once (you may move the desired load plans to a folder and then export the entire folder with “Child components export” selected, but that would be considered cheating 🙂 ). Or maybe you just want to do it for the sake of doing something in a dynamic way (if you already read some of our posts, you already know that we like dynamic coding!).

First, let’s take a look on the OdiExportObject object from the Toolbox.

From Oracle Documentation:
Use this command to export an object from the current repository. This command reproduces the behavior of the export feature available in the user interface.

Great, that’s what we want: export any object (even Load Plans, that it’s not listed in the Oracle documentation) from the current repository. You may read about all its parameters here:

https://docs.oracle.com/middleware/11119/odi/develop/appendix_a.htm#ODIDG805

An example of a Load Plan export would look like this:

Two important parameters here: First we have the Object ID, that indicates which object you are about to export. This ID can be found by double clicking the ODI object and checking its Version tab:

The other parameter is the Classname. This one you may check on Oracle documentation but, as I said before, there may be some class names missing in the documentation, like SnpLoadPlan. So, the easiest way to check the correct Classname for any ODI object is to export it using the user interface, like below:

Go to the folder and open the xml file in a text editor. The Classname will be the Object Class right in the beginning of the xml file:

Ok, but it does not seem very dynamic, since we need to pass the object ID/Classname in order to export the correct object. So here we will use two of our favorite techniques to make it dynamic: Command on Source/Target and ODI metadata repository SQL. This is how it works: we will create an ODI procedure that will contain a SQL that queries the ODI metadata repository in the “Command on Source” tab (returning all the objects that we want to export) and OdiExportObject command on the “Command on Target” tab to actually export the objects.

Let’s begin with the “Command on Source” tab. First create a connection to you ODI work repository and define a Logical Schema to it. In the Command, add the SQL that will meet your requirement (in this example, retrieve all Load Plans that were created/modified since last week):

Our query needs to return three columns: the OBJECT_ID, OBJECT_CLASS and FILE_NAME. This information will be passed to the “Command on Target” to identify which objects needs to be exported.

Now, we need to add the OdiExportObject to the “Command on Target” tab and this is pretty simple to do. Every ODI object found in the Toolbox can be added to an ODI procedure and be called as “ODI Tools” Technology. If you are not sure how to do it, a good tip here is to add the ODI object that you want to add in the ODI procedure in an ODI package, set its parameters as you would normally do and click at the “Command” tab, like bellow:

Now just copy the command text and add it to your procedure in the “Command on Target” tab, selecting “ODI Tools” as its Technology:

As you can see, we have added three # variables here that will receive the information from the “Command on Source” tab. When you run this procedure, if 10 load plans were created/modified since last week, those will be exported to the EXPORT_DIR folder.

In this example we queried SNP_LOAD_PLAN table in order to get all load plan information. Luckily, the ODI table names are very similar to its Classname, so they should not be hard to find. Here is a list of the most common objects that you will likely export from ODI:

That’s it guys. I hope you liked it! See ya!