Archive for March, 2014

DEVEPM at ODTUG Expert Panel – ODI

Posted in EPM, ODI with tags , , , , on March 25, 2014 by Rodrigo Radtke de Souza

Hi all!

This has been a great year for DEVEPM! First we had one of our submissions accepted at Kscope14. Now we were invited to be part of the second “ODTUG Expert Panel – ODI” that will happen on Tuesday, April 29, 2014 3:00 PM – 4:30 PM EDT. This is a free webinar offered by ODTUG (check more great webinars here) and this will be an unique opportunity to talk with some of the ODI experts about anything related to ODI, so don’t missed!

The following are some potential topics, but audience participation will shape the scope of the panel:

  • What are the top features of ODI 12c and can they be used in a typical data warehouse implementation?
  • Describe the migration from OWB to ODI 12c as it might occur in practice. Anything to watch out for?
  • What are some examples of customizations that you have made in ODI and why?
  • Interested in taking the ODI certification exam? We can talk about what was in the test, how to prepare for it, what to study, etc.
  • Using ODI to improve the Hyperion Planning/Essbase capabilities.

Cool hum? Go here to register today and enjoy it!

See ya!


We’ll be at KScope 14

Posted in EPM, Hyperion Planning, Kscope 14, ODI, ODI Architecture, ODI Architecture with tags on March 18, 2014 by RZGiampaoli

Hi guys how are you doing? We are very happy and proud to announce that our article “Unleashing Hyperion Planning Security using ODI” was approved for KScope 14! We’ll be there to talk about one of our preferred subject areas: How to improve the EPM tools potential using ODI. We’ll be very pleased if you guys show up in our presentation. It’ll be great to find everyone there and talk about EPM and other cool stuffs.

In our session we’ll show some tricks to unleash the planning security and enable it to be create at cell level, automaticaly and using any attribute dimension or UDA.

You can expect a lot of technical informations, a new way to view and work with Hyperion Planning and ODI plus some real facts about what has happened after a successful implementation of this approach.

And remember, 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 in Kscope 14.

Kscope 14 Speaker

10 Important Things to Improve ODI Integrations with Hyperion Planning Part 8 (Building Planning DataStore)

Posted in EPM, Hyperion Planning, ODI, ODI Architecture, ODI Architecture, ODI Mapping on March 10, 2014 by RZGiampaoli

In order to create a process that is able to load any application and dimension using one single ODI interface we need to make some code changes to the KM that is responsible to load metadata into Hyperion Planning. First, we need to understand the ODI concept of a KM. KM 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 SQL command based in those data stores. In a nutshell the ODI KM is code generator based in the information that you set in the interfaces, data stores, topology and so on.

As we know the default Hyperion Integration KM is able to load only one application and dimension at a time because of the need of a target data store for each dimension in each application. If we take a deeper look in the KM to see what it does behind the scenes we will see something like this:
KM Behind the ScenesFigure 1 – KM behind the scenes.

Basically what the KM does is translate the Planning application data store to a SQL query, and as we know, we get this data store by reversing a Planning application inside ODI. Fair enough, but this also means that if we could somehow have the same information that ODI has to reverse this application dimension to a data store we could easily end up with the same SQL created from that data store information. As we already showed before we have the Planning application repository itself where all the information about a Hyperion application is stored. We only need to read this information to get the same information provided by the ODI data store.

Knowing this the only thing left is to change the default KM according to our needs, and for this we need to make three changes on it:

  • Make the application name that it is going to be loaded dynamic;
  • Make the dimension name that is going to be loaded dynamic;
  • Change the way that the KM builds its SQL command that will load metadata to Hyperion Planning. Currently it builds its SQL command based on the source and target data stores and the interface mappings;

Default KM Behind the ScenesesFigure 2– Default KM behind the scenes.

In Figure 2 we can see how a default planning integration KM works. Basically it has two main steps: “Prepare for loading” and “Load data into planning”. The first one is responsible to set all information regarding connections, log paths, load options and so on. The second step is responsible to retrieve all source data based in the interface mapping and the source/target data store and load it to planning. In our case, the application and dimension names resides on the first step and the SQL command resides in the second step so we already know where we need to change the code.

But we need to analyze further to know what exactly we need to change. For the application name ODI gets it from “<%=snpRef.getInfo(“DEST_CATALOG”)%>” API function that returns the application name based in the destination target store that is connected to a logical schema that finally resolves into a physical schema that contains the application name itself. If we change it to an ODI variable we will be able to encapsulate this interface into an ODI package and loop it passing the application name as a parameter, making it independent of the target data store topology information and giving us the a ability to load any Hyperion planning application using one single interface.

The dimension name follows the same logic: ODI gets it from “<%=snpRef.getTargetTable(“RES_NAME”)%>” API function that returns the resource name from the target data store that in this case is the dimension name itself. Again if we changed it to an ODI variable we will be able to encapsulate this interface into an ODI package and loop it passing the dimension name as a parameter, making it independent of the target data store resource name and enabling us to load any dimension with one interface.

The third part is the most complex one. ODI data stores for planning applications are so different from one dimension to another that they require one data store object for each dimension. In figure 10 we can see that ODI relies on “odiRef.getColList” API command to return all mappings done in the target dimension data store, which has the correct dimension format required to load that dimension metadata into planning.

So the big question is: How can we change the “Load data into planning” step to use a dynamic SQL to create dynamic interface mappings to load to any application/dimension? The answer is to rely again on the “Command on Source/Target” concept and on the planning repository metadata information.

Instead of getting the mapping information from the ODI data store object, we can query Planning repository to get the same mapping for all dimensions and applications being loaded. The result of this query is a formatted mapping, identically of what ODI would have generated if we used the default planning development, but with the big advantage of being entirely dynamic to any application and dimension.

Dynamic KM behind the scenes
Figure 3 – Dynamic KM behind the scenes.

In figure 3 we can see an example using an Attribute dimension. The command on source will query HSP_OBJECT and HSP_ATTRIBUTE_DIM of a given application (defined by #SCHEMA_APP variable) to retrieve information about one attribute dimension (defined by #DIMENSION variable). Those variables are passed from an external ODI package that will be used to loop all applications and dimensions that we want to load.

Dimension Datastore Information

Table 1 – Dimensions Data Store information.

If we take a further look into all different data stores that a Planning application could have, we will see a pattern regarding the information that we need to send to Planning to load metadata depending of each dimension, as we can see in the Table 1.

The logic to create the dynamic mapping columns is exactly the same used to create the inbound and the extract tables. The only difference is that for the inbound and extract tables we need to put all columns together and for the KM mapping we need to, depending of the selected dimension, take the right information in the application repository. This information will help us to create the necessary mapping that contains the right source columns and the right alias of those columns, which will inform Planning about what that metadata column stands for.

Since our metadata tie out table contains standard columns for all dimensions we don’t need to worry about adjustments when we change to another dimension, and since our source metadata table already has the metadata information in the correct planning format, we don’t even need any kind of transformation here, it is just a matter to read from the metadata source table and load directly to Planning.

In the Figure 3 example we will use the SRC_MEMBER, SRC_PARENT and SRC_ALIAS as the mapping columns and for the Planning alias the only one that is dynamic is the member name alias that identifies the dimension name. To get this information we need to query the application repository looking for the attributes into HSP_ATTRIBUTE_DIM and for its name in HSP_OBJECT table, and finally we can use the OBJECT_NAME column to get the dimension name alias.

Executing this query we will get a one line mapping string that will be passed as a parameter (#MODEL) from “Command on Source” to “Command on Target” and will enable ODI to load metadata to that specific dimension/application. If we execute this interface and look at the query created in ODI operator we will see that the result is the same as a default KM would create but with the big advantage of being entirely dynamic. Following this logic, we would only need to change the value of the #SCHEMA_APP and #DIMENSION variables to get another application\dimension loaded.

Off course we need to work a little more to get the mapping for the other dimensions as Account or Entity, but the idea will be always the same: query the application repository to get the data store information depending on the dimension\application selected.

Dimensions Mapping informationTable 1 – Dimensions Mapping information

In table 1 we can see all the possible mapping combination that we can have in a planning application for the mainly planning dimensions and we notice that some information are dynamic (dependent of the planning repository) and some are fixed. To put everything together in one single query here are some tips:

  • The majority of the columns are fixed and can be obtained with a simple “select ‘Any string’ from dual”;
  • The easiest way to create this SQL is to create separated SQLs for each different kind of information and put everything together using Unions statements;
  • Split the final query in small queries to get the different categories presented in table 1;
  • Use the MULTI_CURRENCY column in HSP_SYSTEMCFG table to find out if that application is a multicurrency one or not;
  • For aggregations and plan type mapping we need to get the name of the plan type itself and for this we use the HSP_PLAN_TYPE table;
  • When the query is ready you need to add a filter clause to filter the dimension from where that information belongs;

With the query ready the only missing step is to insert it into the “Command on Source” tab inside the Planning IKM and pass the string generated by it to the “Command on Target” tab as we can see in the figure 3.

This ends all the preparations that we need for learn how to build a ODI interface that will dynamically load metadata into any number of Planning applications.

Thanks you and see you in the next post.