Inline Calculation rule and syntax checking – Quick Tip # 9 2

What if I tell you that syntax check in Essbase is overrated.

You are probably going to tell me that I’m crazy or stupid, or you might even think that I’m Bill Gates.

I’m not any of those (maybe a bit crazy) :).

You are probably going to hear about this specific solution in one way or another at a later point in time. However, I thought of pointing this out for you (if you are using Groovy to generate Essbase calcs) about an issue that I faced while using On-Prem Groovy functionality.

I was on a project with my big brother, where the calculation scripts for Essbase gets generated as in line calcs (sub calcs triggered using a MaxL script) using a Groovy script.

Business Case

I do have information stored in a relational table, and for every single row in that table, I need to generate a calc script with certain logic. The idea was to use Groovy scripting and call it from a Planning business rule.

The Groovy script will internally run multiple calc scripts using “execute calculation ‘calc command’ on app.db;” MaxL statement.

In my case, this was generating 50k calculations and was taking close to 9 hrs. It is/was crazy, I told you 🙂

It was acceptable for a while since this was something that the client was never able to do with their VBA solution 🙂

However, as you all know, it became unacceptable in a short period.

I had to come up with some magic to reduce the timings. At first, I thought this could not be done.

Later I thought I could combine the scripts (instead of 50k inline calcs) to reduce the login and log off time. Now the issue was with the size of the MaxL statement; it cannot be more than 81,920 characters or bytes.

I started processing a set of rows (20 rows at a time), generate the calc, and prepare the next set.

Once that was done the number of calcs got reduced to 1400. Now I need to reduce the timing, I thought of doing that by running the inline calcs parallelly within Groovy.

That hurdle, too, was solved using GPars.

Timing got improved, and it now takes 4 hours and 45 minutes.

Another issue that we had was once the Groovy script starts running ESSBASE process will hit 100% CPU, and no one else can log in to the system. Now, that is a bummer. We had to fix this issue. I did tell Cameron that “I don’t think I can do anything further to solve this issue.”

I guess we both are stubborn and didn’t want to give up yet. We started looking at the logs and found that the calcs are not taking more than 5 secs (the longest/biggest). Then how on earth it this taking 4 hours.

We both decided to find out what is wrong with the Groovy code. Cameron took a wiser approach when he decided to copy the 5742 line code into an Essbase script and decided to run it from EAS.

As expected, the calc took less than 5 secs to get executed. However, the validation of the script took more than 3 minutes!

We found the culprit. Hooray to us 😀

What happens when you run a CALC String using MaxL, it tries to validate the script before running to find out whether there is an error in the script or not.

That’s exactly what was happening with those 1400 dynamic scripts. Most of the time was spent on validating the syntax, which was taking time, and it was clogging the CPU (ESSBASE process).

Solution

The idea that we used is as follows

• Store the dynamically generated script as a csc file under the database and a local folder (for logging purpose).
• Execute the calc script using EXECUTE CALCULATION App.DB.CalcName; MaxL script. This will make sure that validation is not performed, and it gets processed like a regular calc using ESSSVR.
• Delete the calc script from the database location.

This made Essbase think that the validation was done already as the script is saved under the database location. It mimics what you perform to create a calc script in EAS. You write the script, validate it, and save it (if you don’t perform validation and save directly; Essbase runs a validation before saving) under the database directory.

We got the whole thing to execute in 5 mins! Yes, 4 hours and 45 minutes to just 5 mins.

I still call that Groovy script the “Sonic Script” anytime I need to refer it.

Here is a glimpse of all twelve concurrent Essbase calcs (generated by Groovy) running.