DEV EPM

In a previous post we saw a very powerful feature in ODI: the command on source and command on target tabs inside the procedures. This allows us to create “loop effects” and can be used even with different technologies (remembering that the source technology must always be Oracle).

But sometimes we may find situations that two tabs are not enough and we wished to have a “third command tab” or something like that.  Here is one example:

This example procedure will call an ESSMSH OS command to load an Essbase database. It will retrieve the user name, password and server name from the command on source schema information using the following:

<%=odiRef.getInfo(“SRC_USER_NAME”)%>
odiRef.getInfo(“SRC_PASS”)
<%=odiRef.getInfo(“SRC_DSERV_NAME”)%>

This will work fine as long as you have one app. Imagine now that you have multiple applications in the same server and you want to load all of them with one procedure using the target/source tabs technique. It would look like this:

Great! Now we have an Oracle command on source that will retrieve all Essbase cubes that we want to load. Those cubes will be passed as parameters to “Command on Target” tab to be executed. But unfortunately this command will not work. The reason is simple: our source tab changes from Hyperion Essbase to Oracle, the ODI commands related to user, password and server name will get the wrong information to connect in Essbase.

If only we had a “third command tab” to get the Essbase information… And we can have it using a technique for these kinds of situation. We use java variables to get connection information and use it in our command. Let’s create another step in our procedure:

This step will get user name, password and server name from Essbase and stores it in Java variables. With this information in Java variables we can change our procedure to the following:

Done! We were able to mimic a “Third command tab” situation where we get the Essbase connection using Java in one tab, use it on an OS command in another tab and we will loop it through any number of rows that returns in the Oracle tab. It gives you a lot of flexibility. This technique can be used in a lot of other situations. It only depends on your creativity. Java is a great language to be used with ODI and can give you that extra flexibility that you may need in your code.

This entry was posted on November 30, 2012 at 2:50 pm and is filed under ODI Architecture with tags Architecture, EPM, Essbase, Integration, Loading Metadata, Loading Planning, Loop Application, ODI, ODI Architecture, ODI Expert, odi interface, ODI Modelless, Planning. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.