I guess all of you are familiar with Planning Text members if you are not; Hyperion Planning allows you to enter free form text in Webforms and Smart View.
Text value gets stored in a relational table HSP_TEXT_CELL_VALUE, and a number TEXT_ID goes into Essbase.
When a user requests for this member with the correct intersection, Essbase sends the number back, then Web/Smart View converts this to the TEXT information.
What we are trying to do is generate text value from a rule, if you are simply looking for an option where the user is entering text, you can always use StringAsNumber variable and prompt them for the text.
The setup works fine for most of the scenarios, except the ones where you are deriving the text using some functions in the Business Rule.
This method is only for cases where you are using the text from a different variable other than StringAsNumber, or let’s say you are concatenating strings.
In this example, I’m trying to set a member of a Text data type to say that the product came from an Entity.
I got a variable that lets the user choose an Entity, and this value is used to set the text value.
I created the following members in Vision application; “FromEntity” to hold the text value, and “DynamicTextRule” to check whether the rule is already running.
The first idea was to perform INSERTS into the underlying table “HSP_TEXT_CELL_VALUE,” the challenge is to refresh the Planning cache so that the values are picked up.
I did try a couple of options to find the correct PRIMARY_KEY and OBJECT_TYPE to insert into HSP_ACTION to force a Planning cache refresh. I gave up because of the inability to make it work.
LCM approach
I’m using LCM to make this work. Import new text values as an LCM file 🙂
Prerequisites
I created an initial LCM for Text Values, copied the folder to a different location
Once I copied it to the new location, I updated the Import.xml file and added a username and LCM encrypted password.
I also update the Text Values.xml file to have only one record.
Calculation Manager Rule
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
"compile"
"-file"
"Y:/groovy_scripts/GetUserName.groovy"
"ruleName"
"Groovy - Dynamic Text Assignment"
;
RUNJAVA com.hyperion.calcmgr.common.groovy.cdf.GroovyCDF
"compile"
"-file"
"Y:/groovy_scripts/AddTextInPlanning.groovy" /* Groovy script */
"appName,planName,varProduct,fromEntity" /* list of variables used in the script separated by commas */
"Vision,Plan1,{Product},{FromDepartment}" /* list of values used in the script separated by commas. */
;
FIX("P_000")
FIX("No Version")
FIX("No Scenario")
FIX("No Year")
FIX("BegBalance")
FIX("000")
"DynamicTextRule"
(
IF("DynamicTextRule" == 1)
@RETURN(@CONCATENATE(@CalcMgrGroovyString("Y:/groovy_scripts/GetUserName.groovy","","ruleName","Groovy - Dynamic Text Assignment") ," is already running this rule, please wait for 30 secs"), ERROR);
ELSE
1;
@CalcMgrGroovyNumber("Y:/groovy_scripts/AddTextInPlanning.groovy", "", @LIST("appName","planName","varProduct",varEntity),@LIST("Vision","Plan1",@NAME(@MEMBER({Product})),@NAME(@MEMBER({FromDepartment}))));
ENDIF
)
ENDFIX
ENDFIX
ENDFIX
ENDFIX
ENDFIX
ENDFIX
In the rule above, I’m using two groovy rules.
- GetUserName – this is used to get the user who is currently running the rule.
- AddTextInPlanning – the main rule that creates a new ID and runs the LCM process to assign the new entry.
To run this rule, you must have an existing block for the following intersection.
Rule, first checks whether DynamicTextRule is set to 1 if it is then don’t run the rule. It then shows a RETURN message with the user name who is running it.
Note: I couldn’t mimic the behavior where two users are running this simultaneously. The occurrence of this happening is pretty thin.
Groovy script for getting the user name
import groovy.sql.Sql
Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )
// Read configuration script for the environment info.
def keyConfig = new ConfigSlurper().parse(new File('Y:/groovy_scripts/Env_Info.config').toURL())
planSchema=keyConfig.serverEnvs.onPrem.planSchema
planSchemaPass=new String(keyConfig.serverEnvs.onPrem.planSchemaPass.decodeBase64())
jdbcURL=keyConfig.serverEnvs.onPrem.jdbcURL
Sql sql=Sql.newInstance(jdbcURL, planSchema, planSchemaPass)
userName=sql.firstRow("""SELECT CASE
WHEN B.FirstName IS NULL THEN B.Name
WHEN B.LastName IS NULL THEN B.FirstName
ELSE B.FirstName || ' ' || B.LastName
END as Name
FROM HSP_JOB_STATUS P
, HSP_USERS A, HSSADMIN.CSS_USERS B
WHERE P.USER_ID=A.USER_ID
AND A.SID=B.IDENTITY_ID
AND p.job_name='$ruleName'
AND JOB_ID =(
SELECT MAX(JOB_ID)
FROM HSP_JOB_STATUS
WHERE JOB_NAME='$ruleName')""" as String)
def retUserName = userName ? userName[0] : 'Missing'
return retUserName
SQL used in the groovy rule is getting the Max JOB_ID with the rule name from HSP_JOB_STATUS table, gets the USERID and matches it to Shared Services CSS_USERS table to get the name, first name, and last name.
If no one is running the rule, then the Calc Manager rule is executing another groovy script to update the text fields.
Groovy script for assigning text value
import groovy.sql.Sql
/* Import groovy XML driver */
import groovy.xml.*
Thread.currentThread().setContextClassLoader( getClass().getClassLoader() )
// Read configuration script for the environment info.
def keyConfig = new ConfigSlurper().parse(new File('Y:/groovy_scripts/Env_Info.config').toURL())
planSchema=keyConfig.serverEnvs.onPrem.planSchema
planSchemaPass=new String(keyConfig.serverEnvs.onPrem.planSchemaPass.decodeBase64())
jdbcURL=keyConfig.serverEnvs.onPrem.jdbcURL
essUser=keyConfig.serverEnvs.onPrem.maxlUser
essPass=keyConfig.serverEnvs.onPrem.maxlPass
maxlKey=keyConfig.serverEnvs.onPrem.maxlPrivateKey
println fromEntity
println varProduct
Sql sql=Sql.newInstance(jdbcURL, planSchema, planSchemaPass)
def getIDForString(sql, stringToFetch){
string = sql.firstRow("SELECT TEXT_ID FROM HSP_TEXT_CELL_VALUE WHERE VALUE='$stringToFetch'" as String)
return string ? string['TEXT_ID'] : "Missing"
}
def getMaxID(sql){
string = sql.firstRow("SELECT MAX(TEXT_ID) as MAXID FROM HSP_TEXT_CELL_VALUE" as String)
int maxID=string ? string['MAXID'] : 1
return maxID
}
def runCalcScript(stringID){
def calcScript = new StringBuilder()
calcScript <<
$/
login $$key ${essUser} $$key ${essPass} on localhost;
execute calculation '
SET AGGMISSG ON;
SET UPDATECALC OFF;
SET EMPTYMEMBERSETS ON;
FIX("FY17")
FIX("Working")
FIX("FromEntity")
FIX("000")
FIX("$varProduct")
FIX("BegBalance")
Plan=$stringID;
ENDFIX
ENDFIX
ENDFIX
ENDFIX
ENDFIX
ENDFIX
'
on $appName.$planName;
logout;
exit;
/$
essmsh.runScript(maxlKey,calcScript.toString())
}
def addString(sql, stringToAdd){
def idOfStringToAdd=getIDForString(sql, stringToAdd)
if (idOfStringToAdd == 'Missing') {
maxID = getMaxID(sql)
now = new Date()
/* parameters */
lcmFolderName= """Dynamic Text ${now.format("yyyyMMdd-HHmmss.SSS")}"""
lcmRootFolder = "E:/Dynamic Text Assignment"
new AntBuilder().copy(todir: "$lcmRootFolder/$lcmFolderName") {
fileset(dir: "$lcmRootFolder/Dynamic Text")
}
// Update Text Values.xml to add the new values
textValueXML = new XmlParser().parse(new File("$lcmRootFolder\\$lcmFolderName\\HP-Vision\\resource\\Relational Data\\Text Values.xml").toURI().toString())
textValueXML.text.@value=stringToAdd
textValueXML.text.@id=maxID + 1
/* write the updated XML file */
textUpdatedValueXML= new File("$lcmRootFolder\\$lcmFolderName\\HP-Vision\\resource\\Relational Data\\Text Values.xml")
textUpdatedValueXML.withWriter('UTF-8') { writer ->
writer.write( XmlUtil.serialize (textValueXML))
}
/* import XML process */
Process impProc=Runtime.getRuntime().exec("cmd /c E:\\Oracle\\Middleware\\user_projects\\epmsystem1\\bin\\Utility.bat \"$lcmRootFolder\\$lcmFolderName\\Import.xml\"")
impProc.waitFor()
runCalcScript(maxID + 1)
return 0
} else {
runCalcScript(idOfStringToAdd)
return 0
}
}
addString(sql, fromEntity)
In the groovy rule, the first thing we are checking is whether the text value exists in the table or not (line 25). If it does exist, get the ID of the Text value and create an Essbase calc script to assign the value to where the text is going (line 101).
If the string doesn’t exist, then the groovy script gets the Maximum value of the TEXT_ID column increments it by 1 (line 34), copies the LCM folder to a new folder with the time stamp (lines 75-82). It then updates the Text Values.xml file with the new ID and new value (lines 84-92) and imports the file to Planning (line 95).
Then it runs the calc script to assign the Max value in Essbase (line 98).
Here is the rule in action
There you go, you can use this similar setup to concatenate text too.