Archive for Planning

Newer Entries »

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 3 (Executing a Maxl)

Posted in ODI Architecture with tags , , , , , , , , , , , , on November 30, 2012 by RZGiampaoli

Sometimes in a project that involves an Essbase database we need to execute commands on that to get or set some information as substitution variables, execute calculation scripts and others. Normally we can set a calculation script into an option inside an interface and it’ll be executed in the beginning or in the end of its execution.

0

But sometimes we need a more robust way to execute a series of commands in Essbase. If we need to calculate some data in essbase we could need to set some substitution variables and execute more than one calculation script to calculate the right area.

Well, the easy way to do that is again the use of ODI procedures. Combining an ODI Tool inside a procedure and some OS commands we can easily create a powerful execution center to command essbase from ODI.

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 can be used for any Maxl command available as:

  • Execute Calculations, with some steps we can set the POV using the alter variables command and then execute a calculation already created in EAS or we could send the script store in a parameter table to essbase avoiding the creation of new calculations scripts in EAS;
  • Alter database, any type of changes as load, stop, enable or disable connections and so on;
  • Studio Deploy, this is very helpful for control a metadata interface and use studio to deploy it and maintain a drill thought ASO cube ;
  • Clear Database (For ASO). Awesome way to clear data in an ASO database before insert new data;
  • Get Statistics. Very good way to track some database information or to verify if something is true and with this result execute or not some action;
  • Backups. With some steps only one procedure can backup a Essbase Application and zip otl, clc, and other files as well;

With this approach and a simple interface it’s possible to generate text files from essbase and load these text files to a table for further analysis. Here is one example of an interface to load database statistics:

Create Essbase Statistics Procedure

This procedure  will generate a file with the statistics of a cube.  Then we need only to create an interface to load this file to a table:

Load Essbase Statistics Interface

And we’ll have now an excellent way to track the cube growth, search for some anomaly before a cube crash, plan the memory allocation for the cubes based in the use of each cube and so.

Leave a comment »

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 2 (Procedure Loop)

Posted in ODI Architecture with tags , , , , , , , , , , , , , on November 12, 2012 by RZGiampaoli

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.

6 Comments »

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 1 (Application Loop)

Posted in ODI Architecture with tags , , , , , , , , , , on October 15, 2012 by RZGiampaoli

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.

5 Comments »

ODI Modeless presentation

Posted in ODI Architecture with tags , , , , , on October 10, 2012 by RZGiampaoli

Hi all, please stay with a presentation about the new ODI concept to create model-less Hyperion planning integrations.

This presentation will cover:

The default EPM development architecture for Oracle Hyperion Planning;

The new architecture concept for EPM and Oracle Hyperion Planning

The advantages between the default and the new EPM architecture

http://youtu.be/2CMeM2fapOU

Greetings

1 Comment »

Newer Entries »