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.

ODI Series – Planning 11.1.1.3 enhancements

With the release of version 11 came the outline loader utility, this utility provided the ability to load meta/data information into planning from command line using a flat file, it also allowed to load information to ALL dimensions and even exchange rate information. This utility had always been desired it just arrived very late.

I expected the planning adaptors for ODI to equal the functionality of the outline loader utility in version 11 but sadly this was not to be, the adaptors were more in line with HAL where you could only load metadata information for attributes, account, entity and custom dimensions.

In case you were not aware in release 11.1.1.3 the new functionality has now been included for use with ODI, I am not sure if there any mention of in the documentation, though I may have missed it. You don’t even have to update any of the planning ODI adaptors as the functionality has been added to the core planning Java classes.

Prior to release 11.1.1.3 if you reversed a planning application (I am using the sample planning app as an example) then you would end up with a model similar to :-



Repeat the same reverse on 11.1.1.3 then the model will look like :-



This means you can now use ODI to maintain all your dimensions in a planning application; all you need is the starting shell of a planning app.

If you maintain multi-currency applications then the added functionality of being able to manage FX rate information through ODI is a huge bonus, I know in the past I had to build a custom application to upload FX data from excel to the planning repository tables, the client required to populate 60 years of FX data and the usual method of entering it through planning web was just not feasible, the ability to use ODI would have been much more of a robust solution.



Expanding the HSP_FX_RATES Datastore shows that you can pretty much do everything in ODI that you can through planning web, I will let you decide which method is going to be more efficient in the long run but I know where my money is on.

While we are on the subject I might as well take you through how to use it, now as ever your sources could be flat files, database tables or even pulled in via a web service, the data would not have to come in the exact format required by ODI, that is where the power of transforming data in ODI could be used. In this example I just going to use a simple formatted csv file.



I am going to populate the equivalent of above but using a csv file, I will let you type it manually and I will create the integration and lets see who is the quickest ☺



Ok, the CSV file contains small meaningless data but this is just a simple example,

First create and reverse the file Datastore.



Create the interface; drag the csv Datastore to the source and the HSP_FX_RATES DataStore to the target.



I manually added in the Table name and To_Currency to the target mapping but this could easily be picked up from a file or populated using ODI variables.

If the exchange rate table does not exist then it is created for you so even less manual intervention, the only issue I could see was if you use the Description column as it didn’t seem to populate planning, I don’t see this a big deal though and probably will be fixed in the future. If you are really fussed about the description just create the table first in planning web.



Are you still manually entering as I am finished ☺

This is a really beneficial new feature and with the power of ODI you can now provide a worthy solution for managing planning exchange rate data.

In the next blog I am going to continue with the new functionality and provide a solution to a question that has been asked many times in the past.

Stay Tuned!!!

ODI Series – Handling errors with Planning/Essbase loads

I always encourage questions being asked that have not been covered in my blog and Cameron Lackpour has raised a very good question on the OTN forums.

The question basically is how do you know when there have been any metadata load errors so you can take action, it is true that an error log is produced but there is no real indication that errors have occurred.

There are a number of different methods to attack this problem and I am going to go through a couple of methods that you can use. You would of thought that just checking for the existence of the error log would be enough but unfortunately the error log is recreated each time an interface is executed even if no errors are wrote to it, otherwise the ODI tool “OdiFileWait” could have been used to check for the existence of the file.

One method would be to use something like Jython/Java/OS commands to check the size of the file and if it is not zero bytes then act on it, it is a perfectly acceptable way of dealing with it but not one of my preferred methods.

The first method I am going to take you through is based on the statistics that are generated in one of the steps on the metaload knowledge modules,



I did write a blog on how to report statistics correctly so it is worth having a look through that first to get an understanding of what I am going to be talking about.



Right, I will now consider you know how to report statistics correctly by updating “Report Statistics” step in the knowledge module.

If you run an interface metaload that generates load errors and check out the “Report Statistics” step in the operator you will see the number of records processed and how many of them were errors



So by looking in the operator we can see that 1 record was rejected but we need to be able to act on that error, to do this we need to access the ODI work repository where it stores the statistical information, before we can do that we need to set up a connection to the work repository, I will briefly go through setting up the connection which I am sure you can do for yourself by now.

In the Topology manager create a new Data Server for the technology your ODI work repository is using.



Add the JDBC connection information.



Add the physical schema information



Finally add a logical schema to the context you are using.



With the connection in place we can now access the tables in the work repository, the table that stores the statistical information is “SNP_STEP_LOG”.



As you see there are fields NB_ROW,NB_INS,NB_ERR



These relate to the total number of records, the records inserted and the number of error records, so you can see the values for the interface I ran earlier, the whole record is defined by the session number.



The session number for the interface I ran was 909001. So if you think about it by querying this information you will be able to find out if and how many errors were generated.



That is all fine but how are we going to know what the session number is for the interface being run, luckily ODI has an API command that will tell us just that. The command is odiRef.getSession(“SESS_NO”)

Now we need a way of storing the value produced from the query and once again ODI has just the thing for it – variables, to be specific a refreshing variable, a refreshing variable lets you set the value of a variable by the use of a query.

Create a new variable, it can be set to a numeric type.



In the refreshing tab select the logical schema for the work repository that was set up in the topology manager earlier.



The query is added, note you will need to surround the ODI API with <%= %> for it to be able to compile and execute correctly, you will not be able to validate the query as the query can only use the API commands at runtime.

With this refreshing variable we can now store the number of errors that have been generated, we just need to put this all together in a package and add a check to act on the value of the refreshing variable.



If you drag the variable onto the diagram again and set it as an evaluate variable.



This means we can check the current value of the variable and if it is greater than 0 then act on it.



In my example if the number of errors is greater than 0 then I send an email out advising there were errors, the amount of errors and attaches the error log, if the errors were 0 then I send out a success email. This is just an example you could add whatever process you want depending if there were errors or not.

Well that is the first method covered and it is probably the method I prefer to use.

The second method also uses a refreshing variable but this time the variable will use a query to read the first line of the error log and if it doesn’t contain a first line it errors and follows the next step of a process, if it does contain an error it follows a separate step.

First of all you will need to have a logical schema that points to directory location of the error logs.




If you have ever looked in the operator at the SQL generated when loading from a text file you will notice a long string of ODI specific parameters for the file that is being loaded.



Basically we are just going to lift the code that is generated and put that into a refreshing variable.

If you want to generate the code for yourself then just create a new data store using file technology



In the Files tab just set it is as delimited, you don’t need to change any other options.



In the columns tab manually add a new column of string type and set the lengths to a high value. Now you can just create an interface and use the data store as the source and for the target it doesn’t really matter as we are just after the code that is generated.



Execute the interface, it doesn’t matter if it fails just check the operator for the Load data step.



Now you can just copy the code that is generated.



Create a variable, the data type of Alphanumeric can be chosen.



Set the logical schema to the one that was created earlier that points to the error log directory.

Paste in the code

select C1 C1_C1
from TABLE
/*$$SNPS_START_KEYSNP$CRDWG_TABLESNP$CRTABLE_NAME=
ERROR_LOGSNP$CRLOAD_FILE=E:\FileStore\/enterr.logSNP $CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=09SNP
$CRFILE_SEP_LINE=0D0ASNP$CRFILE_FIRST_ROW=0SNP
$CRFILE_ENC_FIELD=SNP$CRFILE_DEC_SEP=SNP$CRSNP
$CRDWG_COLSNP$CRCOL_NAME=C1SNP$CRTYPE_NAME=STRINGSNP
$CRORDER=1SNP$CRLENGTH=1000SNP$CRPRECISION=1000SNP$CR$
$SNPS_END_KEY*/

Now I updated the code slightly, I removed the C1_C1 as that is not really required, I also updated the section

$CRLOAD_FILE=E:\FileStore\/enterr.log

to

$CRLOAD_FILE=<%=odiRef.getSchemaName("D")%>\enterr.log

I updated it to use the ODI API command getSchemaName, this will convert the current schema details at runtime into the full path, this is good practice as if the directory ever changes you only have to change the location in the topology manager.

So the final code is

select C1
from TABLE
/*$$SNPS_START_KEYSNP$CRDWG_TABLE
SNP$CRTABLE_NAME=ERROR_LOG
SNP$CRLOAD_FILE=lt;%=odiRef.getSchemaName("D")%>\enterr.log
SNP$CRFILE_FORMAT=DSNP$CRFILE_SEP_FIELD=09
SNP$CRFILE_SEP_LINE=0D0A
SNP$CRFILE_FIRST_ROW=0SNP$CRFILE_ENC_FIELD=
SNP$CRFILE_DEC_SEP=SNP$CRSNP$CRDWG_COLSNP$CRCOL_NAME=C1
SNP$CRTYPE_NAME=STRINGSNP$CRORDER=1SNP$CRLENGTH=1000
SNP$CRPRECISION=1000SNP$CR$$SNPS_END_KEY*/

Just to recap, if you use this refreshing variable it will try and read in information in the file, if will either fail if there is no data or succeed if there is data in the file.

Now we can put this into a package like the earlier example



Then just add steps to the success/failure of the refreshing variable.



So there we have it two examples of error handling when using the Hyperion KMs, hopefully you have understood and found it useful what I have tried to explain, I know it can get a little confusing at times. If you want it explaining further then just get in touch.

Until next time!!!

ODI Series – Problem with latest essbase patch release.

Just a quick update, it seems like there is an issue with the recent patch release 10.1.3.5.2_02 ONE-OFF PATCH Essbase Fix that resolves the painful issue of ODI loading data record by record to Essbase when it encounters a rejected record.

It is certainly true that it does fix the data loading problem but it seems to have spawned a new issue around the error logging.

Before the patch was applied and a rejected record was encountered then the error log would display…



Sorry if the image is small but the log says :-

Year,Measures,Product,Market,Scenario,Data,Error_Reason
'Mar','Sales','100-10','New York1','Actual','720','Cannot end dataload. Analytic Server Error(1003014): Unknown Member [New York1] in Data Load [1] Records Completed'

After the patch has been applied loading the same data produces the following error log.



Year,Measures,Product,Market,Scenario,Data,Error_Reason
'Mar','Sales','100-10','New York1','Actual','720','Loading failed with error [3303]'

You do get the actual record that was rejected but not the member, this is fine if only a couple of records are rejected but if you have a large number of rejections and need to process them then this becomes a real pain.

This bug has been logged with Oracle (Bug 8912703 - AFTER PATCH 10.1.2.3.5.2_02 REJECTED MEMBER INFORMATION IS NOT COMPLETE) so it will be fixed.

I will keep you informed once I hear any further information on it.

Using Lifecycle Management (LCM) with ODI

It has been a while since my last blog, lately I have not had much chance to sit down and dedicate the time required to research and write a detailed blog but here goes.

I have been meaning to write a blog on combining the benefits of LCM (Hyperion version 11) and ODI for a while now, the beauty of LCM is that you can automate loading and extracting of objects from most aspects of the EPM suite of products, this gave me an idea of how to incorporate this with ODI.

If you take for example the loading of cell text into a planning application, in the past this has always been a pain but with LCM you can load cell text easily, say you have a csv template with the cell text and you wish to automate the process of loading this into a planning application, if you want to use LCM then you would have to format it into the correct XML file format. I want to go down the route of just supplying the csv file and not mess around with having to format it into XML; this is where ODI comes into play.

I am going to break this down into sections and provide the usual step by step process, this example is just looking at cell text but as LCM uses the same principle you could easily use the concept for extracting/loading other information.

I am going to use everybody’s favorite sample planning application to add a line of cell text.



We now need to use LCM to export cell text to have a look at the output XML file, this is done by logging into Shared Services, expand Application Groups and then the group you hold the planning application against which in my case is called Planning, select the planning application and this will open the LCM artifact list, expand relational data and select “Cell Texts”



Define the migration and set the destination as file and give it a name.



Once this has been executed it will create an xml file on the server, usually in the directory \hyperion\common\import_export\<account migration run against>\<migration name>\....



If you open the xml file you will see the format LCM uses.



Now what I want to do is provide the following csv file.



Then automatically log it into the sample planning application.

ODI is pretty good at handling XML formats and I have touched on using it in the past, it has the ability to reverse engineer an xml file and generate Datastores based on the xml structure, you can then load information from the file or generate a fresh populated xml file.

The first thing to set up is the connection details to the XML file in the topology manager.

Right click XML and “Insert Data Server”



Within the JDBC section select the “Sunopsis JDBC Driver for XML” driver



In the URL you need to provide details to the XML file, the outputted xml file from LCM was named “Cell Texts.xml”, I moved this file and renamed it to remove the space in the filename, I never like having spaces in file names as it can sometimes cause problems.



The format to point to a file is :- jdbc:snps:xml?f=
You can use the “Test” button to make sure ODI can connect to the file.

By default ODI uses its memory engine to store the relational schema mapping of the XML schema, this is fine in most cases though if you had a large complex xml file then it could run into problems, it is also possible to store this information in an external database, I am going to go down the database route as it is easier to show what is going on behind the scenes.

To use an external database you can specify all the parameters in a properties file, an example of a properties file is in \OraHome_1\oracledi\demo\xml\properties, the properties file will need to exist in a location that ODI can reference so if you are using a local agent then it will be fine in the drivers directory, if you are using an agent service in windows then you will have to update the wrapper configuration file which I will go through shortly.



Basically you can use the four parameters I have highlighted as these are mandatory, you will just need to update the driver/url to match your environment, the schema relates to the Oracle schema which is the same as the username, the password can be encoded by running the following command from the oracle\drivers directory.

java -cp snpsxmlo.jar com.sunopsis.jdbc.driver.xml.SnpsXmlEncoder password

If you want to find descriptions for each of the parameters it is available in the documentation: - OraHome_1\oracledi\doc\webhelp\xml\index.htm

To reference the properties file the filename minus the .properties needs to be added to the url in the data server configuration, the parameter is dp_props



Once you apply the Data Server settings a physical schema window will open and you should be able to select schemas from the dropdown.



Then it is just a matter of applying it to a context and giving it a logical schema name.

As I said earlier the properties file needs to be in a location that ODI can reference e.g. \oracledi\drivers which is fine if you are using a local agent, I am using an agent created as a window service so the file OraHome_1\oracledi\tools\wrapper\conf\snpsagent.conf requires an extra line in the classpath reference



Once the file has been updated the agent windows service requires restarting to pick up the reference to the file.

This completes the configuration and we can move on to the designer to reverse the xml file into a model.



Create a new model, set the technology as XML and pick the logical schema that was created.



In the Reverse section just select the correct context and you are ready to reverse.



The structure of the XML file will be converted into DataStores, you can view how each DataStore relates to each other by expanding Hierarchy.

If you have a look in the repository you will see a number of tables will have been created, most of them relating to the DataStores



The table SNPSRDBIDGEN holds all the table names



The other tables relate to the different elements of XML file, for instance DIMMAPPING holds all the dimension mapping information, if you look at the XML file and the database table you will get an understanding of how it hangs together.



The above table we don’t really have to mess around with as it is going to stay static, the table that we are interested is CELLT_CELLNOTE as this holds the cell text information.

The XML file has the following information



The database table contains



Most of the information is self-explanatory, the extra information are just IDs and they link to the other tables, if you are loading information against one plan type then the IDs will stay pretty static and the only information that needs updating is the dimension intersection for the cell text and the actual cell text value.

If you need to get a better feel of how the tables the tables are populated it might be worth entering more cell text and against different plan types then follow the LCM process of exporting to XML, once you have the XML file in place it is possible to use a command to read the file and load it straight into the repository tables.

This is done by creating an ODI procedure.



Executing the procedure will load the information from the XML file directly into the repository tables.

So basically what I need to do is populate the CELLT_CELLNOTE table with the information from the csv file that holds the cell text information, to achieve this it can be done with a simple interface file to SQL.

I am not going to go over how to set up the file information in ODI as it has been covered in the past, if you are unclear read back into the early days of my blog, quick overview:- set up File Data Server in the topology manager or use an existing one, create a new DataStore against a File Model, reverse the DataStore and you should end up with something like :-



The table CELLT_CELLNOTE will also need to be reversed into a DataStore.



Ok now to create an interface to load the CSV information into the database table.



Mapping the interface is just a matter of comparing the records in the db table CELLT_CELLNOTE and assigning against a dimension in the source. e.g. DIM1 has Actual as the value so this maps to Scenario.

After the dimension columns have been mapped it is just a case of hard coding the numeric values so for instance DIM1ORDER is 0 in the db table and it will stay static so it can be hard coded with that value in the target, this is the case for all the other ORDER columns, the only element that could possible change is the PLANTYPEFK but as in this example it is against one plan type it will always be a static value of 0.



In the flow option truncate is used to clear down the table before loading the new records.



After executing the interface you can see the records from the CSV file have been populated into the CELLT_CELLNOTE database table.

Ok, so now we have the values we want in the cell note table and all the other tables are static so don’t need touching, what we need to do now is recreate the XML file with the new information, this can be done again using an ODI procedure.



This time the command SYNCHRONIZE FROM DATABASE is used which basically dumps the information from the database tables and recreates the XML file based on the information



The XML file has been recreated and populated with the information from the original CSV file, now all that is left is to use LCM again to load it back into planning.

I am going to manually do it for today so that means renaming the xml file back to “Cell Texts.xml” and moving it back to the LCM file directory



In Shared Services it is just a matter of selecting the same migration name under Application Groups > File System



Run through the options and execute the migration.



And there you have it! Cell text that originated from a simple csv file format loaded straight into planning, this concept can be used for any of the areas of LCM you are not restricted just to thinking about cell text.

I must point out if you are going to follow this example use “NoSegment” and not “No Segment” as I have shown as it will fail because you can’t use aliases when using LCM it needs to be the member name, I am too lazy to go back and change the images.

Next time I will go through automating this process and touch on using the LCM API via ODI to execute the LCM import.

I hope you have found this useful, until next time!!

ODI Series – Patch Update – Essbase data load issue resolved

Well it certainly been a long time in the making but at last the patch has been released to tackle the essbase data issue, the problem manifests itself when performing a data load and a rejected record is hit e.g. an unknown member, the load reverts into loading record by record instead of the bulk commit size set in the KM options.

If you want to find more information on the bug, have a read of one of my earlier blogs here.

The patch is available on metalink and here is all the information you need.

ORACLE DATA INTEGRATOR 10.1.3.5.2_02 ONE-OFF PATCH

Patch id - 8785893

8589752: IKM SQL to Essbase Data - Load in bulk mode instead of row by row processing when an error occurs during load

The patch has to be installed on a version 10.1.3.5.0 and greater

The patch itself is only 59KB, it is just a replacement jar - odihapp_essbase.jar

To install the patch shut down any ODI processes such Designer, Topology Manager, Security Manager, Operator or Agent.

Rename the existing odihapp_essbase.jar that is in oracledi\drivers and extract the patch jar to the same location.

Then you can restart everything back up.

So does it work, well lets have a quick go. First all what you need to be aware of is that if you don’t use an essbase load rule the problem will still exist, the fix only works with the use of a load rule.



Back to my trusty quick example that loads data from a database table into the sample basic essbase database.





I have put a deliberate error for Market to trip up the data load

Here is example of the log file before applying the patch



And here is the log file after patching



And an error file is produced with the rejected records, so looks like it is a success, I didn’t have a chance to test a large set of data but it looks to be using the same method as a standard essbase data load using a rules file so performance should be the same. Good news at last!!!

ODI Series – Executing Hyperion Business Rules Part 2

In the last part I went through a couple of methods on how to execute business rules from ODI, today I just want to quickly show how you can incorporate this into a Knowledge module.

Incorporating this into a KM will allow you to say do an essbase dataload and then execute a specified business rule using one interface, this method will also allow you to pick up the connection information so you don’t have to specify it again. It can be incorporated into any KM I am going to use “IKM SQL to Hyperion Essbase (DATA)” in my example.

I am going to assume you went through the last part and understand the concept of using procedures to execute a rule. I am also going to use the Java method but you can easily use the OS execution method.

First duplicate the KM - “IKM SQL to Hyperion Essbase (DATA)” and give it a name



We need to be able to allow whether a rule will be run or not, this is done with the use of KM options.

Right click the KM and select “Insert Option”



I set the default to No but it is up to you, it just defines what is shown in the options of the KM.
The position value defines where the option is placed in the list of options.

Next an option is required for the name of the business rule that is going to be run.



You can add as many options as you like but I am going to stick with these two for now.

Now edit the KM and the details tab and add a new command



The command is exactly the same as using a procedure; the only difference is that the source and target depends on your interface source/target.

Select “Jython” as the technology, the command section is pretty much the same as what was used when I created the procedure in the first part of this blog, the difference being is that I can use some of the variables that have already been defined in the KM.

If you look at the command “Prepare for loading” in the KM you will see some of the variables that have already been defined.



These variables are valid throughout the interface.

If you look back to the original procedure I created then you will see I set it up like :-



I don’t have to hardcode the server name; username and password as these variables have already been defined, I can pick up the rule name from the KM options.

I do have to change the command used to define the variable file, this is because when I originally created the procedure I used the “Command on Source” to set the technology and logical schema.



This time I will use a different ODI API command - getSchemaName(), this command lets you pass in the name of the logical schema and will return name of the data schema, so in this case by passing the in the logical schema name for the file it will return the directory location, it will automatically pick up the context that it is being executed on.



As you can see I have used the variables (serverName,userName,password) that have defined in the “Prepare for loading” command

I have used the API command getOption() to retrieve the value of the option HBR_NAME

If you select Options tab you will see everything is selected and the “Always Execute” option is checked, we only want the command to be run if the option “RUN_HBR” is set to yes, so uncheck “Always Execute” and check “RUN_HBR”



This means the command will only be run if the RUN_HBR option is set to “Yes”

Once you apply then the command will be created as the bottom of the list of the commands, depending on where you want the business rule to be executed defines where you move the command to in the list, I want it run just after the load so move it up to run after “Load data into essbase”.



If you create a standard essbase data load interface and in the flow section you should be able to select the custom IKM that has been created, it should also display the options created.



If you don’t change the RUN_HBR option then the KM will operate exactly the same as the standard essbase data IKM.



If you change the HBR option and enter the business rule name then it will execute the business rule after the data load.



As I said you don’t have to use this IKM you could just as easily customize one of the planning KMs, you could also customize it further and add in the creation of the variables HBR file.