Load DWH Job Failures - Troubleshooting Guide

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

Tips on how to debug Load Data Warehouse (DWH) Job failures to uncover root cause/fix

Environment

Release : All Supported Releases

Component : CA PPM DATA WAREHOUSE

Resolution

See the below table for troubleshooting steps:

ID	Troubleshooting Step	Details
1	Is the Full or Incremental Load DWH job failing or both?	If just the incremental is failing: Try running the Full load Then see if the incremental job completes successfully.
2	Investigate where the error is happening	In the Clarity UI, go to Administration  → Data Warehouse.  Click on the Load Start Date label to order by the Load Start Date descending.  This tells you the table the DWH failed on On Premise customers, if you want to do this via a query: Run SELECT * FROM dwh_cfg_audit ORDER BY dw_load_start_date DESC The first record will have the table that the load aborted on Run SELECT * FROM dwh_cmn_error_message This should give you the error and the statement that was running
3	Review the bg-dwh.logs	The errors can get overwritten quickly, so it's recommended to save the files locally once the failure occurs Check for the most recent error by doing a Search in the files Sometimes you may have to go to the bg-dwh.log.1, bg.dwh.log.2, bg-dwh.log.3 to find the error
4	Self Service / Knowledge Base Search	Once you have as much information as possible regarding the exact error causing the issue, search in the Support Knowledge base for any known issues related to that error: See KB: 141061 for tips on how to optimize use of Self Service for Clarity Exclude any custom attribute names/fields in the search
5	Generate the Health Report to Review for any issues	Ensure Period Data Setup is set correctly under System Option  Ensure Time Slices Setup is showing up green and follow the documentation for prerequisite on Load Data Warehouse Job
6	On Premise customers: The Attached SQL Query can point out the most common issues	The query looks for missing objects and/or attributes that are DWH enabled. It also looks for calculated fields with no calculation
7	If the above doesn't help, open a case with Broadcom Support, providing the following information:	Does the job fail when run Full, Incremental, or both?  Time frame issue occurred Clarity Version For On Premise please also provide the below: bg-dwh.logs (ensure this includes the time frame the issue was reproduced) Output of below queries can also help expedite troubleshooting: SELECT * FROM dwh_cfg_audit ORDER BY dw_load_start_date DESC Run SELECT * FROM dwh_cmn_error_message