How to check whether a “User” ran a “Rule” after data entry 15

This entry was posted in Planning SQL on by

I had to face this question at a client site, How can we check if a User ran a specific rule after a data entry.

In our case all were menu driven rules (the rule will run only for the changed data co-ordinates), so I cannot opt for a business rule task list and force them to run it.

Customer was looking for a report which says, this user changed a specific data and he ran the rule (or he didn’t run it).

I was looking at different options on how to implement this and found out two ways to do so.

  1. Enable Auditing for Data changes and Launch Business Rules
  2. Look at the alternate approach.
They could just filter on the “user” and pull up a report which says they did change data and they ran the rule at this time.

You can create two SQL spreadsheets with a filter control to filter the “user”

Prerequisites

  1. Enable Auditing in Planning for Data changes and Launch Business Rules.

Understanding the tables

HSP_AUDIT_RECORDS keeps the audit of modified values 

Key Columns
  • Type – type of the modified object (this depends upon the audit options that is set for the application)
  • ID_1 – mostly name of the modified object (this is null for Audit Options)
  • ID_2 – mostly object_id from HSP_OBJECT table (identifies specific object that was audited, this is -1 for Audit Options)
  • USER_NAME – User who performed the event

SQL Queries

Sheet 1

SELECT ID_1 AS “Form Name”, time_posted AS “Data Posted Time”, user_name AS “User” FROM HSP_AUDIT_RECORDS WHERE TYPE = ‘Data’

Sheet 2

SELECT NEW_VAL AS “Business Rule Name”, time_posted AS “Data Posted Time”, user_name AS “User” FROM HSP_AUDIT_RECORDS WHERE TYPE = ‘Business Rule’

This report will populate the information of “who” changed the data and “who” ran “which” Business Rule.

Alternate Approach  (just for kicks :-))

My first approach to solve this request was to look at the Job Console and populate required information from there.

This information is stored in HSP_JOB_STATUS and I thought of sharing the information that I learned with you.

HPS_JOB_STATUS gets deleted after every Planning re-start, so you’ve to add a Property in Planning to keep the information for days/forever.


JOB_STATUS_MAX_AGE -1, this setting will make sure that the job status table doesn’t get deleted at all.

You can also specify the values in milliseconds for this property, e.g. for 3 days it is 259200000 (1000*60*60*24*3)

Understanding the tables

Key Columns
  • PARENT_JOB_ID – This is the id for the parent job. If the rule is 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.Note: If the rule (Business Rule) is a Business Rule sequence, the sequence itself is treated as a rule and the rules in it will not get populated.
  • JOB_NAME – Name of the job
  • JOB_TYPE – This is the type of the job being executed. Possible values:
    • Calc Manager Rule Set – 5
    • Rules (Calc Manager/Business Rules) – 1
    • Business Rule Sequence – 6
    • RTPs – 4
  • USER_ID –  This is the id for the user who executed the job, comes from HSP_USERS/HSP_OBJECT table
  • RUN_STATUS – current status for the job,  Possible values:
    • 1 – Processing
    • 2 – Completed
    • 3 – Error
  • ATTRIBUTE_1 – For 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 – For rule, this stores the plan type against which the rule is being run. In case of RTPs, this property stores the RTP prompt text.
SQL queries

Sheet 1

SELECT ID_1 AS “Form Name”, time_posted AS “Data Posted Time”, user_name AS “User” FROM HSP_AUDIT_RECORDS WHERE TYPE = ‘Data’

Sheet 2

SELECT a.JOB_NAME AS “Calculation Name”, a.START_TIME AS “Calculation Start Time”, a.END_TIME AS “Calculation End Time”, b.OBJECT_NAME AS “User” FROM HSP_JOB_STATUS a, HSP_OBJECT b WHERE a.USER_ID = b.OBJECT_ID AND b.OBJECT_NAME = ‘admin’ AND a.JOB_TYPE <> 4

Have an error free New Year (Happy New Year)

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

15 thoughts on “How to check whether a “User” ran a “Rule” after data entry”