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.
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.
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
SELECTATTRIBUTE_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.