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

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.

-D for decrypting
MaxL file name
Private key for decryption
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]

Explore more

Explore more

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

Like this:

Related

Leave a ReplyCancel reply

Share this:

Like this:

Related

Leave a ReplyCancel reply