Archive for ODI ETL

Tips and Tricks: Working with ODI Variables and Global Parameters

Posted in ODI Architecture with tags , , , , , , , , , , , , on September 7, 2015 by RZGiampaoli

Hi guys, today we’ll talk about some very simple but powerful technic that we always use in our integrations. Its joins two concepts together and make our lives a lot easier and our integration a lot more dynamic. We are talking about variables and the concept of “Global” parameters.

In our integrations we never, ever have anything hard coded. Every time you hard code something it will come back to bite you in the future that is for sure.

Then the first thing we do in a project is to create a table that we call ODI_PARAMETER. This table will contain all configuration and parameters that needs to be validated, hard coded and so one.

I like to create this table in our work schema (to make easier to use) and its look like this:

ODI_PARAMETER Table

The “SESSION_NM” is used to make the variable reusable in all scenarios that we want, meaning we’ll have only one variable for packages in the project or even for all projects (if we make this variable global in ODI).

How it works? First of all we need to get the “Session Name” for our “Scenario/Package”. Why did I say “Scenario/Package”? Because the result could change depending if you are running a Scenario or a Package. Let me explain this.

To get the “Session Name” in ODI we use an ODI Substitution method called “odiRef.getSession”. This method has other parameter that could return the Session ID, and other stuff but what matters for us is the “SESS_NAME” parameter, that will return the name of the session, the same thing that appears in the operator when we run any object in ODI.

Why I said object? Because if you run a variable the session name will be the variable name. If you run an interface, the session name will be the interface name, it goes to procedure, package and scenario, and that is why I separate the “Scenario/Package” because if we do not pay attention, the name of the package would be different of the name of the scenario, causing a problem when we run one of them.

Let me show how it works. First of all, we’ll create a Global ODI variable called SESSION_NM (could be whatever you want, I just like to call it like this) and we’ll put this code inside of it:

SESSION_NM Variable

After that, we will run this variable to see the results:

SESSION_NM Results

As we can see, the value of the variable was the name of the Variable itself. Now, let us create a package, put this variable inside it, and see what’s happens:

Package test 1

Here is what the interface looks like and above its results:

Package test 1 results

As we can see the result of the variable is the same as the session but in UPPER case since I create the variable like this. But why I did that? Let me create a scenario of this package to show you why:

Scenario Creation

And this is why I create in the variable getting the result and put in UPPER and why I said we need to worry about some peculiarity regarding Scenarios and Packages. When you create a scenario will have the name of the interface in UPPER case and also, NO SPACES. Now, if we run the just created scenario we will have:

Scenario results

Meaning, if we will use the result of this variable as a way to return data from a table, we’ll have a problem because it’ll not find the same result if you run the package or the scenario of that package.

The easiest way to resolve that is to have the name of the main scenario (the scenario that will contain all the other scenarios) with no spaces and no special characters (ODI also transform special characters like % in to _).

Doing that and we are good to continue as we can see below:

Package results

Now we have the same results if we run the package or the scenario.

Ok next let us create another variable to return the LOG_PATH, the path where we will store all our logs from our integrations. The code that we will use for this variable is:

Query ODI_PARAMETER

As we can see we are using the result of the “SESSION_NM” variable in this “LOG_PATH” variable. This is what’ll make this variable reusable in all “Packages/Scenarios/Procedures”. Let us insert a value inside our ODI_PARAMETER Table and run the Package to see the results:

Insert Test 1

Package 1 Results

Now let us create a new package with a different name, use the same variable as above, and insert a new line in our ODI_PARAMETER table for the new interface:

Package 2 results

See, same code, two different results. That means, 90% of the interfaces needs just to be duplicated and the parameters in ODI_PARAMETER needs to be inserted for the new interface and it is done. Also, we don’t need a ton of variables to get different results. And there is more.

The code of the variable also does not change that much. For a new variable, we just need to duplicate the LOG_PATH variable and change the PARAMETER_TYPE, PARAMETER_NAME and PARAMETER_VALUE to get any other information from the ODI_PARAMETER. That means, easier to maintain.

However, let us not stop here. In this example, we are getting the LOG_PATH for our logs in our integrations. Normally this path does not change from integration to integration. What changes is the name of the integration that we are logging right? In addition, with our SESSION_NM variable we could just put in our LOG_PATH variable the root of our LOG folder and then use like this:

#LOG_PATH\#SESSION_NM

This would make the LOG_PATH equal for all integration right. Nevertheless, in the way we create our variables we will need to insert one line for each integration in our ODI_PARAMETER table right.

Well, we just need to change a little bit our code in our variable to create the concept of GLOBAL parameters. How it will work:

First, we will delete the two lines we just created and then we will insert just one line in ODI_PARAMETER table:

Insert Global

Now we just need to change the code from our LOG_PATH variable to this:

Query ODI_PARAMETER global

And here we go:

Global results

We have one global parameter that can be used for all integrations. And the cool thing is that the code above tests if we have a parameter for the actual SESSION_NM and if not it’ll get the parameter from the GLOBAL parameter, meaning if any integration needs a special LOG_PATH or something you just need to insert a new line in the ODI_PARAMETER to get the value just for that integration:

Global results exceptions

This will guarantee that you never ever needs to touch your code again to test or change anything that the business ask you for.

As I said, is a simple but very powerful tool to use.

Hope you guys enjoy and see you soon.

Advertisements

ODI needs your help: Petition for EPM KMs support

Posted in EPM, ODI, ODI 12c, Oracle, Petition with tags , , , , on October 27, 2014 by radk00

Hi all,

Today’s post is a request for all of our readers. As you know, ODI 12c does not support EPM KMs. Although it seems that Oracle has announced that they will support the Hyperion Essbase KMs sometime in the future, no such acknowledgement has been made for the Hyperion Planning and Hyperion Financial Management Knowledge modules and it all indicates that this functionality will migrate to FDMEE application. This has created a lot of noise in the EPM community, especially in big clients that have invested a large amount of time and money to build ODI ETL processes around their EPM applications.

Having this in mind, Chris Rothermel (thank you so much Chris for all your effort on this, the ODI/EPM community really appreciate this!) has created an online petition requesting Oracle to rethink its strategy and add the EPM KMs support in ODI 12c.

DEVEPM supports this petition and asks you all to sign it!

Link for the petition site here: Change.org

Some of you may think that signing this petition is going to make you go “against Oracle” in some way but that’s totally the opposite. The ODI/EPM community is raising this petition just because we love ODI and all Oracles’ EPM tools and we really want to see them shine! We live and breathe ODI/EPM every single day and we may tell you for sure that ODI is the best ETL tool in the market today and should bring all technologies under its wings. See what the ODI team is doing for Golden Gate/Big Data integrations…. It’s just amazing! We just want the same for all EPM tools!

Is this petition going to have any effect on Oracles’ thought? I don’t know, but they need to know at least that there is an ODI/EPM community out there that cares about it and that will be left alone if they decide to abandon the EPM KMs support. Another suggestion for Oracle is to make the EPM KMs open source at least, so the EPM community would modify/upgrade them as needed when a new version of EPM tools hits the market. DEVEPM even offers to be a major contributor to these changes if they go open source 🙂

Thanks guys, we are counting on you to make this petition work!

See ya!

Edited on 01/15/2015 ——————————————————————————————

Victory!!!!! It seems that Oracle has heard the community!!! Take a look on what Chris has added to the petition site:

“12 de jan de 2015 — Hooray! The Hyperion Planning Knowledge Modules will likely be supported in ODI 12c. That means both Essbase and Planning will be supported in the next release of ODI. That’s a change for the better. No official word on this yet. The HFM Knowledge Modules have a different story for ODI version 12C. Since the HFM API in version 11.1.2.4 will be Java there is a big architecture change and the ODI KMs for HFM will not be available or supported by Oracle for the next release.

Also note that Planning has a new set of API’s with 11.1.2.4 so look for improvements in both Planning and ODI.”

Thanks for all those that contributed somehow to this cause! Lets wait for the official Oracle announcement!