ODI Series – Executing Hyperion Business Rules

At a conference I was attending a few weeks back a question was asked – “Is it possible to run business rules through ODI?”.
Now it is certainly true that none of the adaptors have the functionality to run business rules but I thought I would go through a couple of ways that it is possible to execute them.

This is going to be based around classic business rules and not calc manager ones. I always thought the available functionality outside EAS has been pretty poor for business rules, you have the choice of the not so great web launcher or the command line launcher, and there has never been an API that I feel would have been a lot more useful.

Anyway in your business processes you may have business rules that are run a scheduled basis and you want to integrate them with your current ODI processes.

The first option is to use the OS functionality in ODI procedures, I am going to use the command line launcher in version 11 which is pretty much the same as previous versions except you have to use an encrypted password file. I am also going to be using a windows O/S but the example could easily be modified to fit a different environment.

The documentation for the command line launcher is completely out of date but the usage should be :-

CmdLineLauncher [-p:] -Sservername -Uusername [-rBusiness Rule Name | -sSequence Name] [-fRun Time Prompts file]

The important parameter to watch out for is –fRun Time Prompts file as even if you are not using run time prompts you still need to provide information on which database you want the rule to be run against.
The run time prompts file can be generated from within EAS by right clicking on a rule and choosing “Automate Launch Variables” and this will generate a file.



The ExecDB parameter is always required, all parameters are paired together with ::
You can manually edit the file to add in more run time prompts if required.

If you are using version 11 then you will have to create an encrypted password file first, there is a utility (PasswordEncryption.bat) that is in the same directory as the command line utility (\Hyperion\products\Essbase\eas\console\bin)

So an example to run a business using the command line launcher would be



Ok, so we need to transfer this example to ODI. In the designer you will need to create a new procedure, add a new command and set it against “Operating System” technology.



To use the command shell it is good practice to start with cmd /C this means the command will be terminated after execution



If you execute the procedure as it is then it will fail.



Not the most useful error code but what you need to realise is that the command is not being executed in the directory of the utility, if you have a look at the command line launcher batch script you will see one of the reasons why it is failing



The script uses a relative path so none of the java files will be added to the classpath, I created an environment variable to point to the EAS directory and updated the batch script to use it.



It is up to you whether you want to use an environment variable or just set one up in the batch script.



You also need to be aware that when calling the command line launcher from ODI you need to specify the location of the password file and run time prompts file otherwise it will not locate the files.

Once again, this can be done by hard coding the path or using environment variables.



Another option would be use ODI variables and they could be set a run time if required. (If you want to know more about ODI variables have a read of this previous blog)



You could set a variable up for each parameter of the command line call.



This way the variables could be set when you run the integration or they could be constants or set by retrieving the information from tables or files, there are many different options to choose from.

This time when you execute the procedure it should through without any problems.


So that is one method which does what it is supposed to but is a little limited, the next option would to be use a little piece of Java to call the launcher, now unfortunately there is no HBR launcher API but that is not going to stop me and I will show you how it can be done.

Before you start you will need to use a number jar files in your classpath, most of them are displayed in the batch file but I found that it required an extra jar file to get it working (xalan.jar). I just copied them all to another directory.



You will also need the HBRCLient.propeties file as the java code looks for this file by default.



Basically you just need to pass in the server name, username, password, rule name, the RTP file location and the location of the HBRClient.properties file, this is set as a system property (HBR_HOME)

The main method is not required this is just to test the java code was functioning; it can be removed after testing.

In using the java example you don’t need to provide the location of the password file you can pass it straight into the method, unfortunately you still need to provide the location of the run time prompts file. I did have a look whether you could get round this and it is possible but you would have to change the core java code and I didn’t want to head in that direction.

If you are interested then in the LaunchManager class there is HashTable that stores the run time prompt properties.



If this is changed from private to public you could probably push the RTP values into it, as it is currently private you can’t access it from outside of the class.

Once you have the code working and executing the rules then it can be compiled and put into a jar file.

To be able to use this java in ODI you need to let ODI know where all the jar files are, there are few ways of doing this, you can just copy all the files in the oracledi\drivers directory, make sure you restart any ODI GUI and agents.

Another way would be to update the classpath in odiparams file in the \oracledi\bin directory.



This will dynamically add all the jar files to the classpath from the directory you specify, in my case E:\Java\HBRlauncer

If you are running an agent as service then you will have to update a configuration file that is called when the service is started, it is snpsagent.conf and is located in \oracledi\tools\wrapper\conf



I just added a new line with location to the jar files.
One thing to watch out for with the above methods is not to include xercesImpl.jar as it causes conflicts with the other classes that ODI uses.



You can just remove the file and the code will still function correctly as it will use a class already referenced by ODI.

With the files in place you can now create a new procedure in ODI to call the java method, the procedure could use “Java BeanShell” or “Jython” technology.

I am going to use Jython the same technology that the Hyperion KMs use.



In the example I just import the class in, set up the variables and pass them into the Java method.

Once again you can set use ODI variables instead of hard coding all the parameters.



If you any of the parameters are incorrect it should raise an error.



If the rule has run successfully and you are running an agent as a service then it will be logged in \OraHome_1\oracledi\bin\agentservice.log



The hbrlaunch.log will also be populated with information about the execution just like it would if it was run from EAS or planning.

The only issue at the moment with this solution is that you have to manually edit the RTP file each time you want to make changes, well as usual there are ways around this and you can let ODI do the work and create the RTP file for you.

You could have the information stored in a db table, a file or an ODI variable and create an interface to generate the file. I am going to show how this can be done using an ODI variable that could be populated at run time.

First you need to create a File Datastore point to the directory and name of the file.



Create one column with a length that will be long enough to hold RTP information.



Now create a variable of text type to hold the RTP information



Create a new interface, set the staging area (I am using SQL Server as the staging for this example) and drag the file Datastore that was just created to the target.



Next in the mapping I added the reference to ODI variable and used a replace function to replace spaces, this is because with a ODI text variable the lines are separated by a space, I replaced the space with a line break char(10).

This means it should write a new line for each line of the text variable.



I know your asking, but what if the value element of your variable has spaces in it, well if that is the case you could add something to distinguish each separate line for example.



Then use the replace as

replace('#HBR_Variable','@NEWLINE@ ',char(10))

In the flow section of the interface the IKM to use is “IKM SQL to File Append”, though it will require some changes as if you try and run the interface it will generate an error in its current state.



This is because the IKM is based on the interface having a source and we are not using a source so the SQL generated is incorrect.



As you can see there is no table name, to get around this, duplicate the “IKM SQL to File Append” and give it a new name



In the details tab, you can delete the Command “Insert column headers” as it is not required and then edit the Command “Insert new rows”

In the source tab you view the code that is generating the SQL when the IKM is used



As you can see it has from and then an ODI command to retrieve the table name, this can be updated, if you are using SQL server as the Staging Area then you can remove everything from just before from.



If you are going to be using Oracle as the staging area you would change it to “from dual”



So what this means it will ignore generating any source table name and will just run SQL to display what you have in the target mapping of the interface, in this example it will be the text from the ODI variable.

Once you have saved it you can update the IKM in the flow of the interface to use the newly updated IKM.



I have used TRUNCATE option so the file is recreated each time.



When you execute the interface and check the steps in the operator you can now see that it has generated the correct SQL.



The output file now has all the required information to be used with HBR launcher.

A package can be created to bring it all together.



If you generate a scenario from the package and integrate some of the web functionality I have shown how to create in previous blogs, you will have your own HBR web launcher with the benefit of being able to add other ODI elements into the package.

ODI Series - Quick look at user functions

Today is going to be a quick blog on how to create user functions as it is something I have not covered in the past and they can be quite useful, if you have used user functions then this is probably going to be no use to you and you may as well do something far more interesting instead.

User functions allow you define a customised function that can be used in interfaces or procedures, the function can be used across different technologies. They are useful when you use a manually created function across many interfaces; it gives you the benefit that you don’t have to keep typing all the code into each interface.

They work on the same principle as functions in other applications or programming languages, you pass variables into the function, the variables are used to say calculate a value and in the end a value is passed back from the function to the caller.

A function can be created as global so it is common to all projects or it can be defined at project level.

I am going to go through an example of a user function I had to create recently and go the process of how it was defined, it is nothing complex but I find useful.

Here is the conundrum, I have a standard variable that is a text data value e.g. 01/06/09 (UK date format), and this variable is declared when a scenario is executed. I need to generate a numeric value corresponding to the month of the variable and then use it in a calculation. If the month is April then I need to return 1, if the month is March then I need to return 12. Just to add to that the current month to be used in the function is always the prior month of the month entered in the variable, so if it is May in the variable then I want to use April in the function.

This example is based on using Oracle technology.

Before I created the function I had the following code placed in a mapping column on the target Datastore of the interface



So what it is basically doing is converting ‘01/06/09’ to a date format, it is using the function add_months to get the previous month, it then converts the date back to a string, it then uses the CASE function to convert the month to a numerical value.

As I didn’t want to hard code the date I used a variable to hold the current month.



So it could of ended up like



This is fine but it was a piece of code that I was going to use again and again so the best way to handle this was by using a user function.



In this example I am creating a project based function, it is exactly the same process to create a global variable though it is created by going to the Others tab in the Designer.




The fields are :-

Name: Name of the user function e.g. ReturnMonthVal

Group: Group of the user function. If you type a group name that does not exist, a new group will be created with this group name when the function is saved.

Syntax: Syntax of the user function that will appear in the expression editor; The arguments of the function must be specified in this syntax.

The format of the syntax is

<Function Name>($(Variable),$(Variable2)….)

As I am only passing in one value the format is

ReturnMonthVal($(MonthVar))

It doesn’t have to be called MonthVar it can be named anything that is meaningful.



In the Implementations tab click add, now you can enter the code for the function, so I just pasted the code I had used earlier in the interface mapping, the date had to be updated to the variable name defined in the first Syntax screen.



As this example is using Oracle then it was chosen under the Linked Technologies, if the code was acceptable in its current format for other technologies then they could also be chosen.

If the function was going to be used on different technologies and required different code then this can easily be done by just adding a new implementation, entering the code and applying it to a technology.

To use the function in an interface mapping then it can be achieved with



The objective was to pass a variable to the function so the variable created earlier can be used.



Remember though if you are going to use a variable you will need to declare it say in a package, now the function can be used throughout various integrations without having to rewrite the code each time

So there you have it a quick simple example of how to define and put into practice a user function.

ODI Series – Processing all files in a directory

There have been a number of occasions where it has been necessary to process all files in a directory, the files are all of the same structure but could be for different weeks or months and all need to be processed in one session.

Now there are probably a number of ways going about this and I know it might not be your preferred route (just thought I would say it before I get the usual messages from the purists :) ) but this is a method that I believe is worth sharing and it is one I can look back upon once I forget how it is done.

The object of this example is to pick up all the files in a directory and load them into a database table.



An example of the file structure is as follows



The first step is to create a new Datastore holding the structure of one of the files (remember all files must be of the same structure)



I have pointed the Datastore directly to one of the files at the moment, this will be replaced later by using a variable, as we need to load files with different names.

In this example I am loading into a database table so I have already created the table to hold the data and reversed it.



Ok, so now we need an interface to load the data from the file straight into the db table.



Test it works and populates the table.



So now we can load the data we just have to loop through all the files.
Next is to create a variable that will store the current filename.



And now go back to the file Datastore and place the variable name as the resource name, remember variables in ODI start with #
Remember the variables are case sensitive as well.



So this means when the variable is updated with a filename it will load that file in the interface. Though it is important to know that you won’t just be able to run the interface now as the variable has to be declared and this is usually done through the use of a package.

Lets create the package then, all that is required is to declare the variable and then execute the interface to load the data from the file.



Once that has been applied we also need a scenario generated so we call the package when required, right click the package > “Generate Scenario” and accept the default configuration values.



Next we want generate a list of the files that need to be processed and to do this we can use an O/S command, I am working on a windows O/S so you will need to change the command a little if you are using a different O/S.



What this command does is to list all the files in the specified directory and writes the output to a file in another directory, I have used a different directory to write the list to, as I want to keep it separated from the other files that will be processed.

The extra parameters /b means it will run in bare format mode so there are no heading or summary info.

/a:-d means use an attribute of no directories so no sub directory information will be written to the file.

Running the command produces this file.



To be able run this command in ODI a new procedure was created



In the details tab a new command was inserted using the technology “Operating System” and the command pasted into the command window.



If you don’t want to hard code directory names then it is possible to use variables.
You can test whether the procedure produces the file by executing it.

The next thing to do is created a new Datastore pointing to the file just created; it will just contain one column for the filenames.



Ok, now we want to be able to read in all the filenames, store current filename in the filename variable and then call the package to load the data passing in the value of variable.

To do this we need to add another command step to the procedure that was created earlier.



In the Source what we want to do is retrieve all the filename information from the Datastore we just created, this means File technology was selected and the schema that points to the correct directory location.

In the command area we want to enter the command to retrieve the information, I originally thought I could just use SQL like the following,

SELECT loadfile FROM Filenames

When I tried to execute it I got an error about invalid format description, now I am not totally sure if I went about this correct way but what I did was create a quick interface to load the file information into a db table.



I executed the interface and then checked the operator to find what code was generated to retrieve the data from the file.



As you can see ODI generates extra information between /* */ which all the information about the DataStore but in ODI terminology.

So what I did was lift this information and put it into the procedure command window. I am sure this can be achieved by one of ODIs substitution methods but I am not clear which one could be used, maybe I will get some suggestions?



So this will retrieve all the filenames record by record and what we want to do on the target is call the scenario to load the file and pass in the current value of “loadfile”

To do this we can use one of the ODI tools OdiStarScen



So what is happening here is the Scenario “LOADSUMMARYFILES” is executed and the value passed into the variable currFileName is from the current value held on the source “loadfile”
When using a variable make sure you include the project name as well e.g. DEV.currFileName

So executing the procedure will retrieve a filename and then use that filename in the interface to load the data from the file into the db table, it will move on to the next filename and repeat the process until it has processed all the filenames.



You can easily add the procedure into another package to carry out further processes or make it more sophisticated by checking for when the files arrive and then processes them and deleting/archiving them when complete.