DWH_TME_ENTRY_FACTS table is missing data

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

The DWH_TME_ENTRY_FACTS table is missing data. There should be multiple entries for every week for each person entering their time.

Cause

This can happen if there was any recent opening and closing of time periods

Run the following query to see if there is any missing data:

select * from DWH_TME_ENTRY_FACTS_V

Environment

Release: All
Component: Clarity Data Warehouse (DWH)

Resolution

Step 1: Make sure the full Load DWH job has recently completed

Run the following query to validate:

SELECT dwh_tme_entry_facts.PERIOD_KEY, 
dwh_tme_entry_facts.TIME_HOURS, 
dwh_tme_entry_facts.TIMEENTRY_KEY, 
DWH_CMN_PERIOD.PERIOD_START_DATE 
FROM dwh_tme_entry_facts 
INNER JOIN DWH_CMN_PERIOD 
ON DWH_TME_ENTRY_FACTS.PERIOD_KEY = DWH_CMN_PERIOD.PERIOD_KEY 
--AND DWH_TME_ENTRY_FACTS.PERIOD_KEY IN (x,y,z)
order by period_start_date

Step 2: Delete the records from rpt_calendar

If the issue still persists:

1. Delete the records from rpt_calendar in the Clarity Database:  
   
   DELETE FROM rpt_calendar WHERE 1=1;
   
   
2. Run the  'Update Report Tables' job
3. Next, run the Full Load Data Warehouse job.

Additional Information

See also: Load DWH job frequently reported issues