Update modified cells to relational database using Groovy scripts – Groovy on-premises Snippets Part I 2

I’m working on something bigly, which some you know already and those who attended the partner training all you heard there was “Groovy,” “Groovy” and “Groovy” :). You now know what the bigly one is 🙂

This post is going to be part of a series where I plan to post some Groovy code snippets that can be used in an Essbase calculation script for automating some of the tasks. This was my Kscope presentation, and due to lack of time, I was not able to perform a deep dive into the code.

Keep in mind there is Groovy on EPBCS apps, and then there are Groovy calc scripts. This one is an On-Premises Essbase Groovy calc script. It’ll not work on EPBCS app.

When I wrote about the EPBCS Groovy rule which can look for edited cells and run a calc for only those edited cells a friend of mine called me and asked, “Can it be done on On-premises?” My short answer was “No, it cannot be done.” Even though I said no, I still wanted to try and see if this can be done. Fair warning – this is a technically feasible solution, it might not be the best/optimal solution.

Many of you might have used “Auditing” feature in Planning, ah now you get it!!!

What I did was to enable “Data” auditing in Planning

Once it is enabled, keep in mind to add a process to back up the table and hold minimal entries there.

We got the new data, old data, the dimensionality, and the form name there.

All I did is to create a SQL to perform an UPDATE statement (depending on your requirement you’ll have to change this to an UPSERT) which gives me the max time posted.

As you can see HSP_AUDIT_RECORDS table captures everything there on the form, so if you’ve parent members even they are going to show up here (Quarters and YearTotal above). This can be solved by joined to dimension tables and only updating level 0 members, or if you are okay with parent members, then you are good to go. I had to use REGEX to split the members (AUDIT table keeps everything as a single column 🙁 )

Next set is to create a Groovy rule which will use this SQL and update the external table.

import groovy.sql.Sql
//textid='12'

keyfile=new File('C:/groovy_scripts/Properties/MaxLJavaKeys.txt').readLines()
//MaxL keys are specified as first line
maxlkeys=keyfile[0].split('\\|')

//SQL credentials are specified as third line
sqlkeys=keyfile[2].split('\\|')

//username is 1st argument
usrname=new String(sqlkeys[0].decodeBase64())

//password is 2nd
psswd=new String(sqlkeys[1].decodeBase64())

Sql sql=Sql.newInstance("jdbc:oracle:thin:@localhost:1521:XE", usrname, psswd)

// ltrim and rtrim is done to remove whitespace
// regexp_substr is done to split the string to columns (as audit table stores all members as one column)

sql.eachRow("""SELECT rtrim(ltrim(REGEXP_SUBSTR (t1.id_2, '[^,]+', 1, 1))) AS Scenario
, rtrim(ltrim(REGEXP_SUBSTR (t1.id_2, '[^,]+', 1, 2))) AS Account
, rtrim(ltrim(REGEXP_SUBSTR (t1.id_2, '[^,]+', 1, 3))) AS Entity
, rtrim(ltrim(REGEXP_SUBSTR (t1.id_2, '[^,]+', 1, 4))) AS Period
, rtrim(ltrim(REGEXP_SUBSTR (t1.id_2, '[^,]+', 1, 5))) AS Version
, rtrim(ltrim(REGEXP_SUBSTR (t1.id_2, '[^,]+', 1, 6))) AS Year
, rtrim(ltrim(REGEXP_SUBSTR (t1.id_2, '[^,]+', 1, 7))) AS Product
, new_val as Data from Hsp_Audit_Records t1,
(select id_2,max(time_posted) timeposted from Hsp_Audit_Records where type='Data' group by id_2) idtime
where type='Data' and t1.id_2=idtime.id_2 and t1.time_posted=Idtime.Timeposted""")
{ row ->
updateCounts = sql.withBatch(500, "UPDATE PLANDATAADMIN.VISIONDATA SET DATA =? where SCENARIO=? and ACCOUNT=? AND ENTITY=? AND PERIOD=? AND VERSION=? AND YEAR=? AND PRODUCT=?") { ps ->
ps.addBatch(row.DATA,row.SCENARIO,row.ACCOUNT,row.ENTITY,row.PERIOD,row.VERSION,row.YEAR,row.PRODUCT)
}

}

sql.close()

What I’m doing in this code
4th line of the code reads a property file (you can even use ConfigSlurper)

Username and password are encoded and is a pipe delimited string. Splitting of the string happens in line 9.

Line 12 and 15 are where they get decoded back.

The trick that I did was to provide the external table user select access to HSP_AUDIT tables!!!

Rest is Groovy magic (I’m using the prepared statement to update data in the external table).

Calc Manager rule is straightforward.

All I’m doing is run the groovy rule here.

Here is a video of what happens in real time.

HTH