Groovy in PBCS and On-Premises Hyperion Planning – Part II, It’s time for SmartLists 6

So you already saw Method 1(if you didn’t please go ahead and take a look J) and how powerful the new feature in 11.1.2.4.008 Calc Manager is.

Something more about these functions which I forgot to mention in my article and the first post.

@CalcMgrGroovyString(name,script,varNames,values)

@CalcMgrGroovyNumber(name,script,varNames,values)

The values passed to Groovy functions has to be all strings. That is because Essbase don’t allow objects. It either allows string array or number array, you cannot mix them.

Let’s say that you want to pass a member name and the value of the member, yes that is an object and it is not allowed, so what Calc Manager team did was to allow string arrays only as values

Inorder to pass numbers you’ll have to use @CalcMgrDoubleToString(mbrname) and then convert them back to Double in your Groovy Script using Double.parseDouble(str).

Let’s jump to Method 2. Well isn’t it kind of obvious that I’m going to talk about Smartlists now ;). Yes Method 2 is about doing the same thing except that we are going to use Smartlist.

Method 1 had its own issues like user typing in a non-existing entity member. (It was shown to demonstrate that text data type matching can be done easily in Calc Manager now)

Method 2

As I said earlier Method 2 uses a Smartlist and user will be only allowed to select from a pre-defined set of members. Now this method raises one question, how is the Smartlist going to be maintained?

If metadata is managed centrally (sourced from a relational/DRM et al) then it is easier to update both metadata and the metadata-Smartlist (I just made that up 😉 ). Now what if the metadata is managed directly in Planning!!!!

Auto populate Metadata-Smartlist

Here we’ll have to mimic what PBCS does. If you don’t know about hierarchy dirven Smartlists read about it here.

What we are going to do is create a mechanism which can automatically generate a smarlist when a new member is added/updated/deleted in a hierarchy.

Since we’ve the luxury to look at the Planning tables (all because of on-premises) we can create procedures, and triggers. Aha you are getting the idea now.

The below given procedures and triggers are created in a different schema (this is not using Planning application schema)

Procedure for generating the file for Metadata-Smartlist

What I’m doing in this procedure is to run a select statement to generate a file of the following format

ID, NAME, LABEL

Well we’ll need more than that, however we’ll get stick to the select statement now.

As you can see from the above SELECT statement I’m auto-generating an ID, member name becomes my NAME and member alias becomes my LABEL. I’m looking for only level 0 members under “Total Entity”.

In PBCS this will be similar to

Now that the select is ready and we are talking about making this automatic we’ll have to generate a file using this select statement.

I’ll be honest with you, I had to dig deep to find out how it can be done and here is what I did.

First I created a folder where I can write the file. (easy enough)

This is all done on an Oracle database, so SQL guys sorry about that 😉

Issue the below given SQL statements to create a directory in Oracle.

create or replace DIRECTORY SMARTLISTPROC as 'C:\Entity_Smartlist';

grant read,write on DIRECTORY SMARTLISTPROC to SYSTEM;

create or replace PROCEDURE ENTITYSMARTLIST
IS
out_File UTL_FILE.FILE_TYPE;
CURSOR sList
IS
SELECT ROWNUM AS ID ,'_'||B.OBJECT_NAME NAME,
CASE WHEN A.OBJECT_NAME is NULL
THEN B.OBJECT_NAME
ELSE A.OBJECT_NAME END LABEL
FROM SMPPLNADMIN.HSP_OBJECT A,
(select member_id,alias_id from SMPPLNADMIN.HSP_ALIAS where aliastbl_id = 14) C,
(SELECT
OBJECT_NAME ,
OBJECT_ID,
CONNECT_BY_ISLEAF ISLEAF
FROM
SMPPLNADMIN.HSP_OBJECT
WHERE CONNECT_BY_ISLEAF=1
CONNECT BY PRIOR OBJECT_ID=PARENT_ID
START WITH OBJECT_NAME='Total Entity') B
WHERE B.OBJECT_ID = C.MEMBER_ID (+)
AND C.ALIAS_ID=A.OBJECT_ID (+);
BEGIN
out_File := UTL_FILE.FOPEN('SMARTLISTPROC', 'EntitySmartlist.txt' , 'W');
UTL_FILE.PUT_LINE(out_File ,'SmartList Name, Operation, Label, Display Order, Missing Label, Use Form Missing Label, Entry ID, Entry Name, Entry Label');
UTL_FILE.PUT_LINE(out_File,'SL_Entity,addsmartlist,Entity,2,,true,,,');
FOR cur_rec IN sList
LOOP
UTL_FILE.PUT_LINE(out_File ,'SL_Entity,addentry,,,,,'||cur_rec.ID||','||REPLACE(cur_rec.NAME,' ','_')||','||cur_rec.LABEL);
END LOOP;
UTL_FILE.FCLOSE(out_File);
END ENTITYSMARTLIST;

I’m using the UTL_FILE option to create a file.

A cursor is created and a file is written!!!! violaaa

Once the procedure is executed you’ll be able to see a file with the following format.

Now that we got our file, how are we going to know when to run this procedure. This is where the triggers are coming into play.

Triggers for starting the file generation process

What we are going to do is create two triggers in a relational database.

1. Trigger for member changes
2. Trigger for alias changes

Trigger for member changes

create or replace TRIGGER PLNMBRTRIGGER
AFTER INSERT OR UPDATE OR DELETE ON SMPPLNADMIN.HSP_MEMBER
FOR EACH ROW
WHEN (NEW.DIM_ID=33 OR OLD.DIM_ID=33)
BEGIN
ENTITYSMARTLIST();
END;

“33” is the ID of Entity dimension, All I’m saying there is watch for any changes in HSP_MEMBER table and if that change is done on Entity Dimension, execute the procedure. You are free to expand this to your liking.

Since we are using aliases as label, we’ll need to watch for any aliases changes too, that is what our 2^nd trigger is for.

Trigger to watch for alias changes

create or replace TRIGGER PLNALIASTRIGGER
AFTER INSERT OR UPDATE OR DELETE ON SMPPLNADMIN.HSP_ALIAS
FOR EACH ROW
WHEN (NEW.ALIASTBL_ID=14 OR OLD.ALIASTBL_ID=14)
BEGIN
ENTITYSMARTLIST();
END;

“14” is the ID of “Default” alias table. What we are doing here is watch for any change in HSP_ALIAS table and if that change is done on Default alias table, execute the procedure. You can expand it to check whether it was only done on Entity dimension (I was too lazy to do that)

So any updates (adding a member/alias or updating a member/alias or deleting a member/alias will run the above given procedure.

Now that we got everything setup, uploading this in Planning is easier, you can use OutlineLoad.cmd.

What I did in-order to completely automate this was to create a bat file which acts like a file watcher.

IF EXIST C:\Entity_Smartlist\EntitySmartlist.txt (
call C:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1\OutlineLoad.cmd -f:C:\Oracle\Middleware\user_projects\epmsystem1\Planning\planning1\password.epw /A:Vision /U:admin /I:C:\Entity_Smartlist\EntitySmartlist.txt /DS:HSP_SMARTLISTS /L:C:\Entity_Smartlist\SmartlistLoad.log /X:C:\Entity_Smartlist\SmartlistLoad.exc /-H /-O
REM for %%I in (SmartlistLoad.exc) do @echo %%~zI
DEL /Q C:\Entity_Smartlist\EntitySmartlist.txt
)

I can schedule this bat file to run every 5 minutes or every hour. If it finds the file, it’ll update my MetadataSmartlist.

Only change on Calc Manager side compared to Method 1 is going to be done on Groovy side. You’ll have to find out what is the ID of your smartlist from HSP_ENUMERATION table.

Groovy Script

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

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

String destentmbr

try{
sql.eachRow("select NAME from HSP_ENUMERATION_ENTRY where ENUMERATION_ID=18 and ENTRY_ID="+textid){
destentmbr = it.NAME.replace('_','')
if(destentmbr.isDouble())
destentmbr = Double.parseDouble(destentmbr).longValue().toString()
}

} finally {
sql.close()

if(sourceentmbr!=destentmbr) {

essmsh.runScript("""
login admin password on localhost;
execute calculation '
SET AGGMISSG ON;
SET UPDATECALC OFF;
SET EMPTYMEMBERSETS ON;

FIX("Plan",
&CurYr,
"Working",
@LEVMBRS("Period",0),
@REMOVE(@DESC("Account"),@RELATIVE("FlexAccounts",0)),
@IDESC("P_TP"))

DATACOPY "$sourceentmbr" TO "$destentmbr";
CLEARDATA "$sourceentmbr";

ENDFIX
'
on Vision.Plan1;
logout;
exit;
""")
}
}
return destentmbr

Line 11 we are looking at HSP_ENUMERATION_ENTRY table and getting the NAME of the Smartlist, which is nothing but the member name of destination entity.

Line 14 I’m removing the “_” from member name. (NAME of a Smartlist must be alphanumeric and that is the reason why _ was added, you don’t have to do this if the member names are alphanumeric)

Line 22 onwards Groovy is now executing a calc script based on the destination entity it got from the table.

Here is the video of me using the second method to transfer data.

Part III is going to be how this can be done in PBCS with and without Groovy.