ODI Series – Planning 11.1.1.3 enhancements continued

In the last blog (a while ago I know, my spare time is extremely limited lately) I highlighted a few of the new enhancements available with 11.1.1.3, today I am going to continue with the new functionally and go through an area that I know has had a lot of questions raised in the past.

There have been numerous times I have been asked whether the process for setting the opening and closing year/periods in the scenario dimension can be automated, until now you have been pretty restricted and you either have had to manually change them through planning web of write custom SQL that basically updates the underlying table, this method still requires a planning restart because of the caching system planning adheres to and you can certainly feel uneasy about directly altering the tables.

Just so nobody pulls me up you have been able to use the outline load utility to set the year/periods since the release of V11 but 11.1.1.3 is the first release to let you do this via ODI.



If you reverse a planning application you will notice that there is a column to be able to update everything in the scenario dimension.

The easiest method to control the process of updating the start/end year/periods would be to create a flat file with the information you want to update.


Then in the usual way build your interface to update the information in the planning application.



Nice and simple update, though still requires you to populate the flat file (you could build an ODI process that will create and populate the file based on date logic for your system)



Another way to automate the process could be to extract the year/period from your data and update the scenario member based on it.



As an example say you wanted to base the Start/End Year using the Year column from the data and the Start/End periods based on the min and max periods.



With a combination of functions the first month and last month is calculated, this example is based on Jan-Dec calendar, if the calendar year is Apr-Mar or different then a function could be created to calculate first/last months in the data.



The Distinct Rows checkbox is ticked in the Flow so this should only return one record from the data.

If you want to make sure the SQL query is retrieving the desired results then execute the interface, go into the operator and extract the SQL.



Then run the code using your SQL GUI (in my case SQL Developer)



Results are as expected so the planning application should updated correctly.



These methods are fine and have their place but the majority of times the question has been raised about automating the process it has involved the use of substitution variables, many planning applications that are already in place can rely heavily on essbase substitution variables, the process for setting their value has already been automated and it is possible the variables that exist could be used to set the opening/closures values in planning.

So how can we use ODI to transfer the values of the essbase sub vars to planning, well one method could be to create an ODI procedure that uses an OS command to fire off MAXL, the MAXL could use the command “DISPLAY VARIABLE” and this could be spooled to a file, once the file is generated ODI could read in the file, transform the text and send it into planning. Is it certainly a feasible method but I find it a little messy.

How about using the essbase JAVA API, the API is installed and accessible with the default install of ODI so why not take advantage of this.

Extracting variable values using the API is easy so don’t run away just yet.

Before I rush into how to go about using the API I need to explain the solution I am aiming for so hopefully you won’t get totally lost.

Lets take a look at the interface to set the scenario information



So I want to use the interface but populate the target information with a mixture of substitution variables and hard coding, by populating all the target information I will not need a source, this will need some customization but I will go through that shortly.

I am going to retrieve values from substitution variables CurrYear and this will define the Start Year in the interface, CurrPeriod and this will define the Start Period. I am going to set the End Year the Same as the Start Year and in this example the End Period is going to be hard coded as December but could easily be a sub var. I am also going to set it against scenario member Plan.

Ok, lets look at a quick example of using the essbase java API to retrieve a substitution variable value.



Basically all the code does is log into an essbase server (EPMV11) and retrieve substitution variable valus for CurrYear/CurrPeriod and then print their values. It doesn’t matter if you don’t understand it totally you just need to know what it is achieving.

Here is the text version so if you do want to try this exercise it will save you having to manually entering it.

import com.essbase.api.base.EssException;
import com.essbase.api.datasource.IEssOlapServer;
import com.essbase.api.domain.IEssDomain;
import com.essbase.api.session.IEssbase;

public class EssTest {

private static String s_userName = "admin";
private static String s_password = "password";
private static String s_olapSvrName = "EPMV11";
private static String s_provider = "Embedded";

public static void main(String[] args) {
IEssbase ess = null


try {
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
IEssOlapServer olapSvr = dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
String sYearValue = olapSvr.getSubstitutionVariableValue("currYear");
String sPerValue = olapSvr.getSubstitutionVariableValue("currPeriod");
System.out.println("Current Year=" + sYearValue + " Current Period=" + sPerValue);
ess.signOff()

} catch (EssException e) {
System.err.println("Error: " + e.getMessage());
}
}
}

So how do we get this into ODI in a useable format? Well when you run an interface to load metadata into planning it uses the “IKM SQL To Hyperion Planning”, the KM goes through a number of steps to set up variables, connect to planning, and then load data into the underlying repository tables. So we need to customize the existing KM to incorporate the substitution variables.

First duplicate the Planning IKM so you can still keep you original IKM and not affect any of your existing interfaces.



Edit the duplicated KM and rename it to something meaningful



Click on the Details tab and you will see each stage of the IKM.



What we need to do is add a new step to the IKM, this step will log into essbase and return the values of the substitution variables specified, these values will be stored in Java variables that will be able to be used in the Interface.



Once the add new command icon is clicked a name is given for the step and as I am going to be using Java the technology is set to “Java BeanShell”

The Java code is then copied into the command window.



Now I removed the references to the class and removed the main as these are not required, I just want all the code be executed. Also the code is surrounded by <@ …. @>

So this is how the code looks

<@
import com.essbase.api.base.EssException;
import com.essbase.api.datasource.IEssOlapServer;
import com.essbase.api.domain.IEssDomain;
import com.essbase.api.session.IEssbase;

private static String s_userName = "admin";
private static String s_password = "password";
private static String s_olapSvrName = "EPMV11";
private static String s_provider = "Embedded";

IEssbase ess = null;

try {
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
IEssOlapServer olapSvr = dom.getOlapServer(s_olapSvrName);
olapSvr.connect();
String sYearValue = olapSvr.getSubstitutionVariableValue("currYear");
String sPerValue = olapSvr.getSubstitutionVariableValue("currPeriod");
System.out.println("Current Year=" + sYearValue + " Current Period=" + sPerValue);
ess.signOff();

} catch (EssException e) {
System.err.println("Error: " + e.getMessage());
}
@>


If this step were to be run in the IKM in its current state it would just basically connect to essbase and retrieve the sub values and then write them to the agent log.

Not much use yet then, also there is a lot of hard coding such as the admin/password/server and substitution variable names. If these ever changed then the step in the IKM would have to be edited.

What I want to do is pick up admin/password and server name using ODI API commands.

Now there is a very useful ODI API command odiRef.getInfo(“PARAMETER”), this command can retrieve many different values depending on the PARAMETER used, to see the full list have a look in the ODI API documentation

The parameters I am going to use are :-

odiRef.getInfo("DEST_USER_NAME")

This will retrieve the user for the target datastore, so in this example it will be the username to connect to the planning application that is being used in the interface.

odiRef.getInfo("DEST_PASS")

This will retrieve the password for the target datastore ( planning application)

Now I am doing using a little bit assumption here, I assuming that the planning application will have the same log account as the essbase server, I think in most circumstances it will have the same provisioning rights in Shared Services, if not the account could easily be provisioned to do so or just leave the code as it is.
odiRef.getInfo("DEST_DSERV_NAME")

This will retrieve the destination server name, this is another assumption that the essbase server is on the same server as planning, this is probably never really the case so the code could be left as it is or an Option created ( will be covering that in a minute )
In my example they are on the same server so I am going to stick with it as it gives you a feel of what can be achieved.

For the substitution variable names I am going to create new options in the IKM.



Right click the KM and select insert Option to create a new Option



So I have created a new option that will hold the substitution variable name that is going to be retrieved and be used for the Start Year. I have given it a default value of CurrYear.

I repeated the process to create an option that will be used for retrieving the Start Period (CurrPeriod) substitution variable.



This time the default value is CurrPeriod. As I pointed out before if your essbase server is on a different server than planning you could create another option to set your essbase server.



If you go into the interface and flow area, once you set the IKM to the newly customized one you will notice you now have the new options, the options can easily be changed altered to match the sub var names, this is useful as you could have different interfaces that require to use different sub vars.

Right back into the KM step that we created before with the Java code, it now needs updating to use the ODI API commands described earlier instead of the hard coding of values.



I had to enclose the username and servername with <%=….%> to retrieve the values correctly otherwise I kept getting errors saying the properties didn’t exist.

So that is the username/password/server name taken care of, I know need to retrieve the options and this can be done with another ODI API command - odiRef.getOption("OPTION_NAME")



If you are using the server name as an option then you can just change the code to use .getOption instead of .getInfo

As I am picking up the target server name to be used as the essbase server I had to add a little extra code, this is because it is a planning connection so the destination server will include the RMI port e.g. EPMV11:11333 and I only want the server name.



The code just splits the string into an array of strings where it encounters “:”
So it will end up with an array of two strings, one holding server name (EPMV11) and the other holding the RMI Port (11333)
I just set the variable to pick the first array value (EPMV11) so now I have the server name minus the RMI Port.

Right just one more addition to the code, if the script fails in any way such as unable to connect to essbase it will only output the error to the log, this will not cause the interface to fail and it definitely needs to stop.



So I just added code “throw new Exception(“…. This will raise an error and cause the step to fail.

The final code is :-

<@
import com.essbase.api.base.*;
import com.essbase.api.session.*;
import com.essbase.api.datasource.IEssOlapServer;
import com.essbase.api.domain.*;

String s_userName = "<%=odiRef.getInfo("DEST_USER_NAME")%>";
String s_password = odiRef.getInfo("DEST_PASS");
String olapSvrName = "<%=odiRef.getInfo("DEST_DSERV_NAME")%>";

String sYearVar = "<%=odiRef.getOption("VAR_START_YEAR")%>";
String sPerVar = "<%=odiRef.getOption("VAR_START_PERIOD")%>";
String s_provider = "Embedded";
String sYearVal;

String[] s_Split = olapSvrName.split(":");
olapSvrName = s_Split[0];

IEssbase ess = null;

try {
ess = IEssbase.Home.create(IEssbase.JAPI_VERSION);
IEssDomain dom = ess.signOn(s_userName, s_password, false, null, s_provider);
IEssOlapServer olapSvr = dom.getOlapServer(olapSvrName);
olapSvr.connect();
sYearValue = olapSvr.getSubstitutionVariableValue(sYearVar);
sPerValue = olapSvr.getSubstitutionVariableValue(sPerVar);

System.out.println("Current Year=" + sYearVaue + " Current Period=" + sPerValue);

ess.signOff();

} catch (EssException e) {
System.err.println("Error: " + e.getMessage());
throw new Exception("Error: " + e.getMessage());
}

@>

Once the step in the IKM is saved it will be put the command at the end.



I just moved the command to be the first step.

Just to recap if the interface is run in its current state using the customized IKM it will retrieve the two substitution values, store them in variables and print them to the log. What we need to do now is use the stored variables in the target section of the interface.



Enclosing the mapping in <@ code @> ensures you can call Java code.

Basically I have just set Start Year to be the value stored in the Java variable sYearValue, End Year is the same as Start Year (it doesn’t have to be this way you can easily add your own logic into it. Start Period is set to the value stored in the Java variable sPerValue and finally I hard coded the End Period to be ‘Dec’



I could just hard code the Scenario in as well but I decided to add another option to the IKM.



The ODI API command getOption is used again to retrieve the value stored against the option.

If you look at the interface you will notice I have nothing in the source area, this is because I don’t need to use the source to retrieve any information though if you try to run the interface as it stands you will receive an error.



If you look at the SQL that is being generated it is failing because there is no table name, the IKM by default will try and generate a table name depending on your source/staging area table name, as there is no source it can’t generate a table name.



The ODI API command getFrom() is used in the “Load data into planning” step in the IKM, this command generates the table name.

To get around this we need to give it a dummy table name, I am using Oracle in this example so I can use the DUAL table.

I could just easily replace the getFrom() command with DUAL but as I am going to once again create another option in the KM so I can set whether I want to use the getFrom or DUAL at the option level of the KM.



I am going to keep the default as the standard method of generating the table name, now I have to update the “Load SQL to Planning” step in the IKM



I have used the getOption command again to retrieve the value from the option I just created called “FROM_TABLE”



In the flow section of the interface I can now set how I want to use the option, in this case I have changed it from the default value to use DUAL



Running the interface again will now generate the correct SQL using the DUAL table name.

If you are using SQL Server then you can easily update the KM, you wouldn’t need the “from” or a table name in the sql e.g. select ‘Plan’ “Scenario”



All green in the operator now and you can see the additional step “RETRIEVE_SUB_VAR_VALUES”



So there we go planning has been updated based on a combination of essbase substitution variables and KM options.

Say you wanted to load the same values into more than one scenario member for instance Current & Forecast, this can easily be done as well all you would need is a source with the members in.




Then just map the target datastore to the source.



Remember to set the “FROM_TABLE” option back to the default as you need to retrieve data from a table this time.



Executing the interface should populate the scenario members Current & Forecast with the same substitution variable values



I am going to leave it now for today. I hope you have managed to follow and understand this blog, it should have given you an insight to what can be achieved with setting scenario member properties and how to integrate this with substitution variables, maybe it has given you some ideas to work on for your own projects.