Sunday, 8 February 2009

Generating emails through business rules.

The idea for this blog originates from a post in the Oracle planning forum, as usual it starts off with a simple question and ends up on a trail of a custom defined function.

The question was aimed at the business rules web launcher and the emailing functionality, after you have validated a business rule you are given the option to send an email once completed.



Once the business rule has completed successfully the email is sent to the address provided.



Notice the "From" address of sysop@essbase.com, the question was asked whether this can be changed. I have never really used the web launcher email functionality so I didn’t know the answer but it was time to find out.
I would of thought that it would of picked up the email address from the users profile within shared services and if it didn’t exist then use a default.

The only setting that I could find was the SMTP server name that is contained in the file OlapAdmin.properties that is located in the eas\server directory.



This property can also be set from EAS console.
After delving around in the jar files for the HBR launcher web application I came across Email.class that is part of the package com.hyperion.hbr.common situated in eas\webapps\hbrlauncher\WEB-INF\lib\hbrcommon.jar



In the class there is a method that sets the address of who the email is from but this is obviously not called in the web launcher.

I updated the variable in the class to point to another email address, compiled and placed it back in the web application.



Running the rule again from the launcher still produced an email with the same from address so the class I found was not the one that was being called, after more hunting I found the same class existed in another jar package easserverplugin.jar which resided in the main EAS web application and not in HBR web launcher, updating this class produced an email with the updated address.



So if changing the address of who the emails originate from is important and you are willing to update a little piece of Java then the solution is there.

This problem got me thinking if there were other ways of sending emails from business rules and I knew there was a custom defined function available for essbase that gives you the ability to send emails from calc scripts, seeing as business rules are just glorified calc scripts then this seemed like the right direction to head in. I did have a look at the email CDF that can be downloaded from (here) but I would a go at writing a really simple one to use in conjunction with business rules.

I wanted to be able to use the variable functionality of business rules to set all the properties of sending an email e.g. SMTP server, addresses, subject etc.

To call a CDF in a business rule the command RUNJAVA e.g. RUNJAVA com.hyperion.cdf.emailer “server:smtp.server.com”

The first problem I hit upon was when using the RUNJAVA command it looks like you are not able to use business rule variables as the arguments.



If I changed the variable just for a hard coded value then it would validate, this was no good for what I wanted to achieve because all the properties needed be set by BR variables. Maybe it is possible?

The only way I could see of achieving setting properties would be to create a function and pass them into a Java class method.



With this class I should be able to pass in the email properties and store them ready to be retrieved in the main email class.

The class was compiled and archived into a Jar file then put into the \java\udf under the essbase home directory.

The function required to be registered with essbase and that was done using a maxl command.

create or replace function '@SetEmailProps' as
'org.moretolife.blog.mailProperties.setMailProps(String,String)'
spec '@SetEmailProps(emailProp,emailVal)';

So now I can use the custom function @SetEmailProps in a business rule script passing in two string parameters one as the key and the other as the value.
The only problem is you can’t just use the function anywhere in a script it has to be treated like a Calc command.



Now there is definitely more elegant ways of getting the function to act as a Calc command but I wrote a quick script that fixed down to a small block set and then used an IF command so I could bring the function into play.



The rule validated and ran without errors, so it was time to create a Java class which would retrieve the properties and send the email based on them.



Basically the class collects the email information which has been stored as part of the previous script, it assigns all of the properties to variables and then sends an email based on them. The class was compiled and added to the existing jar.

As I want to use variables in the business I decided that I would have the email subject, the email source and the SMTP server as global variables that will be constant values.



Each of the Global Variables were set up a String types and given a default value, as they are String each of the values have to be enclosed in double quotes otherwise if they are used in a business rule an error will be generated due to invalid object type.

As it is likely an email will be required to be sent in more than one business rule I decided to place the script in a Macro so it could be called from any business rule and parameters passed in.



When the macro is called three parameters are required to be passed into it, the rule name [parm1], the target email address [parm2] and the body of the email [parm3]. I just chose these as parameters to be passed in to demonstrate what can be achieved; they could easily all be set as global constant variables.

Once all the properties have been set in the script the RUNJAVA command is used to call the SendMail class created earlier to send the email.



In a business rule the macro can be inserted and three parameters need to be passed into it, I created three local variables of String type to do this.



Once the variables have been created they can be added to the macro parameters.



Executing the rule will display the variables that have been defined as run-time prompts; once again it is up to your circumstances whether you use local variables or even prompts.



The rule can be executed from EAS, web launcher



or through planning web either attached to a form or directly.



The macro can be called from any rule or even another macro and executed through one of the above methods giving you added useful functionality for not much effort.

3 comments:

Anonymous said...

Mind Blowing Stuff!!!!!!!!I am really Impressed.......

DjSmeadly said...

Does this apply to 11.1.2.1 and up?

John Goodwin said...

it should apply up to 11.1.2.1 as after that business rules are no more.