Clarity PPM: SaaS Refresh Data Warehouse OData Model Slow Performance

Document ID : KB000118445
Last Modified Date : 07/12/2018
Show Technical Document Details
Issue:

Why is the SaaS job called the Refresh Data Warehouse OData Model, taking a long time to complete?

It will also fail with following example error:
Refreshing Data Warehouse OData Model...
Job Started 2/25/19 5:50 AM
Message 2/25/19 5:50 AM
OData V2 Model refresh initiated...
Job Completed 2/25/19 6:37 AM
NJS-0401: Execution of job failed.
Error 2/25/19 6:37 AM
Data Warehouse OData Model refresh failed: There was a problem creating the model.Error getting data source. System not available, try again later.

Cause:

The problem can be a combination of a database performance issue and HDP (Hybrid Data Pipeline) server encountering an issue.
 

Resolution:

1. If the job never completes, the SQL execution is not optimal on the SaaS environment.

After profiling the SQL, the job runtime was brought down from hours to within 15 minutes completion time.

The SaaS database solution of ensuring the best SQL execution plan for this job can be applied to any environment for subsequent runs.

The database profiler selects a non-optimal plan based on the ALL_SYNONYMS table, therefore it is best to use USER_SYNONYMS table instead. 

The following SQL is to be profiled:
SELECT SYNONYM_NAME FROM ALL_SYNONYMS WHERE TABLE_OWNER = :v0 and TABLE_NAME = :v1 and OWNER = :v2

An improvement will result as a result of using the SQL Tuning Advisor.

2. If the job eventually fails, the HDP (Hybrid Data Pipeline) server needs to be restarted in addition to applying the best execution plan.

Additional Information:

This is reported as DE42965.

This issue occurs in 15.5.1.0.