Description

The data source getting from data ocean marketing, which get from salesforce (SFC) on the Case tables in order to get the number of complaints comparing with BW query QVBW_QRY_MVSDSO57_0002 QVBW - Deliveries Detials SO&PO (core query), which get the number of delivery items

Dataflow

prj-data-dm-marketing-dev.ODS.ODS_BWH_0000_F001_F_M_qvbw_qry_mvsdso57_0002 → prj-data-dm-marketing-dev.ODS.V_ODS_BWH_0000_F001_F_M_qvbw_qry_mvsdso57_0002 → prj-data-dm-marketing-dev.ODS.VB_BWH_no_of_delivery_orders → prj-data-dm-marketing-dev.DS_prj_industrial_dash.V_BWH_no_of_delivery_orders => prj-data-industrial-dash-dev.DataOcean.V_BWH_no_of_delivery_orders → prj-data-industrial-dash-dev.DPL.V_BWH_no_of_delivery_orders

prj-data-dm-marketing-dev.ODS.ODS_SFC_0000_F001_F_M_case → prj-data-dm-marketing-dev.ODS.V_ODS_SFC_0000_F001_F_M_case → prj-data-dm-marketing-dev.ODS.VB_sfc_case → prj-data-dm-marketing-dev.DS_prj_industrial_dash.V_sfc_case ==> prj-data-industrial-dash-dev.DataOcean.V_sfc_case → prj-data-industrial-dash-dev.DPL.V_sfc_case

From these 2 sources, they will combine to DM.FACT_customer_complaints on GCP project prj-data-industrial-dash-dev by using Talend job

Tools: Talend

From source to ODS 

Talend project DATA_OCEAN_DOMAIN_MARKETING

F001_QVBW_QRY_MVSDSO57_0002_to_ODS (number of delivery item)

Detail Job: F001_QVBW_QRY_MVSDSO57_0002_to_ODS 

1. Define variable 
2. Generate meta_run_id
3. Check the incremental load.  If variable l_VAR_XTRACT_PARA_TALEND_QVBW_QRY_MVSDSO57_0002 = currentmonth, it will load from BW last month to current month, else must be this format &YYYYMM_Start=202306&YYYYMM_End=202311
4. Call reference job to load from BW and save the output file to bucket cs-ew1-prj-data-dm-marketing-dev-staging/TALEND_DEV_QVBW_QRY_MVSDSO57_0002
5. Load from Bucket to STG and ODS
6. Update log

F020_SFC_case_customer_complaint_to_ODS (number of complaints)

Detail job J020_SFC_case_customer_complaint_to_ODS

1. Connect to Salesforce (SFC)
2. Query SFC on the case table 
3. Write output to file in local PC
4. Put the file to bucket cs-ew1-prj-data-dm-marketing-dev-staging/Case/
5. Delete the output file in local PC
6. Write log on the main flow job

From DM to Operational Dashboard

Talend Project IND_DASHBOARD

Talend job F010_FACT_customer_complaints  (J010_FACT_customer_complaints)

1. Check execution time format
2. Select data from DataOcean.V_sfc_case sfc and DataOcean.V_BWH_no_of_delivery_orders 
   1. Create a template internal table (period x gbu x plant x serivity)

      1. calendar to have period in format YYYY-MM back to n month, which control by variable l_VAR_IND_DASH_customer_complaint_nb_month_to_DM  

      2. gbu_mapping to have text and key, which get from variable l_VAR_IND_DASH_customer_complaint_gbu_mapping = internal table for gbu, add more in the variable if we have new gbu

      3. severity_mapping to have list of severity value = not specified, Low, High, Major, Medium, Anomaly, Critical and Standard which get from variable l_VAR_IND_DASH_customer_complaint_severity_mapping 

      4. site_cal to have all plant and gbu from V_sfc_case sfc cross join with severity_mapping and calendar  to have template table that have all period, gbu, plant and serivity

   2. Create an aggregate internal table of customer complaint (cc_aggr) from V_sfc_case sfc 
   3. Create an aggregate internal table of delivery (del_aggr) from V_BWH_no_of_delivery_orders 
   4. Select the final data by calculate rolling 12 months 
3. Delete all data in DM.FACT_customer_complaints
4. Load data from point 2 to FACT_customer_complaints
5. In case of error, email inform DataOps (l_VAR_IND_DASH_email)
6. Update log

Access rights

It is required to access BW query via Xtract.  

and access to Salesforce 

Source

BW

BW query = QVBW_QRY_MVSDSO57_0002

Xtract job = TALEND_[ENV]_QVBW_QRY_MVSDSO57_0002

SFC (https://login.salesforce.com/services/Soap/u/52.0)

Condition that select from SFC

FROM  Case
WHERE CreatedDate >= 2020-01-01T00:00:00Z
and PO2_CASE_Organization__c='ECO'

Control variable to connect

• l_LOCAL_Domain_Marketing_SF_USER
• l_LOCAL_Domain_Marketing_SF_PASSWORD
• l_LOCAL_Domain_Marketing_SF_TOKEN
• l_LOCAL_Domain_Marketing_SF_endpoint

Format

columnar format

Destination

DataOcean

• Bucket = cs-ew1-prj-data-dm-marketing-dev-staging/
  • TALEND_DEV_QVBW_QRY_MVSDSO57_0002
  • Case
• DataOean GCP = prj-data-dm-marketing-[env]
• STG Table names =

  • STG_BWH_0000_0000_F001_F_M_qvbw_qry_mvsdso57_0002

  • STG_SFC_0000_0000_F001_F_M_case

• ODS Table names =

  • ODS_BWH_0000_F001_F_M_qvbw_qry_mvsdso57_0002

  • ODS_SFC_0000_F001_F_M_case

Product

• GCP = prj-data-industrial-dash-[env]
• DataOcean
  • V_BWH_no_of_delivery_orders
  • DataOcean.V_sfc_case
• DM
  • FACT_customer_complaints
• DPL 
  • V_FACT_customer_complaints

Format

columnar format

Sizing

• STG_BWH_0000_0000_F001_F_M_qvbw_qry_mvsdso57_0002             around   52,200 records / month, Xtract job spend around 2 min / month maximum 6 months. 
• STG_SFC_0000_0000_F001_F_M_case                                                   around  18,576 records

Assessment

Data same as BW query and SFC

Loading

1.1 Incremental Load

Delivery Source to ODS : PL_MKT_QVBW_QRY_MVSDSO57_0002 (WS_DATA_OCEAN_DOMAIN_MARKETING)

control by l_VAR_XTRACT_PARA_TALEND_QVBW_QRY_MVSDSO57_0002 = currentmonth, it will load last month to current month on 0CALMONTH on

Case (number of complaint) and FACT_customer_complaints are not available for incremental

1.2 Full load

Delivery N/A

Case (number of complaint) Source to ODS : PL_MKT_SFC_CASE (WS_DATA_OCEAN_DOMAIN_MARKETING)

FACT_customer_complaints 

1.3. Reloading data

Delivery 

• l_VAR_XTRACT_PARA_TALEND_QVBW_QRY_MVSDSO57_0002  = &YYYYMM_Start=202301&YYYYMM_End=202305.  Maximum should not over 6 months otherwise Xtract will be out of memory and run F001_QVBW_QRY_MVSDSO57_0002_to_ODS (WS_DATA_OCEAN_DOMAIN_MARKETING)

Case (number of complaint)

• Just run the F020_case_customer_complaint_to_ODS again.

FACT_customer_complaints 

• Just run the F010_FACT_customer_complaints  again.

1.4 Plan to schedule

It is scheduled by plans below on WS_DATA_OCEAN_DOMAIN_MARKETING

• PL_MKT_QVBW_QRY_MVSDSO57_0002 Monthly on 1st at 05:00 CET
• PL_MKT_SFC_CASE                                  Monthly on 1st at 05:00 CET

1.5 Timing

Delivery 10 minutes from source to ODS (current month)

Case 3- 10 minutes from source to ODS (full)

Criticality

Low?

Logging

Check the loading records 

select job.job_name, job.meta_start_date, job.meta_execution_id, logs.meta_run_id, logs.meta_source_system, logs.meta_step, logs.meta_status, logs.meta_num_lines, logs.meta_error_lines from STG.log_tables logs join STG.run_jobs job on logs.meta_run_id = job.meta_run_id
where  logs.meta_run_id in (SELECT meta_run_id FROM STG.run_jobs order by meta_start_date desc limit 1000)
and job_name in ('F001_QVBW_QRY_MVSDSO57_0002_to_ODS','F020_SFC_case_customer_complaint_to_ODS')
and meta_start_date >  DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 32 DAY)
order by job.meta_start_date desc