Advanced Reporting scheduled report fails while Load DWH job is running

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

After setting up a report to run on auto-schedule, you receive a failure notification.  It appears that it's running at the same time the Load DWH job is scheduled to run which seems to be causing the problem.

Environment

Release: All
Component: Clarity Data Warehouse (DHW)

Cause

This is caused by the DWH load job putting a deadlock on an object which a report is attempting to access.

In the log you see the following:

Caused by: java.sql.SQLException: ORA-04020: deadlock detected while trying to lock object PPM_DWH.DWH_INV_INVESTMENT 

This shows us that there is a deadlock on the object in the DWH at the same time that the report was running.

Resolution

The best way to prevent this from occurring is to have a known rule across your organization that there is a "blackout period" for scheduling reports. Example: between 12am and 1am (or however long your DWH job typically takes - leave a little bit of extra room there as it can fluctuate a bit) no reports should be scheduled.

Additional Information

See also:

• Data Warehouse (DWH) known issues
• Jaspersoft / reports known issues with Clarity