Hi all! Quick post today. DEVEPM recorded another OTN 2MTT. Now we are talking about “Incredible ODI Tips to Work with Hyperion Tools (Kscope16 Preview)”. In this 2MTT we give an idea of what will be one of our session at Kscope 16 that will happen on June 27, 2016, 4:30 PM – 5:30 PM. Take a look and join us at Kscope 16!
Thank you guys and see you soon 🙂
Hi guys how are you doing? We are very happy and proud to announce that our article “Dynamic Integrations for Multiple Hyperion Planning Applications” was approved for KScope 13! We’ll be there to talk about one of our preferred subject areas: integration of Hyperion Planning applications. We’ll be very pleased if you guys could show up in our presentation. It’ll be excellent to meet everyone there to talk about EPM and other stuffs.
Above is the link with more information about our article.
This article is all about what we have being writing in our blog series “10 Important Things to Improve ODI Integrations with Hyperion Planning” and some other surprises that we’ll talk at the presentation. You guys can expect a lot of technical information, a new way to view and work with Hyperion Planning integrations plus some real facts about what has happened after a successful implementation of this approach.
Thank you very much everybody and we’ll be waiting for you in Kscope 13.
Kscope 13 Speaker
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.
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.
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:
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:
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:
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.
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.
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.
More to life… jason’s hyperion blog Glenn Schwartzberg’s Essbase Blog Hooked On Hyperion
DEV EPM 