Load Data Warehouse fails after exceeding timeout setting

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse job, full load, fails after exceeding the timeout setting specified in the NSA / CSA. It is set to 700 minutes, which is more than the default value.

Environment

Version: 15.9.3

Cause

DB latency issues. It was noticed that one or more table took inordinate amount of time to process.

Run the following query to fetch details about data load times against each table in the Data Warehouse. Note that the query is for an Oracle DB. It will need modification to execute against SQL Server and Postgres. The query is to be executed against the DWH DB.

SELECT
    dgmv.table_name,
    dgmv.table_type,
    dgmv.population_time_sec,
    dgmv.record_count,
    CAST(dgmv.dw_load_start_date AS TIMESTAMP) dw_load_start_date,
    CAST(dgmv.dw_load_end_date AS TIMESTAMP) dw_load_end_date,
    CAST(dgmv.dw_updated_date AS TIMESTAMP) dw_updated_date
FROM
    dwh_gather_metrics_v dgmv
ORDER BY dw_load_start_date DESC

Resolution

Engage a DBA to analyze the high load times.

• The DB performance might have to be generally optimized
• Ensure that the DWH DB has all the parameters set as per the documentation
• There can be one or more slow query that might need tuning. An AWR report can be extracted to identify such queries