Archive for March, 2016

“Where to Start? ETL Developer Career” ODTUG Webinar

Posted in Career, ETL, ODI, ODTUG, Oracle, Webinar with tags , , , , , on March 30, 2016 by radk00

Hi everyone!

Today’s post is an invitation for the ODUTG Webinar “Where to start? ETL Developer Career” that will be presented by me on Tuesday, April 19, 2016 12:00 PM – 1:00 PM EDT. This webinar is the first one of a series of career webinars that ODTUG will be providing in its new Career Track Community that will be released very soon. I cannot give you too much details about this new community yet, but I may say that it will be aimed to provide career content for those young professionals that were just graduated or those that wants to give a shift in their career but they are not sure on how to star on them.

Here is the webinar description:

Are you a young professional who just got out of college and unsure which career path to follow? Are you are thinking about changing your career to something completely new and looking for options? Either way, this webinar is the right one for you. It’s the first in a series that the new ODTUG Career Track Community will bring you to show what Oracle careers look like and where/how to start with them.

During this webinar, we will talk about what an ETL developer career looks like, what the expectations are, challenges, rewards, and which steps are needed to be successful. We will discuss a wide range of topics, such as tools used on the job, certification paths, the importance of social media, user groups, and more. This webinar will be presented by Rodrigo Radtke de Souza, who has been working in the Oracle ETL world for quite some time now and has achieved great accomplishments as an ETL developer, such as Oracle ACE nomination, frequent Kscope speaker, ODTUG Leadership Program participant, and a successful career at Dell.

Here is the link to register to the webinar:

“Where to Start? ETL Developer Career” ODTUG Webinar

Also, ODTUG has several great webinars that are completely free for you. Take a look on the link below! If for some reason you are unable to attend the webinars at the specified date/time, you may become and ODTUG member which will grant you access to all previous recorded webinars, plus all Kscope presentation, technical journals, discount on Kscope and SP conferences and much more. If you are unsure if it is worthy or not to become an ODTUG member, read our post on how ODTUG changed our lives.

ODTUG webinars page

That’s it folks! See you next time!

Dynamically exporting objects from ODI

Posted in 11.1.1.9.0, ACE, ODI, ODI Architecture, Tips and Tricks with tags , on March 1, 2016 by radk00

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.

1

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:

2

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:

3

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:

4

5

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:

6

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):

7

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:

8

9

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

10

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:

Capture

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