Groovy, E/PBCS, and Random() stuff....

My last few posts were talking about EPBCS and the newest (most powerful and exciting) feature added recently to the cloud which is Groovy scripting! My background is computer science and naturally, I was super excited about the latest addition and started instantly writing scripts and playing around with it.

Thanks to Celvin Kattookaran who read the Groovy posts and his valuable feedback, he suggested a simpler way of writing the code, and after we exchanged some comments it turned out I was using an old version of the API and he got me in touch with the Oracle Calculation Manager development team, who were kind enough (Thank you Ujwala Maheshwari!) to call me and give me some updates and insights on the latest Groovy scripting feature, as well as the current API library and some of the examples provided. So I thought I should share some of the points/findings:

Some of the pre-built EPBCS Groovy rules are written using the old API version

This is going to be updated soon (maybe as early as August 2017), Good thing that the API library has a lot of useful examples, to access this library go to Academy Groovy Javadocs and simply enjoy! The documentation provided by Oracle here is superb, very clear, powerful and well-written examples, this must be in your browser's favorites

Groovy is simpler than Java

I come from computer science background, I learned to program first using low-level languages (Assembly) and then moved to high-level languages (Pascal, C, VB and Java), why am I mentioning this? because you're very likely to start scripting in Java and totally forget how awesome and groovy Groovy the language is! So what is wrong with writing code in Java? Nothing, but if you can achieve the same result with fewer lines that look more aesthetic then why not?

I will give a very simple example to highlight the above, consider a script that prints Hello World and the current day, this is the first thing you'll learn how to write in any programming language, I'll write the same in Java and Groovy and see where they are different.

Java

1 2 3 4 5 6 7 8 9

import java.util.Date; import java.text.*; public class Main{ public static void main(String[] args) { DateFormat dateFormat = new SimpleDateFormat("EEE, MMM d, yyyyy"); Date date = new Date(); System.out.println("Hello, World! Today is: " + dateFormat.format(date)); } }

Output

Groovy

1 2 3 4

import java.text.* DateFormat dateFormat = new SimpleDateFormat("EEE, MMM d, yyyyy") Date date = new Date() println("Hello, World! Today is: " + dateFormat.format(date))

Output

So, what do you think? Groovy certainly looks nicer and more concise, I wanted to add the date bit because I wanted to highlight some differences between Java and Groovy in the given example:

1. In Groovy, java.util package is imported by default, hence the import line is missing from my Groovy script unlike Java where you need to import every package/class you intend to use.

2. In my Groovy script I don't have a class and method declaration like Java, so if you just want to print Hello World you just need

println("Hello World!")

3. To get formatted dates, you need DateFormat and SimpleDateFormat classes, in both Groovy and Java you need to import the classes

4. Another difference is the semi-colons, in Groovy I don't need to end my lines with ";"

For a more detailed list of differences click here



You can write a perfectly functioning Groovy script and then realize it's really a Java script

One of the first scripts I wrote when I was testing Groovy in EPBCS was a data validation script to prevent users from entering negative values, so the first time I wrote the code it was exactly like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

EPMApplicationShell appshell = new EPMApplicationShell() DataGrid grid = null; // exit if rule is executed from Rules launcher try { grid = appshell.getCurrentGrid() } catch(BindingsMissingException) { return '''BegBalance ( @Return("Please run the rule from the dataform"),Error); )''' } //validate negative value GridIterator itr = grid.dataCellIterator() itr.each{ DataCell dc = it if ((dc.isEdited()) && (dc.data <0)) { dc.addValidationError(0xFF6347,"Cannot enter negative numbers") } }

Lines 1 to 10 are there to make sure the script is launched in a form/grid and not Rules launcher, I borrowed this code from Trend Calculation Groovy rule in PBCS, and apparently this is the alpha version and we no longer need to define appshell variable or write the try catch statement. (leave it for now, I'll come back for it).

Lines 11 to 18 Invokes a Grid Iterator and checks edited data cells, and if negative values are found then throw an error.

Now, the following is the refined code:

1 2 3 4

// Validate negative value operation.grid.dataCellIterator{DataCell cell -> cell.edited}.each { DataCell cell -> if ((cell.data < 0)) cell.addValidationError(0xFF6347, "Cannot enter negative numbers") }

Oracle Calc Manager were kind enough to re-write my code and they explained the redundant bits.

Lines 1 to 10 are no longer needed and is simply replaced with "operation.grid", so when you try to run the rule above from Rules Launcher it will throw an error "grid not found" because of operation.grid.* which assumes there is an active grid, otherwise throw error and break.

Lines 12 to 18 can be replaced by a simple Closure block, coming from Java background the concept is still new to me, but that is what makes Groovy beautiful and easy to use!


Groovy in Action

Want to learn more about Groovy? Check this out, I'm enjoying this book and most importantly it is really fun to read! It is not written in a typical academic computer science text book style, which means it is fun to read, I don't remember ever enjoying reading my text books back at university. (You can get a combo deal at manning publications which includes a pdf, epub, and soft cover paper version)

What kind of subscription you need to have Groovy your POD? PBCS? PBCS + 1 module? or EPBCS?

Thanks to Shankar Viswanathan  Oracle Planning Product Management, I got this point clarified:

Groovy is technically made available for EPBCS App type which is different from saying it is only for EPBCS. There are three SKUs that customers can buy: PBCS; PBCS + 1 module option; EPBCS. If you buy the PBCS +1 module option you can still deploy the application as EPBCS App Type or convert from PBCS App Type to EPBCS App Type. It is just that contractually you can't deploy more than 1 module when you buy PBCS + 1 module. To get Groovy all you need is to have EPBCS App Type. To use groovy you don't even have to deploy any modules once you are in this EPBCS App Type. So technically it is available for PBCS as long as at least the PBCS + 1 module option is bought by the customer. You could be using just the custom cubes in EPBCS App Type and still use Groovy. There is no current plans to make it available for standalone PBCS. Hope this can be clarified in your future posts.



Yes, that is right you can have Groovy with PBCS as long as you go for the plus module option, if you ask me is it worth it? my answer is...

YES         YES       YES      YES

Groovy and Data Validation in EPBCS - Quick Tip

This post is about simple data form validations, preventing users form entering negative amounts in certain members, we all familiar with that request, we used to achieve this pre Hyperion Planning 11.2.2 by changing the java script files. Now thanks to EPBCS's Groovy capabilities, it is a piece of cake in the cloud! No need to locate server files or any of the hassle we used to go through, we just write a simple script and that's it!

To keep it simple and lite, I'll stick to my usual approach and use a simple form (minimized version of Product Revenue form in EPBCS's Financials dodel). The same form I used for my previous post.

I'll start with my Groovy script, it's pretty simple and straightforward.

1 2 3 4

// Validate negative value operation.grid.dataCellIterator{DataCell cell -> cell.edited}.each { DataCell cell -> if ((cell.data < 0)) cell.addValidationError(0xFF6347, "Cannot enter negative numbers") }

Layout of my data form:

I have two business rules attached to the form (one business rule and one Groovy script to be precise)

GroovyValidate is my validation script and OnSave is a simple BR that calculates Product Revenue (Volume multiplied by Average Price), and OBVIOUSLY, I'll run both rules after save.

Let me enter some data and confirm it's working:

You can see above I was able to save the negative values which means there is something wrong either with my script or the form. I ruled out the script because it's a simple if condition that is definitely working, so what is wrong exactly? Ok see below the data form execution options for the business rules, we have Before Save and After Save!! Interesting!

By defaults and after a decade working on Hyperion Planning, almost 90% of the cases I had to run the rule on save, and that was my obvious and default choice, well I was wrong! I corrected this and selected Run Before Save

And now I can't enter negative values:

Notice the form did not save and the cell March->Average Selling Price is still in edit mode, so is Volume -> Mar but because I'm highlighting the cell you can't tell. And If I hover the mouse over the highlighted cell I can see the tool-tip.

All I have to do now is simply change the negative value and I can proceed with my work.

What happens if I enter data in all cells will it work and randomly enter negative values here and there?

The answer is yes, it works just fine!

Remove the negative values and that's it!

Voila!

Another reason to...💕 EPBCS 

Update

Celvin Kattookaran was kind enough to highlight I was using an alpha version of the API library, and he hooked me up with the Oracle Dev team responsible for Calculation Manager/Groovy, I had a call with Ujwala Maheshwari from Oracle and she was kind enough to go through my code and highlight the changes, and recommended another and better way to write the same code which I will elaborate in future posts. If you look again at the code in this post you'll notice some changes from the initial version (the changes and recommendations will be explained in a separate post in the very near future)

Clarification:

I got a comment from an anonymous  user in the previous post, presumably from Oracle, so I'm going to quote it, it clarifies an assumption I made earlier that Groovy is only available for EPBCS, which is not the case...

Quote Start

A point of clarification: Groovy is technically made available for EPBCS App type which is different from saying it is only for EPBCS. There are three SKUs that customers can buy: PBCS; PBCS + 1 module option; EPBCS. If you buy the PBCS +1 module option you can still deploy the application as EPBCS App Type or convert from PBCS App Type to EPBCS App Type. It is just that contractually you can't deploy more than 1 module when you buy PBCS + 1 module. To get Groovy all you need is to have EPBCS App Type. To use groovy you don't even have to deploy any modules once you are in this EPBCS App Type. So technically it is available for PBCS as long as at least the PBCS + 1 module option is bought by the customer. You could be using just the custom cubes in EPBCS App Type and still use Groovy. There is no current plans to make it available for standalone PBCS. Hope this can be clarified in your future posts

End Quote

On the fly Calculation Scripts in EPBCS with Groovy!

So the most exciting thing recently (at least for me) was the introduction of Groovy scripting for EPBCS. I wrote an introductory post here and another LinkedIn article Along Came Groovy that shows how powerful this thing could be.

In this post I want to show how powerful Groovy scripting can be, and how can we ON THE FLY perform focused calculations on a specific data combination that is not possible with normal calculation scripts. The example given below is quite simple and straightforward, in more complex scenarios you need to take into considerations a couple of things when it comes to Groovy, most importantly it is Groovy/Java after all and not Essbase calculation script! you don't want to write a script that takes ages to execute. The concept of block/cell calculation mode is not really applicable here.

I will start with my Product Revenue data form, a simple form that allow users to enter product volume and average selling price. and calculates product revenue based on the entered assumptions (Volume * Avg price).

I also have a business rule (Groovy rule) attached to the form to run after save. Typically a business rule will simply perform the calculation (Volume * Price) for all cells regardless if data was changed or not, so in this case I want to show how to perform the calculation (in this instance Volume * Price) based on dirty cells (cells that has been changed by the user). I entered some data in the form prior to attaching the business rule because I want to show how the rule will only calculate the revenue for changed cells.

Ok, let's start with the interesting stuff; my full Groovy script which is pretty much tailor scripted to the data form I'm testing with, but it should be enough to demonstrate what is this post is all about.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

// iterate over edited cells def uniquePeriodNames = operation.grid.dataCellIterator{DataCell cell -> cell.edited}.collect([] as Set,{ DataCell cell -> cell.getPeriodName(MemberNameType.ESSBASE_NAME) }) if (uniquePeriodNames.size() == 0){ println("No cells were edited") } else{ List<String> fixMemberNames = operation.grid.pov*.essbaseMbrName List<String> editedPeriods = uniquePeriodNames String calcScript = """ Fix("${fixMemberNames.join('", "')}") Fix("${editedPeriods.join('", "')}") "OFS_Calculated" ( "OFS_Product Revenue" = "OFS_Volume" * "OFS_Avg Selling Price"; ) EndFix; EndFix;""" println("The following calculation script was executed by: " + operation.user.fullName + "\n" + calcScript) return calcScript.toString() }

I'll briefly describe what the script is doing for those of you who are not familiar with programming.

Iterate grid and get edited Periods.

// iterate over edited cells
def uniquePeriodNames = operation.grid.dataCellIterator{DataCell cell -> cell.edited}.collect([] as Set,{ DataCell cell ->
    cell.getPeriodName(MemberNameType.ESSBASE_NAME)
})

Performing the calculation for the selected POV and edited cells, if the user did not enter any data then nothing will happen.

if (uniquePeriodNames.size() == 0){
println("No cells were edited")
}
else{
List<String> fixMemberNames = operation.grid.pov*.essbaseMbrName
List<String> editedPeriods = uniquePeriodNames
String calcScript = """ 
Fix("${fixMemberNames.join('", "')}")
Fix("${editedPeriods.join('", "')}")
"OFS_Calculated"
(
"OFS_Product Revenue" = "OFS_Volume" * "OFS_Avg Selling Price";
)
EndFix;
EndFix;"""
println("The following calculation script was executed by: " + operation.user.fullName + "\n" + calcScript)
return calcScript.toString()
}

Now, let us get back to the data form, I'll save the form without entering data.

As you can see above, it looks like the business rule was run after save "Rule was run successfully", but in my script I instructed the rule not to do anything if there are no changes, and here is the job console to show that is exactly what happened.

So now we know the rule will only work if only there is a change in the data, so I'll change the Volume value in "Jan" and save.

And the job console:

Taata! that's on the fly calculation scripts in EPBCS, the first fix will contain the POV combination, and the next fix will contain the edited periods.

Now, I will change the values of four more periods and confirm the rule will work with more than one edited period.

The job console:

The calculation script is dynamic and changing based on the changes in the data form. Ok so what happens if I enter data in "Average Selling Price" account instead of "Volume".

You can see the Product Revenue Account is getting calculated for "Jun" to "Dec", that's mainly because in my Groovy script I'm iterating through the available data cells without specifying any member (for more complex cases you can iterate a specific member's data cells in the grid), and here is the script that got executed from the job console in case you're interested

Finally, just to confirm the POV selections are getting selected properly, I'll change the year POV selection from FY16 to FY17 and enter data in all periods

And finally the job console to show the calculation script,,,

Voila! that's it, I know the example given here is quite simple, but it should be enough to give you a taste of Groovy & EPBCS (in the near future, I hope PBCS). And you can see how powerful this stuff can really be! It is simply AWESOME! and frankly, I'm happy to say...


Update:

Celvin Kattookaran was kind enough to highlight I was using an alpha version of the API library, and he hooked me up with the Oracle Dev team responsible for Calculation Manager/Groovy, I had a call with Ujwala Maheshwari from Oracle and she was kind enough to go through my code and highlight the changes, and recommended another and better way to write the same code which I will elaborate in future posts. If you look again at the code in this post you'll notice some changes from the initial version (the changes and recommendations will be explained in a separate post in the very near future)

I 💓 EPBCS

Oracle Planning & Budgeting Cloud June 2017 Update! Groovy Scripting!

It's been a while since I last wrote, I've been doing a rigorous  running program for the past couple of months in order to prepare for an ultra-marathon, and as a result I end up very tired after finishing work and my daily run! I'll try and get back to writing more frequently!

I've been waiting (like so many of you) for the latest PBCS/EPBCS June release on Test environments to play around with the latest features! Oracle published the list of updates here June 2017 Release,,, for me the thing that caught my attention is the introduction of Groovy scripting in Calculation Manager, the first one to write and elaborate on that was Celvin.

So today I finally got to play around with the feature and did write a very simple "Hello World" kind of script just to test and it was really fun!

So now you can go "Rules" under "Create and Manage", create a new script, switch to Script mode, on the right side next to Jump to line you have Script Type as shown below.

Now, I copied one of EPBCS's pre-built Groovy scripts and changed it to simply return an error message to indicate whether the rule was executed from the "Rules" launcher or the "Dataform" business rules launcher. Here is the script

The script is very simple, it will first check if there is a data grid from which this script is triggered and return a simple message. In order to execute a set of commands you need to use Groovy return function either in the simple way shown above, or by using a StringBuilder. I have yet to look at the Groovy documentation that should be part of the new release, it should describe in details the available functions and methods etc.

So now, I'll go and run the rule from "Rules" launcher

I'll get the following message...(The same concept is already applied in some of the pre-built EPBCS rules)

And If I run the rule from a dataform (any dataform)...

I'll get..

So it's officially here, Groovy scripting in the cloud! It's been there for a while for on-premise Planning, so it's really exciting to have it in the cloud.

Now we have a glimpse of the future of scripting, exciting times ahead :)