DEV EPM

When we start to develop integrations with ODI we normally go through the “normal” development way.  Generally it leads us to a situation where we spend a lot of time developing a huge amount of interfaces to get around on more complex problems.

This is especially true if Hyperion Planning makes part of the project. ODI structure is based in Models and it loses a lot of flexibility when we start to use them in a lot of similar interfaces.

Let’s imagine the follow scenario:

A company needs to create Metadata integrations for a planning application. In our example, the planning applications have 4 plan types including a Plan, Workforce, Capex and Manufacturing cube. A common set of dimension for those applications should be something like this:

In this example we’ll need to create at least 11 interfaces to load all metadata into Planning. This is ok, but imagine if we have to load metadata into more than one application (let’s say five similar applications) that changes depending on the region (Americas, Japan, Emea, Worldwide and an analytic app with a small set of entities from each region). This will turn our 11 interfaces into 55 interfaces. That means a lot of work and a lot of trouble when any maintenance is needed in those interfaces.

What can we do to simplify this work? In this post we’ll see the first step to make use of ODI with Planning more efficient.

The first thing that we need to understand is the ODI concept of KM (Knowledge model). A Knowledge model is a set of instructions that will take the information from what exists in the source and target data stores of an ODI interface and construct a command (in our case a SQL command) based in those models. If we take a look in the planning KM we’ll find the follow steps.

For this post we’ll tweak only the “Prepare for loading” step. This step connects into one single planning Application. We can change it a little and make this connection more dynamic. Let’s take a look in the content of this step:

In the first part of this KM (after java imports) we can see the target planning connection properties. If we take a look on the “application” part it takes the information that exists in the target data source (the planning model that we are using in the ODI interface) and goes to ODI topology to retrieve the information about the name of the application.

This means that to load Function dimension for all the 5 planning applications (remember that all the applications are almost the same and they are splitted by the support geography of the Entity) we’ll need 5 interfaces and each one with a model for the respective application. This is because the application and the database are took from the DEST_CATALOG and the DEST_SCHEMA topology information.

Luckily the KM is pretty flexible and we can change it to make it more dynamic. In this case we need 2 simple changes to divide by 5 the amount of interfaces created.

First we need to create 2 options in the KM. Right click in the IKM SQL to Hyperion Planning and create a new Option.

When the Option screen opens we need to name the option as something meaningful likes Application. Select the type Value and in the default value we can set an ODI variable that will return the application name.

After this step we need to open the IKM and change the line “application” from “<%=snpRef.getInfo(“DEST_CATALOG”)%>” to “<%=snpRef.getOption(“APPLICATION”)%>”

Save it and it’s ready for testing. This change will make possible to loop that dimension for all the applications using a single interface to load.

How it works: As the structure of the dimension is the same for all applications, we only need to make one interface and create a “parent” ODI scenario that calls this interface passing one application at a time as an input parameter.

This is the first of a set of tips that I will post in this blog. I wish this to be useful for you guys as it is for me and be prepared: in the next post we’ll start to talk about more efficient ways to loop in ODI, call scenarios and other interesting things until we reach the modeless development architecture. This removes the need of models from ODI and makes only one interface to be generic enough to load any number of applications for any dimension.

This entry was posted on October 15, 2012 at 1:53 pm and is filed under ODI Architecture with tags EPM, Hyperion, Integration, Loading Metadata, Loading Planning, Loop Application, ODI, ODI Architecture, ODI Expert, 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.