DEV EPM

In the last post we changed the KM to create a Hyperion Planning option, making the KM a little more dynamic. But we also need to loop this interface by any number of applications. We have some ways to do that and this post will be describing the best way to do that, in my opinion.

Normally we learn to loop in ODI using a count variable and a check variable. In our case one variable is used to get the number of applications to be looped and another variable is used to identify that application number and transform it into the application name itself. The flow should be something like this:

We set a variable with the initial value, enter in the loop and increase the variable. The App_Cnt variable uses the value of the incremental variable. The number of applications and its names are inserted inside a parameter table. The same variable checks if we need to loop more. The App_Name variable uses the App_Cnt value to take the proper App name. Then we load the data into Hyperion Planning, putting the #APP_NAME variable into the KM APPLICATION option (created in the last post), making the application name dynamic. This leads to a lot of work and variables to maintain.

There’s an easier way to do the same thing using ODI procedures. In ODI procedures we have the source and target tabs concept. Basically the command in the target tab will be executed for each row that returns from the query in the source tab. Also we can pass the information that returns in the source tab query to the target tab command. That means we can simplify the above loop just doing this:

In the command on source tab we only need to create a query that will return all apps that we want to loop. The application name will be returned in the APP_NAME column.

For the target tab we need a little more work but it is also easy. First we need to create an ODI package containing the ODI interface used to load into Hyperion Planning. Then in the target tab we need to set it as “ODI Tools” technology and write an OdiStartScen command to call the interface package passing the name of the application (#APP_NAME from the source tab) as an input parameter. This procedure will call the interface scenario for each row that results from the source tab query. This will allow us to pass one application name at a time to the scenario, creating the same effect as the previous loop but in a much simpler way to maintain.

This method works for every kind of looping in ODI, especially with Hyperion Planning in a multiple application environment. In the next post we’ll take a deeper look on how we can use this together with the planning repository metadata to create dynamic interface loads.

This entry was posted on November 12, 2012 at 1:32 pm and is filed under ODI Architecture with tags Architecture, EPM, Essbase, Hyperion, 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.