Wednesday, 18 June 2014

Reporting on the Planning Job Console

I have recently been exploring various back end databases and tables to expose logging and processing details to an OBIEE dashboard.
I hope to blog on these various areas one at a time with the first being the Job Console for a planning application. 

There isn't a whole lot of interesting information or complexity around the job console data. There is a single table in the planning schema's database called HSP_JOB_STATUS. Note that this is the specific Planning application's schema and not the Planning schema itself that the product is installed into.

HSP_JOB_STATUS

 Columns

The columns in HSP_JOB_STATUS are as follows (details including comments taken from Oracle schema, not mine):
  • "JOB_ID" NUMBER(*,0) - (PK) ID for the job'
  • "PARENT_JOB_ID" NUMBER(*,0) - This is the id for the parent job. If the rule is launched as a part of a rule set, this will have the ID for the rule set job. RTPs will have the ID for the rule to which they belong.
  • "JOB_NAME" VARCHAR2(80 BYTE) - Name for the job
  • "JOB_TYPE" NUMBER(*,0) - This is the type of the job being executed.
  • "USER_ID" NUMBER(*,0) - FK(HSP_USERS). This is the id for the user who executed the job.
  • "START_TIME" TIMESTAMP (6) - Job execution start time.
  • "END_TIME" TIMESTAMP (6) - Job execution end time.
  • "RUN_STATUS" NUMBER(*,0) - Current status for the job
  • "DETAILS" CLOB - Job details
  • "ATTRIBUTE_1" VARCHAR2(255 BYTE) - The information stored in this property varies by job type. In case of rule, this stores the application against which the rule is being run. In case of RTPs, this stores the parent rule/rule set name.
  • "ATTRIBUTE_2" VARCHAR2(255 BYTE) - The information stored in this property varies by job type. In case of rule, this stores the plan type against which the rule is being run. In case of RTPs, this property stores the RTP prompt text.
  • "SERVER_ID" NUMBER(*,0) - ID of server who created job, use to track orphan jobs
  • "PARAMETERS" VARCHAR2(2000 BYTE) - Parameters used in job
Unfortunately there isn't documentation on the possible values of JOB_TYPE or RUN_STATUS however we can make some observations.

 

 JOB_TYPE

I don't think I have obtained all possible values for this column yet however the following lists those that I have:

1 = Business Rule
4 = Variable
5 = Business Rule Ruleset

 

 RUN_STATUS

1 = Job is running
2 = Job completed successfuly
3 = Job failed
4 = Job did not start -

I have made an assumption on RUN_STATUS=4 based on 2 entries in an application I used for sample data. Whatever the exact meaning of this value, it appears to be related to the total failure of the execution of a job  but not relating to something such as a syntax error or input member error which the front-end would stop a job from getting to this stage.

 

USER_ID 

You can use this field to join the HSP_OBJECT table (OBJECT_ID) and obtain the username of the user who executed the job.

Sample Queries

Sample Query 1: Show all executed jobs and their details

SELECT
  jobs.ATTRIBUTE_1 AS Application,
  jobs.ATTRIBUTE_2 AS Database,
  jobs.JOB_NAME,
  CASE jobs.JOB_TYPE
    WHEN 1 THEN 'Calculation'
    WHEN 4 THEN 'Variable'
    WHEN 5 THEN 'Ruleset'
    ELSE CAST(jobs.JOB_TYPE AS VARCHAR(2))
  END AS Job_Type,
  jobs.START_TIME,
  jobs.END_TIME,
  CASE jobs.RUN_STATUS
    WHEN 1 THEN 'Running'
    WHEN 2 THEN 'Completed'
    WHEN 3 THEN 'Failed'
    WHEN 4 THEN 'Not Started'
  END As Run_Status
FROM HSP_JOB_STATUS jobs
INNER JOIN hsp_object Users ON user_id = object_id
WHERE jobs.PARENT_JOB_ID IS NULL AND users.OBJECT_NAME = 'ProcessAdminMan'
ORDER BY jobs.START_TIME

Here is some sample data from a system with names removed. Note that I filtered it to an admin (ProcessAdminMan) used for daily processing and that the database is blank for this job type.

Application Database Name Type Start Date End Date Status
SomeApp
Calc1 Ruleset 17-JUN-14 07.27.04AM 17-JUN-14 07.27.31AM Completed
SomeApp
Calc2 Ruleset 17-JUN-14 07.27.42AM 17-JUN-14 07.36.40AM Completed
SomeApp
Calc3 Ruleset 17-JUN-14 07.36.52AM 17-JUN-14 07.37.20AM Completed
SomeApp
Calc4 Ruleset 17-JUN-14 07.37.31AM 17-JUN-14 07.40.20AM Completed
SomeApp
Calc5 Ruleset 17-JUN-14 07.40.31AM 17-JUN-14 07.49.44AM Completed
SomeApp
Calc6 Ruleset 17-JUN-14 07.49.56AM 17-JUN-14 08.06.21AM Completed


Here is this report on an OBIEE Dashboard (minus a couple of columns). Again I've whited out a few names so there are a few gaps. I'd also apply conditional formatting to add some colour based on the run status.


Sample Query 2: Show the variables used for a calc execution

SELECT
  ATTRIBUTE_1 AS "Calculation",
  JOB_NAME AS "Variable Name",
  DETAILS AS "Variable Value"
FROM HSP_JOB_STATUS
WHERE PARENT_JOB_ID = 51743;

Output would look like:

Calculation Variable Name Variable Value
Calc1 StartDate d20140616
Calc1 EndDate d20140611
Calc1 SomeVar SomeValue
Calc1 AnotherVar AnotherValue

PARENT_JOB_ID is the ID of the calculation script.

And there you have it. Hopefully that helps provide some basic understanding of behind the scenes of the Planning Job Console.

3 comments:

  1. This table appears to truncate after a specific time. We would prefer that this does not happen, do you have any thoughts about this?

    ReplyDelete
  2. Hi there
    How is that HSPJOBSTATUS table accessed?
    Thanks
    Ros

    ReplyDelete
  3. It's a table in the EPM Planning schema that you set up when you install Planning

    ReplyDelete