Technical Architecture

Data Flow

1. BW(Workforce + Payroll) →  Xtract → Talend → GCP → Qlik
2. Pulse(gsheet) → Talend → GCP → Qlik

Data Source

Prerequisites:

  BW 

• Remote Access should be enabled for the interface query
• Access to the two interface queries should be assigned to the role: ZH_PA_INGESTION and technical user: RFC_TAL_WBP

  Pulse

• It's integrated into one gsheet(tab:Pulse) as above and should be shared with
  • sa-talend@prj-data-dm-hr-dev.iam.gserviceaccount.com
  • sa-talend@prj-data-dm-hr-test.iam.gserviceaccount.com
  • sa-talend@prj-data-dm-hr-ppd.iam.gserviceaccount.com
  • sa-talend@prj-data-dm-hr-prod.iam.gserviceaccount.com
• Short text for [Management Level]
• Text for [Cadre, Non Cadre]

RestrictionRestrictions on BW queries:

• User Name = SACBW_HRD
• HR Roles    = WORLDWIDE
• PO2 Flag    = #; E;TSAE

Please see here for details.

Xtract

Job	Description
TALEND_PROD_DO_CPHRPAHDM_0005	People at Solvay
TALEND_PROD_DO_CPHRPY002_0001	Labor Cost - People Insight

Please see here for details.

Talend

Job	Frequency	Start Time	Duration

BWMonthly~20:30pm CET~3.5hPulseOn Demand~4:00am CET~2min

GCP Table/View/Procedure

For the loading of historical data, starting from the current month of Y-2 until previous month of Y.

In order to avoid the memory issue in WBP, please load the data every 3 months, to put the parameter in RDS.talend_param with the format: &YYYYMM_Start=202206&YYYYMM_End=202208.

After the finish of the historical data loading, please put the currentmonth in table:talend_param, it will load the data of previous month on 10th of each month

F120_People_Insight_Data_Mart_Monthly

• F001_BW_QRY_CPHRPAHDM_0005_to_ODS(Workforce Data)
  • J001_BW_QRY_CPHRPAHDM_0005_csv_to_GCS
• F002_BW_QRY_CPHRPY002_0001_to_ODS(Payroll Data)
  • J002_BW_QRY_CPHRPY002_0001_csv_to_GCS

F120_Data_Mart_Pulse_Manual

• F003_FIL_PULSE(Solvay Pulse Survey Data)
  • J003_Pulse_GSheet_to_GCS

GCP

STG Tables:

• STG.STG_BWH_0000_0000_F001_F_

• M_qry_cphrpahdm_0005

• STG.STG_BWH_0000_0000_F001_F_

• M_qry_cphrpy002_0001

• STG.STG_FIL_0000_0000_F003_F_A_pulse_to_hr_peopleinsight

ODS Tables:

• ODS.ODS_BWH_0000_F001_F_

• M_qry_cphrpahdm_0005

• ODS.ODS_BWH_0000_F001_F_

• M_qry_cphrpy002_0001

• ODS

• .ODS_FIL_0000_0000_F003_F_A_pulse_to_hr_peopleinsight

DM Tables:

• DM.FACT_hr_peopleinsight_workforce
• DM.FACT_hr_peopleinsight

• _payroll
• DM.FACT_hr_peopleinsight_pulse

Stored Procedure:

• DM.PROC_PAYROLL_ODS_TO_DM
• DM.PROC_WORKFORCE_ODS_TO_DM
• DM.PROC_PULSE_ODS_TO_DM

Views:

• DS_prj_data_ppl_insgt_dashb.V_FACT_hr_peopleinsight_workforce
• DS_prj_data_ppl_insgt_dashb.V_FACT_hr_peopleinsight_pulse
• DS_prj_data_ppl_insgt_dashb.V_FACT_hr_peopleinsight_payroll

• DataOcean.V_FACT_hr_peopleinsight_pulse
• DataOcean.V_FACT_hr_peopleinsight_payroll
• DataOcean.V_FACT_hr_peopleinsight_workforce

• DPL.V_FACT_hr_peopleinsight_pulse
• DPL.V_FACT_hr_peopleinsight_payroll
• DPL.V_FACT_hr_peopleinsight_workforce

The DataOcean and DPL views should be created in GCP project: prj-data-ppl-insgt-dashb-<<env>>; the others to be created in GCP project: prj-data-dm-hr-<<env>>

Service Accounts:

• prj-data-ppl-insgt-dashb-<<env>>
  • sa-talend@prj-data-ppl-insgt-dashb-<<env>>.iam.gserviceaccount.com
• prj-data-ppl-insgt-dashb-<<env>>
  • sa-talend@prj-data-dm-hr-<<env>>.iam.gserviceaccount.com

DA&AI - Domain Mapping - HR

DA&AI  - HR - People Insight Dashboard

QlikSense

QlikSense technical documentation

Data Quality Controls

Data comes from SAP system. To compare data between BW and sources systems, check propagation layers.

Operational Documentation

Procedures

<Describe the recurring procedures needed to operate the application (eg. start/pause/terminate/restart the app processes, data preparation, data ingestion, ETL, data visualization, data export, other manual activities)>

Scheduling

<Describe the scheduling in place for the application (eg. existing jobs, trigger time/event based, dependencies)>

Monitoring

<Describe the monitoring checks to confirm the application is performing well (eg. check the overall status, check performance metrics like runtime/data volume/memory/disk/CPU, maintain and react to alerts/notifications)>

Error Handling

<Describe how to handle errors (eg. error codes, description and respective resolution, alert users)>

Known Bugs

<List the existing bugs, its criticality, workarounds and resolution plan.>

Roadmap

<List past & future evolutions for the application (including links to MED/FSD/TSD)>