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)
Parameter | Description |
---|---|
name | Optional*, name of the Groovy rule |
script | Optional*, Groovy script |
varNames | Variable names used in Groovy script |
values | Values for the Groovy variables |
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.
- Trigger for member changes
- 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 2nd 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.
Hi Clevin,
Loving the new functions already, pretty cool!!.
What is interesting is the smart list generation feature which has been a standard functionality in TM1 for ages, it is quite powerful and gives you a lot of room for alternative/efficient design options,, hopefully Oracle will be kind enough to give something similar to PBCS in On Premise planning soon.
Cheers,
Omar
Thanks Omar. It is there already and I’ll talking about that and more in the next two posts which is going to be PBCS.
That would be awesome Celvin!
Hi,
Excellent article. Can we have any insight on part III ? I’m trying to use the smartlist names to copy data from source cost center to a destination one.
Thanks in advance
Part III and IV (maybe V) is going to be PBCS and even if I post this PBCS groovy is only coming (oh well it’ll come 2 days from now ;)) in 17.02. Yeah I’ll try to post something soon.
Great !!! I forgot to mention PBCS in the 1st post. I’m really waiting for the part III to see how to play with the smartlist in PBCS and Calc Manager. Thanks in advance for your contribution !!!!