Load DWH job failures - troubleshooting

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.

Resolution

Table of Contents

• Full or Incremental Load DWH job failing (or both)?
• Determine where the error is happening
• Review the bg-dwh.logs
• Self Service / Knowledge Base Search
• Generate the Health Report
• Run attached SQL Query (On Premise customers)
• When to Rebuild the DWH
• Additional help

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. 

TIP: Review the following KB: Load DWH Full - When to run to prevent failures to determine if the issue is due to the full Load needing to be run.

Determine where the error is happening

1. In the Clarity UI, go to Administration  → Data Warehouse. 

2. Click on the Load Start Date label to order by the Load Start Date descending. This tells you the table the DWH failed on

3. On Premise customers: You can also check 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

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

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: Data Warehouse (DWH) known issues
• Note: The list of articles contained in the above link is not a full list of KBs for Load DWH issues, so if a match for your issue isn’t found, please also search using the Broadcom support search portal
• Exclude any custom attribute names/fields in the search

Generate the Health Report

Review 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

Run attached SQL Query (On Premise customers)

The SQL query attached to this KB 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

When to Rebuild the DWH

There are at times a rebuild of the DWH may be needed. See the following KB When to rebuild the Data Warehouse (DWH) database for some times where this may be required. 

Additional help

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
• For On Premise please also provide the below:
  • Clarity Version
  • 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

Additional Information

Reference:

• Data Warehouse (DWH) known issues
• Load DWH Full - When to run to prevent failures