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

Tools: Talend

Talend project DATA_OCEAN_DOMAIN_MARKETING

• F020_SFC_case_customer_complaint_to_ODS (number of complaints)

• F001_QVBW_QRY_MVSDSO57_0002_to_ODS (number of delivery item)

From source to ODS 

Job: F001_QVBW_QRY_MVSDSO57_0002_to_ODS (number of delivery item)

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

Job:F020_SFC_case_customer_complaint_to_ODS (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

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

Access rights

It is required to access BW query via Xtract.  

and access to SFC

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

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

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) is not available for incremental

1.2 Full load

Delivery N/A

Case (number of complaint) Source to ODS : PL_MKT_SFC_CASE

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

Case (number of complaint)

• Just run the PL_MKT_SFC_CASE 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