Execute a Calculation script based on Planning unit status – Groovy on-premises Snippets Part III

This entry was posted in Groovy SQL Tricks on by (updated 1863 days ago)

I was on a mission to finish all the posts in my drafts, and I’m somewhat satisfied that I could cover halfway through.

I remember someone asking this on OTN forums, “How can I run a business rule when a Planning unit is approved?”

I used Vision application and will not be covering Planning Unit details 🙂 (I’m tired 🙁 )

I’m going to use Groovy script to achieve this request.

I’m logged in as a user who can approve Planning units.

If you look at Application schema, you’ll be able to see the tables associated with Planning Units. Yep, they all start with HSP_PM_.

HSP_PM_ACTIONS holds all the actions a user can perform, from here we now know that approval action id is 2

HSP_PM_STATES table will tell us all we need to know about different states.

Table of our interest is HSP_PLANNING_UNIT, and this table holds all the details we need for creating a Groovy script. I’m using Account as my secondary dimension.

We can run a quick SQL to get the member names involved from the four dimensions.


select B.Object_Name Scenario,C.Object_Name Version,d.object_name Entity,e.object_name Secondary_Member from Hsp_Planning_Unit a,
hsp_object b,
hsp_object c,
hsp_object d,
hsp_object e
where a.Process_State=3
and A.Scenario_Id=B.Object_Id
and a.version_id=c.object_id
and a.entity_id=d.object_id
and A.Secondary_Member_Id=e.object_id

Now that we have all the required information let’s create a groovy script calc to run a calculation. I’m going to copy working data to final for all the approved Account, Entity, Scenario, and Version combination.


import groovy.sql.Sql

Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )

// Read java and maxl configuration script for the prod environment.
def keyConfig = new ConfigSlurper().parse(new File('C:/groovy_scripts/Properties/JavaMaxl.txt').toURL())

def appname=keyConfig.essbaseEnv.prod.esbApp
def dbname=keyConfig.essbaseEnv.prod.esbDB
def sqluser=new String(keyConfig.essbaseEnv.prod.sqlUser.decodeBase64())
def sqlpass=new String(keyConfig.essbaseEnv.prod.sqlPass.decodeBase64())

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

def now = new Date()

def tmpMaxlfile = new File("C:/groovy_scripts/tmpmxl_${now.format("yyyyMMdd-HH_mm_ss_SSS", TimeZone.getTimeZone('UTC'))}.msh")
tmpMaxlfile.withWriter{ out ->
    out.println ($/
    login $$key ${keyConfig.essbaseEnv.prod.maxlUser} $$key ${keyConfig.essbaseEnv.prod.maxlPass} on localhost;
    execute calculation '
        SET AGGMISSG ON;
        SET UPDATECALC OFF;
        SET EMPTYMEMBERSETS ON;
         
        FIX(&CurYr,
        @LEVMBRS("Period",0),
        @IDESC("P_TP"))
    /$)
        sql.eachRow("""select B.Object_Name Scenario,C.Object_Name Version,d.object_name Entity,e.object_name Secondary_Member from Hsp_Planning_Unit a,
                        hsp_object b,
                        hsp_object c,
                        hsp_object d,
                        hsp_object e
                        where a.Process_State=3
                        and A.Scenario_Id=B.Object_Id
                        and a.version_id=c.object_id
                        and a.entity_id=d.object_id
                        and A.Secondary_Member_Id=e.object_id""")
                     { row ->
        out.println """
                FIX("$row.Scenario",
                "$row.Entity",
                "$row.Secondary_Member")
                   
                    DATACOPY "$row.Version" TO "Final";
                   
                ENDFIX
                    """    
                     }
            

        sql.close() 
        out.println """
        ENDFIX'
        
    on $appname.$dbname;
    logout;
    spool off;
    exit;
    """    
}

    essmsh.runFile(["-D","C:/groovy_scripts/tmpmxl_${now.format("yyyyMMdd-HH_mm_ss_SSS", TimeZone.getTimeZone('UTC'))}.msh","1036072223,1105601687"] as String[])

First, we import the excellent Groovy SQL, read the parameters.
Line 13, I’m connecting to Planning schema and going to write a temporary MaxL script. I’m using time stamp so that it is unique for different users. (I don’t think two users are going to perform a similar action with millisecond precision).
Lines 24 – 30 I’m creating a calc script on the fly.
Line 30 I’m running the SQL and finishing my calc script 🙂

Line 64 we are going to execute the temporary MaxL script, the order of arguments are important, if you are using an encrypted Maxl, follow the order below.

  1. -D for decrypting
  2. MaxL file name
  3. Private key for decryption
  4. Any variables

Here is a sample output of this script.

    login $key 980700984040509848903004677700 $key 2954990220132113241093195557202967062540 on localhost;
    execute calculation '
        SET AGGMISSG ON;
        SET UPDATECALC OFF;
        SET EMPTYMEMBERSETS ON;
         
        FIX(&CurYr,
        @LEVMBRS("Period",0),
        @IDESC("P_TP"))
    

                FIX("Forecast",
                "410",
                "1110")
                   
                    DATACOPY "Working" TO "Final";
                   
                ENDFIX
                    

                FIX("Forecast",
                "410",
                "1150")
                   
                    DATACOPY "Working" TO "Final";
                   
                ENDFIX
                    

        ENDFIX'
        
    on Vision.Plan1;
    logout;
    spool off;
    exit;

I’m going to call this script from a calc script as given below.

</pre>

RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "clear";
RUNJAVA com.hyperion.calcmgr.common.cdf.CDFLogger "level" "FINE";

RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"run" /* compile, run */
"-file"
"C:\groovy_scripts\PMApprove.groovy"
"" /* variables in Groovy Script */
;

There you go, you can run this script, and it’ll look at all approved PUs and copy data to the final version.

Here is the output from CDFLogger.


[2018-02-20 17:52:18] [INFO]  GroovyCDF main: [run, -file, C:\groovy_scripts\PMApprove.groovy, ]
[2018-02-20 17:52:18] [INFO]  Binding essmsh=com.hyperion.calcmgr.common.groovy.cdf.MaxLGroovyShell@235dc721
[2018-02-20 17:52:18] [INFO]  Binding =[null]
[2018-02-20 17:52:18] [INFO]  Binding MaxL=com.hyperion.calcmgr.common.groovy.cdf.MaxLGroovyShell@61bc59aa
[2018-02-20 17:52:18] [INFO]  Binding logger=java.util.logging.Logger@75be16f5
[2018-02-20 17:52:18] [FINE]  [essmsh, -D, C:/groovy_scripts/tmpmxl_20180221-01_52_18_512.msh, 1036072223,1105601687]
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]   Essbase MaxL Shell 64-bit - Release 11.1.2 (ESB11.1.2.4.017B009)
[2018-02-20 17:52:19] [FINE]   Copyright (c) 2000, 2017, Oracle and/or its affiliates.
[2018-02-20 17:52:19] [FINE]   All rights reserved.
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  MAXL> login $key 980700984040509848903004677700 $key 2954990220132113241093195557202967062540 on localhost;
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1051034 - Logging in user [admin@Native Directory].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1241001 - Logged in to Essbase.
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  MAXL> execute calculation '
[2018-02-20 17:52:19] [FINE]     2>         SET AGGMISSG ON;
[2018-02-20 17:52:19] [FINE]     3>         SET UPDATECALC OFF;
[2018-02-20 17:52:19] [FINE]     4>         SET EMPTYMEMBERSETS ON;
[2018-02-20 17:52:19] [FINE]     5>          
[2018-02-20 17:52:19] [FINE]     6>         FIX(&CurYr,
[2018-02-20 17:52:19] [FINE]     7>         @LEVMBRS("Period",0),
[2018-02-20 17:52:19] [FINE]     8>         @IDESC("P_TP"))
[2018-02-20 17:52:19] [FINE]     9>     
[2018-02-20 17:52:19] [FINE]    10> 
[2018-02-20 17:52:19] [FINE]    11>                 FIX("Forecast",
[2018-02-20 17:52:19] [FINE]    12>                 "410",
[2018-02-20 17:52:19] [FINE]    13>                 "1110")
[2018-02-20 17:52:19] [FINE]    14>                    
[2018-02-20 17:52:19] [FINE]    15>                     DATACOPY "Working" TO "Final";
[2018-02-20 17:52:19] [FINE]    16>                    
[2018-02-20 17:52:19] [FINE]    17>                 ENDFIX
[2018-02-20 17:52:19] [FINE]    18>                     
[2018-02-20 17:52:19] [FINE]    19> 
[2018-02-20 17:52:19] [FINE]    20>                 FIX("Forecast",
[2018-02-20 17:52:19] [FINE]    21>                 "410",
[2018-02-20 17:52:19] [FINE]    22>                 "1150")
[2018-02-20 17:52:19] [FINE]    23>                    
[2018-02-20 17:52:19] [FINE]    24>                     DATACOPY "Working" TO "Final";
[2018-02-20 17:52:19] [FINE]    25>                    
[2018-02-20 17:52:19] [FINE]    26>                 ENDFIX
[2018-02-20 17:52:19] [FINE]    27>                     
[2018-02-20 17:52:19] [FINE]    28> 
[2018-02-20 17:52:19] [FINE]    29>         ENDFIX'
[2018-02-20 17:52:19] [FINE]    30>         
[2018-02-20 17:52:19] [FINE]    31>     on Vision.Plan1;
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012553 - Copying data from [Working] to [Final] with fixed members [Account(1110); Period(BegBalance, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, YTD, QTD, ITD, No Period, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Rolling); Year(].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012574 - Datacopy command copied [24] source data blocks to [24] target data blocks.
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012553 - Copying data from [Working] to [Final] with fixed members [Account(1150); Period(BegBalance, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, YTD, QTD, ITD, No Period, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec, Rolling); Year(].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012675 - Commit Blocks Interval for the calculation is [3000].
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012574 - Datacopy command copied [24] source data blocks to [24] target data blocks.
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1012550 - Total Calc Elapsed Time : [0.098] seconds.
[2018-02-20 17:52:19] [FINE]   OK/INFO - 1013274 - Calculation executed.
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  MAXL> logout;
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]        User admin is logged out
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]  
[2018-02-20 17:52:19] [FINE]    MaxL Shell completed
[2018-02-20 17:52:19] [FINE]

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.