Understanding Audit Jobs

Some PeopleSoft Campus Solutions source table records do not have a date time stamp field. When source table records lack a date time stamp, a cyclical redundancy check (CRC) must be performed during the ETL process to determine new or changed records. Unlike the traditional date time lookup process which targets the DTTM column for each record, the CRC process reads the entire record for each record in the source table and generates a CRC value to compare against the target warehouse record. Because the CRC process is so extensive it can create performance issues with the OWS jobs that use the logic. However, if you want faster processing you can use PeopleSoft delivered audit jobs as an alternative.

Audit jobs have the same functionality as the OWS jobs but employ a different incremental load strategy that allow them to process faster than their counterparts. Audit jobs use audit records created in the source table. Audit records are associated with a date time stamp field and are created when you enter a new source record or modify an existing source record. The audit jobs join the parent record and audit record to make use of the date time stamp field and determine new or changed source records. Since the audit jobs only process modified or added records, they process much faster than the OWS jobs which use the CRC logic.

Audit tables include only the key fields of the base table plus three additional audit-specific fields:

• AUDIT_OPRID: Identifies the user who causes the system to trigger the audits, either by performing an add, change, or delete to an audited field.

• AUDIT_STAMP: Identifies the date and time that the audit is triggered.

• AUDIT_ACTN: Indicates the type of action the system audited. Possible action values include:

  • A – Row inserted.

  • D – Row deleted.

  • K – Row updated, snapshot before update.

  • N – Row updated, snapshot after update.

For example the audit job J_STAGE_PS_CA_STATUS_TBL_AUDIT contains the base table PS_CA_STATUS_TBL and the audit table PS_AUDIT_CA_STATUS_TBL. The following table compares the fields in the base table with the fields in the audit table:

When bringing your source data into EPM using ETL, you can use either the audit jobs or the existing OWS jobs that use the CRC logic. You can also make use of both; for example, if you discover that only five jobs process slowly due to the CRC logic, you can implement the audit jobs for these five jobs only and continue to run the remaining OWS jobs with CRC logic. If you want to use audit jobs, you must import the appropriate .dsx file and project, build the records related to the audit jobs, and define audit triggers for the audit records you build. These steps are documented in detail below.

You can access all PeopleSoft delivered audit jobs from the following IBM WebSphere DataStage location:

• FMS Warehouse - FMS_E, OWS, Base, AuditJobs, Server.

• CS Warehouse - CS_E, OWS, Base, AuditJobs, Server.