FIS-HR Integration

Overview

Mainline was tasked with migrating TUFTS existing SAP HR extracts workflow into DENODO. The following documents important information related to this migration.

Diagram

Denodo Elements

The following is a list of all elements that were added in Denodo. The naming “_final” was used to denote views that serve as the final table, comparable to the original target table. The naming “_lineout” was used to denote views that serve as the lineout/csv file from the existing ETL. These views would be what an API can grab directly from.

Cached view

Final view

• Denodo VDP

  • Education_technology

    • 03-integration

      • SAP_Refactoring

        • FIS2X_employees_plus

          • df_fis2x_hr_pre_proc_step1

          • df_fis2x_hr_pre_proc_step2

          • u_j_fis2x_hr_pre_proc_step2

          • fis2x_employees_plus_final

        • FIS2X_HR0_001

          • fis2x_hr0_001_final

          • j_fis2x_hr0_001_01

          • j_fis2x_hr0_001_01_query1

          • j_fis2x_hr0_001_step2_query

          • j_fis2x_hr0_001_step2_sql

          • j_fis2x_hr0_051_final

          • j_fis2x_hr0_052_final

          • j_s_hcm_eg_rank_descr

          • j_s_hcm_names_fname_prf

          • j_tfth_qry_div

          • p_bv_d_fis_title

          • p_bv_d_person_attr

          • p_bv_s_hcm_dept_tbl

          • p_bv_s_hcm_dept_tbl_ed

          • p_bv_s_hcm_eg_rank_tbl

          • p_bv_s_hcm_jobcode_tbl

          • p_bv_s_hcm_names

          • p_bv_s_hcm_names_b1_ed

          • p_bv_s_hcm_tfth_dept_tbl

          • p_bv_s_hcm_tfth_dept_tbl_eee

          • p_bv_s_hcm_tfth_dept_tbl_max_effdt

          • p_bv_s_tfth_appointmnt_f_ed

          • p_bv_s_tfth_appointmnt_f_es

          • p_bv_s_xlattable_vw

          • p_j_fis2x_hr0_001_01_new_3_may

        • FIS2X_hr0_002

          • df_fis2x_fix_hr0_002_b4_write_sql

          • fis2x_hr00_lineout - Final view to consume from, previously known as HR_FEED.csv

          • fis2x_hr0_002_final

          • j_load_fis2x_hr0_002_query

          • j_load_fis2x_hr0_002_query_1

          • j_load_fis2x_hr0_002_query_2

          • j_load_fis2x_hr0_002_query_3

          • j_load_fis2x_hr0_002_sql

          • j_load_fis2x_hr0_002_sql_pref_name

          • p_bv_d_hcm_degree_suffix_view_lookup

          • p_bv_d_hcm_home_dept_tbl_lookup_descr

          • p_bv_d_hcm_pers_names_lookup

          • p_bv_s_tfth_fis_affctr_lookup

          • p_load_hr0_002_sql_1

          • p_load_hr0_002_sql_2

          • p_u_fis2x_hr0_001_max_selection_rank

        • FIS2X_HR1_001

          • df_fis2x_load_hr1_001

          • fis2x_hr01_lineout - Final view to consume, previously HR_PROF_1.csv

          • fis2x_hr1_001

          • fis2x_hr1_001_final

        • FIS2X_hr2_001

          • df_fis2x_load_hr2_001__new_sql

          • fis2x_hr2_001_final

          • p_bv_d_hcm_home_dept_tbl_lookup_tfth_qry_div

          • p_bv_s_hcm_tfth_dept_tbl_descr_lookup

        • FIS2X_HR2_002

          • df_fis2x_load_hr2_002_new_query

          • df_fis2x_load_hr2_002_new_query_1

          • df_fis2x_load_hr2_002_new_sql

          • fis2x_hr02_lineout - Final view to consume, previously HR_PROF_2.csv

          • fis2x_hr2_002_final

          • p_bv_d_hcm_home_dept_tbl_lookup_pdept

          • p_bv_d_hcm_home_dept_tbl_lookup_pdept_ed

        • FIS2X_HR3_001

          • df_fis2x_load_hr3_001_final

          • df_fis2x_load_hr3_001_lineout - Final view, HR_PROF_3.csv

          • df_fis2x_load_hr3_001_sql

        • FIS2X_HR4_001

          • df_fis2x_load_hr4_001_final

          • df_fis2x_load_hr4_001_lineout - Final view, HR_PROF_4.csv

          • df_fis2x_load_hr4_001_query

          • df_fis2x_load_hr4_001_sql

        • FIS2X_HR5_001

          • df_fis2x_load_hr5_001_final

          • df_fis2x_load_hr5_001_lineout - Final view, HR_PROF_5.csv

          • df_fis2x_load_hr5_001_sql

        • FIS2X_HR6_001

          • df_fis2x_load_hr6_001_final

          • df_fis2x_load_hr6_001_lineout - Final view, HR_PROF_6.csv

          • df_fis2x_load_hr6_001_sql

          • p_bv_s_xlattable_vw_2

        • FIS2X_HR7_001

          • df_fis2x_load_hr7_001_final

          • df_fis2x_load_hr7_001_lineout - Final view, HR_PROF_7.csv

          • df_fis2x_load_hr7_001_sql

        • FIS2X_HR8_001

          • df_fis2x_load_hr8_001_final

          • df_fis2x_load_hr8_001_lineout - Final view, HR_PROF_8.csv

          • df_fis2x_load_hr8_001_sql

          • df_fis2x_load_hr8_001_sql1

        • FIS2X_hr_url_master

          • fis2x_hr_url_all_sql

          • fis2x_hr_url_master_all_empids

          • fis2x_hr_url_master_all_emplids_plus_historical

          • fis2x_hr_url_master_final

          • fis2x_hr_url_master_sql

          • p_bv_fis2x_hr_url_master

          • p_bv_fis2x_hr_url_master_tag

          • p_fis2x_hr_url_master_historical_query

Denodo Cache:

Two Denodo views in the overall workflow are cached as indicated above. Those cached views are refreshed in totality every day at 1 am via the Denodo Scheduler fis_hr_cache job. That job consists of two extract and load processes as shown below:

1. fis2x_hr0_001_final

Open

2. fis2x_hr_url_master_all_emplids_plus_historical

Open

Supporting Documents

FIS2X Elements documentation: https://tufts.box.com/s/juhfn9y3ua0oh1wxxqxpvlaxdvnptoap

Additional info

For any questions reach out to:
Colton Henderson - chende03@tufts.edu

John Klein - john.klein@tufts.edu