Overview

Sends HR data combined from the Oracle DW and Person Registry to Enterprise Health, a health requirements tracking platform used by Occupational Health team at Tufts.

The integration runs daily via a job in the Denodo Scheduler that sends data incrementally by checking for rows that are new or changed compared to the data cached the previous day. The feed relies on the fetching of a session ID from Enterprise Health via Lambda to make more rapid, successive API calls.

Design

Components

Bold items are final/consumed views

Denodo VDP
- human_resources
  - 01-base_layer
    - 01-connections
      - ds_eh_hr_feed_data_queue
      - ds_eh_hr_feed_session_id
    - 02-base_views
      - bv_eh_hr_feed_data_queue
      - bv_eh_hr_feed_session_id
  - 02-transformations
    - persons_health
    - persons_health_current_cache
    - persons_health_previous_cache
  - 03-integration
    - j_persons_health_country
    - persons_eh_hr_feed_data_queue_history
    - persons_health_current_previous_diff
    - persons_eh_hr_feed_data_queue_incremental
    - persons_eh_hr_feed_data_queue_manual
Denodo Scheduler
- eh_session_id
- eh_current_cache
- eh_hr_data_feed_queue_previous_cache
- eh_incremental_load
- eh_hr_feed_data_queue_failure_check
Lambda
- enterprise-health
Enterprise Health
- Dev (DM): https://tuftsdm.enterprise.health/webchart.cgi
- Prod: https://tuftsu.enterprise.health/webchart.cgi

How-To

Force a full data load

Invalidate the cache for persons_health_previous_cache via the Design Studio UI or using this query (with the human_resources VDB selected)

ALTER VIEW persons_health_previous_cache
CACHE INVALIDATE;

Once the cache is invalidated, run the feed job in the scheduler.

Feed failures

The job eh_hr_feed_data_queue_failure_check will run after the feed job is complete to see if any records failed to send to EH by querying the cache for human_resources.persons_health_eh_hr_feed_data_queue_incremental. If any failures are found, a CSV is generated and sent out via e-mail to the Tufts Denodo team email address (denodo-devs@elist.tufts.edu). If the event of a job failure, a separate email will be sent from either job to the same email address.

Record failures of status 500 typically mean there was an issue with the EH server, with 400 meaning there was an issue with the request/data. For 500 errors you can just re-send, however 400 errors should be investigated as they may require updating a view to handle bad data.

You can re-send failed emp_id’s by running the following two queries.

Store a fresh EH session ID

SELECT 
  * 
FROM human_resources.bv_eh_hr_feed_session_id 
CONTEXT('cache_wait_for_load' = 'true', 'cache_invalidate' = 'matching_rows', 'cache_preload' = 'true');

Send the records to EH

SELECT 
  * 
FROM human_resources.persons_health_eh_hr_feed_data_queue_manual
where emp_id in (/*EMP ID's go here*/)
CONTEXT('cache_wait_for_load' = 'true', 'cache_preload' = 'true');

Data Strategy