DEV EPM

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:

• 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:

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.

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