Nika_Hadzhikidi

Data Warehouse tables are empty - caused by a timezone/clock mismatch

Blog Post created by Nika_Hadzhikidi Employee on Jun 5, 2017

Recently we have had few issues with DWH_INV_HIERARCHY and similar tables not getting updated when the Load Data Warehouse job - Full Load runs.

 

This can happen on both Oracle and MSSQL

 

Symptoms

Load Data Warehouse job - Full completes successfully, but one or more tables have no data in there.

When we check the corresponding PPM source view, it contains data.

 

I.e. DWH_INV_HIERARCHY does not contain data, we have to check the corresponding view (DWH_INV_HIERARCHY_V) on PPM and we see the data is there.

 

Sometimes, on MSSQL it could be caused by DWH_CMN_PERIOD not updating, and in that case, more tables such as DWH_INV_SUMMARY_FACTS could be impacted.

 

Cause:

Data Warehouse job requires the app server clock and the db clock to be the same, if possible, to the second. If there is a time difference between the server that is higher than 30 sec, that could lead to data discrepancies. We have documented this as a requirement and the newest checkinstall would check for any possible discrepancies before continuing with the upgrade.

 

Solution:

  1. Correct the clock on the app or db server to match the other server to the second or as close as possible.
  2. Run Load Data Warehouse job - Full

 

This should resolve your issue and populate your tables.

 

Documentation reference:

https://docops.ca.com/ca-ppm/14-4/en/reference/ca-ppm-jobs-reference#CAPPMJobsReference-LoadDataWarehouse 

Load Data Warehouse

Server Time, Timezone, Date, and Time

Outcomes