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]