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 all, how are you doing? We are very happy to announce that our presentation “No more unknown members! Smart data load validation for Hyperion Planning using ODI” was approved for KScope 15! We’ll be there to talk about one of our preferred subject areas: How to improve EPM tools potential using ODI. We’ll be very pleased if you guys show up in our presentation. It’ll be great to meet everyone there and talk about EPM and other cool stuffs! Here is our presentation’s abstract:
“Usually, ODI data load interfaces for Essbase are simple and fast to be developed. But, depending on the data source quality, those interfaces may become a performance challenge. Essbase demands that all POV members in which we are trying to insert data to exist in the Essbase outline and when this is not true, Essbase switches its load method from Block Mode to Cell Mode. When this situation happens, one data load that would take only five minutes to complete may take several hours, degrading the Hyperion environment performance. Join us in this session to discover how we solved this problem in a dynamic way for any number of Hyperion Planning applications only using ODI data constraints and Hyperion Planning metadata repository to validate all POV members that will be used in the data load, guaranteeing the best performance and data quality on the Hyperion Planning environment.”
Kscope is the largest EPM conference in the world and it will be held on Hollywood, Florida on June this year. It will feature more than 300 technical sessions, five symposiums, deep dive sessions, and hands on labs over the course of five days. Got interested? If you sign up by March 25th you’ll take advantage of the Kscope early bird rates, then don’t waste more time and let’s be part of the greatest EPM event in the world.
Thank you very much everybody and we’ll be waiting for you at Kscope 15!
Today we will talk about something very important when we work with Hyperion Planning: its repository.
Today I can say that, if it was not for the Planning repository, 10% of the solutions/issues that I had in my clients should not have been implemented/fixed and 50% the solution/issues should take 10 times more to be implemented/fixed.
Well, as we are talking about integration in this post I will focus on this for now but I promises that I will talk about the repository itself in another time, focusing in Planning development, security and maintenance.
Let us start. First, what is the planning application repository? Basically it is used for manage all existing metadata in an planning application, which means, we can find all forms information, security, dimensions, settings, workflows and other stuffs of an application there and the best part is that we’ll find this in a relational table (normally oracle).
This allow us to easily get any information from any applications and, in an integration perspective, creating smarter integrations.
I said that because normally when we create an ODI interface to load metadata we basically use an ODI datastore for the mapping. as a source table to a planning dimension, and then load everything that we have in that table into planning.
However, is this a smart way to load metadata? I can say no. I have a good example in a big client that I’m currently working. They have an entity dimension with more than 23000 members, and in every maintenance cycle, only 1% of those entities changes or are added. That means that only 230 cost centers are new/changed every month. Well, in a normal ODI development, you will load all the 23000 members every month! And it is not only that: my client has seven applications, all of them with more or less the same size. Making the math, we would load more than 160000 members every month and only 1600 were needed to being loaded.
What can we do to solve that? Very simple solution. We only need to know what metadata already exists in the planning applications and compare it with the source metadata repository. The difference from this comparison is what we really need to load.
But how we can do that? With only two steps, one for extract data (that is the focus of this post) and other to compare the data (that we’ll talk in a later post of our ODI series).
Let’s start:
A Planning application repository has a central table where every object that you create in the application is stored. This table is the key source to extract metadata from the applications and its name is HSP_OBJECT. From this table we can get every information needed for the extract process and it makes easy to extract any necessary dimension information we need. In this table, we have six important columns:
We can notice that this table is based in a parent child relationship. This type of relationship is perfect in cases where we want to store a hierarchy inside of a table because in this way we do not need to know how many generations one hierarchy will have to create the table. We only need two columns with the parent and child IDs to rebuild that correlation between then. To achieve that Oracle database give us a very useful command: Connect by Prior … Start With.
The start with .. connect by clause can be used to select data that has a hierarchical relationship and Prior word is used to create recursive-condition. Long story short, everything we need to do is:
This command will retrieve all the Account Dimension hierarchy and if we need to rebuild the metadata from another dimension the only thing that we need to do is change the OBJECT_NAME to the desired dimension name, E.g. Entity. This query will be the core of our extract process. For now, we have the hierarchy built, but only this is not enough, as we need to have all information about the members in order to compare to our inbound table. We need to take the other metadata information from planning repository as well. We need to join all the following tables together to have everything that we need:
|
Repository Table Name |
Extract Table |
|
HSP_OBJECT |
Member |
|
Parent |
|
|
Generation |
|
|
Has_Children |
|
|
Postion |
|
|
HSP_OBJECT_TYPE |
Dim_Type |
|
HSP_MEMBER |
Data Storage |
|
Data Type |
|
|
Two Pass Calculation |
|
|
Aggregation (Plan1) |
|
|
Aggregation (Plan2) |
|
|
Aggregation (Plan3) |
|
|
Aggregation (Wrkforce) |
|
|
Aggregation (Capex) |
|
|
HSP_ALIAS |
Alias: Default |
|
HSP_MEMBER_FORMULA |
Formula |
|
HSP_DIMENSION |
Hier_Name |
|
HSP_STRINGS |
Description |
|
HSP_ACCOUNT |
Account Type |
|
Time Balance |
|
|
Skip Value |
|
|
Exchange Rate Type |
|
|
Variance Reporting |
|
|
HSP_PLAN_TYPE |
Plan Type (Plan1) |
|
Plan Type (Plan2) |
|
|
Plan Type (Plan3) |
|
|
Plan Type (Wrkforce) |
|
|
Plan Type (Capex) |
|
|
Source Plan Type |
|
|
HSP_ENTITY |
Base Currency |
|
HSP_ENUMERATION |
Smart List |
|
HSP_MEMBER_TO_ATTRIBUTE |
Associated Attributes |
|
HSP_UDA |
UDA |
This table contains the mapping of the tables and the information that you can find in the application repository. Two important things to say when you build the final extraction query from planning repository:
With this query ready, all we need to do is loop it passing the Dimension name to the core query mentioned above and it will extract all dimensions from planning.
See Part 2 of our ODI series.
This is it. We only need to compare the extracted planning data to our inbound data and we’ll see that in a future post.
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 