How to find out the RTP values for a user-initiated calculation – On-Prem Hyperion Planning


If you read my blog post on How to find out RTP values for a user-initiated calculation, you might be thinking, “Celvin, that is for Cloud, what about On-Prem.” If you were one among those, then this post is for you.

On-Prem gives you the luxury of looking at the backend tables and figure out what exactly happens behind the scene. If you have got that knowledge, you can apply that same for the cloud. However, the cloud keeps you guessing (table structure can be changed, and you will never know).

If you look at Planning tables, you’ll find that the job status information gets stored in two tables.

  • HSP_JOB_STATUS
  • HSP_HISTORICAL_JOB_STATUS

A simple query shows us the results, as shown below.

HSP_JOB_STATUS table
Job results

The history table contains the history data from the JOB_STATUS table.

The USER_ID that you see there is coming from HSP_USERS table, to get the user name, you need to join HSP_USERS with CSS_USERS table under Shared Services schema.

That’s it. You got all the information you need to generate a report to find out the RTPs and their values for a user-initiated calculation.

How it works

Joins, UNION ALL and a recursive SQL will do it 🙂

The SQL query to get the current day’s job status.

SELECT A.JOB_NAME,A.START_TIME,A.END_TIME, A.DETAILS,A.ATTRIBUTE_1,A.ATTRIBUTE_2
, B.FIRSTNAME,B.LASTNAME, B.NAME 
FROM HSP_JOB_STATUS A
, HSSADMIN.CSS_USERS B
, HSP_USERS C
WHERE A.USER_ID=C.USER_ID
AND C.SID=B.IDENTITY_ID

Now we need to find out the parent job id and its name. As you can see this report doesn’t make much sense.

To get the parent job id, we need to write a recursive query, and here is how it looks like.

SELECT P.JOB_ID, P.PARENT_JOB_ID
		,B.NAME,B.FIRSTNAME,B.LASTNAME
		,P.JOB_NAME,P.PARENT_NAME,P.START_TIME,P.END_TIME,P.DETAILS
		,P.ATTRIBUTE_1,P.ATTRIBUTE_2 FROM 
		(
			SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
				  FROM HSP_JOB_STATUS T1
			 LEFT JOIN HSP_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
			 START WITH T1.PARENT_JOB_ID IS NULL
			CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
		)P
, HSP_USERS A, HSSADMIN.CSS_USERS B 
WHERE P.USER_ID=A.USER_ID
AND A.SID=B.IDENTITY_ID
ORDER BY 1

Groovy code in action

Here is the groovy script that helps to create a report like the other post that we looked at.

If you specify a day to return values, then I’m using a UNION ALL to fetch information from the history table too.

with nDays

There is no restriction in nDays; you can pass any number you wish.

I did run it to generate a report from the past 15 days.

import groovy.sql.Sql

// 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
hssSchema=keyConfig.serverEnvs.onPrem.hssSchema
planSchemaPass=new String(keyConfig.serverEnvs.onPrem.planSchemaPass.decodeBase64())
jdbcURL=keyConfig.serverEnvs.onPrem.jdbcURL

def exportJobConsoleReport(reportFile, delimiter, nDays){
    Sql sql=Sql.newInstance(jdbcURL, planSchema, planSchemaPass)
    println "**Create Job Console Report**"
    writeReportFile = new File(reportFile)
    if (nDays){
        def consReportRows = sql.rows("""
                                        SELECT P.JOB_ID, P.PARENT_JOB_ID
                                                ,B.NAME,B.FIRSTNAME,B.LASTNAME
                                                ,P.JOB_NAME,P.PARENT_NAME,P.START_TIME,P.END_TIME,P.DETAILS
                                                ,P.ATTRIBUTE_1,P.ATTRIBUTE_2 FROM 
                                                (
                                                    SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
                                                          FROM HSP_JOB_STATUS T1
                                                     LEFT JOIN HSP_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
                                                     START WITH T1.PARENT_JOB_ID IS NULL
                                                     AND T1.START_TIME <= SYSDATE AND T1.START_TIME >= SYSDATE - $nDays
                                                    CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
                                                    
                                                    UNION ALL
                                                    
                                                    SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
                                                          FROM HSP_HISTORICAL_JOB_STATUS T1
                                                     LEFT JOIN HSP_HISTORICAL_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
                                                     START WITH T1.PARENT_JOB_ID IS NULL
                                                     AND T1.START_TIME <= SYSDATE AND T1.START_TIME >= SYSDATE - $nDays
                                                    CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
                                                                                        )P
                                        , HSP_USERS A, HSSADMIN.CSS_USERS B 
                                        WHERE P.USER_ID=A.USER_ID
                                        AND A.SID=B.IDENTITY_ID
                                        order by 1
                                        """ as String)
        if (consReportRows){
           // Print column information into the file
           writeReportFile.write(consReportRows[0].keySet().join(delimiter)) // keySet gives us the column names
           
           consReportRows.each{result->
                      cols = result.size()
                      // Check whether the column type is CLOB, if it is then get the char stream's text
                      // Groovy ternary operator in action
                      vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text : "${result[it]}")}
                      writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
           }
       }    
    } else {
        def consReportRows = sql.rows("""
                                        SELECT P.JOB_ID, P.PARENT_JOB_ID
                                                ,B.NAME,B.FIRSTNAME,B.LASTNAME
                                                ,P.JOB_NAME,P.PARENT_NAME,P.START_TIME,P.END_TIME,P.DETAILS
                                                ,P.ATTRIBUTE_1,P.ATTRIBUTE_2 FROM 
                                                (
                                                    SELECT T1.JOB_ID, T1.PARENT_JOB_ID,T1.USER_ID,T1.JOB_NAME,T2.JOB_NAME AS PARENT_NAME,T1.START_TIME,T1.END_TIME,T1.DETAILS,T1.ATTRIBUTE_1,T1.ATTRIBUTE_2
                                                          FROM HSP_JOB_STATUS T1
                                                     LEFT JOIN HSP_JOB_STATUS T2 ON T1.PARENT_JOB_ID = T2.JOB_ID
                                                     START WITH T1.PARENT_JOB_ID IS NULL
                                                    CONNECT BY PRIOR T1.JOB_ID = T1.PARENT_JOB_ID
                                                )P
                                        , HSP_USERS A, HSSADMIN.CSS_USERS B 
                                        WHERE P.USER_ID=A.USER_ID
                                        AND A.SID=B.IDENTITY_ID
                                        order by 1
                                        """ as String)
        if (consReportRows){
           // Print column information into the file
           writeReportFile.write(consReportRows[0].keySet().join(delimiter)) // keySet gives us the column names
           
           consReportRows.each{result->
                      cols = result.size()
                      vals = (0..<cols).collect{(result[it].getClass().name == "oracle.sql.CLOB" ? result[it].getCharacterStream().text : "${result[it]}")}
                      writeReportFile.append(System.getProperty("line.separator") + vals.join(delimiter))
           }
       }
   }
   println "**Job completed"                                      
}
println "******"
exportJobConsoleReport("Y:\\groovy_scripts\\ConsoleReportOnPrem.txt", "|" ,"15")
println "******"

I’m using a different approach (sql.eachRows vs. sql.rows) in this script. Line 16

Using sql.rows I’m collecting all the rows and then to get the columns, I’m using the first row; grab the keyset and join them, this happens at line 45.

Line 25 says that if a job that gets initiated won’t have a parent, and that is our starting parent.

Line 26 is where I’m applying the filter for nDays to get past day’s report.

Since the Oracle database stores the DETAILS column as CLOB, we need to check for that and then print the text information.

Line 51 is where this is happening. First, I’m checking whether the column is CLOB; if it is, I’m getting the character stream of the value and then getting the text. All that happens as a one-liner code.

Leave a comment

Your email address will not be published. Required fields are marked *