ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job failures - troubleshooting

book

Article ID: 196962

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

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

Resolution

Check if it's 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. 

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.

Investigate 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:

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

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

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:

Attachments

1597086274767__dwh_attribute_check_v.sql get_app