...
Mainline was tasked with migrating TUFTS existing SAP HR extracts workflow into DENODO. The following documents important information related to this migration.
Diagram
TODO
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. DB -
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:
fis2x_hr0_001_final
...
fis2x_hr_url_master_all_emplids_plus_historical
...
Supporting Documents
FIS2X Elements documentation: DI_JOB_FIS2X_Generate_Extracts.xlsx https://tufts.box.com/s/juhfn9y3ua0oh1wxxqxpvlaxdvnptoap
Additional info
For any questions reach out to:
Colton Henderson - chende03@tufts.edu
...