Request reference

WO0000000787229

Description

To get the activity of email in each case, it is required to call API https://solvay-restapi.onbmc.com/api/rx/application/datapage?dataPageType=com.bmc.arsys.rx.application.record.datapage.RecordInstanceDataPageQuery&recorddefinition=com.bmc.dsm.social-lib:SCL_Posts

It is setup  on context parameter l_VAR_helix_case_activity_url

The example data of output is here

Tools: Talend

Detail job

• J062_Helix_case_activity

1. tJava1 to print out all required parameter in order to debug on the log file in case of error. 
2. Connect to the source system API (l_VAR_helix_itsm) in order to get token to access data
3. Setup loop to get the data to run the loop when offset >= 0 and number of loop <= l_VAR_helix_case_activity_nloop . 
4. tJava2 to print "offset" of each loop to debug that it is increasing or not.
5. To get data from the source on API (l_VAR_helix_case_activity_url) and control by following parameter
   1. l_VAR_helix_case_activity_limit = maximum number of record to extract from API.  Maximum is 2000. 
   2. l_VAR_helix_case_activity_offset = start index of the records. Normally start with 0 except on reloading data
   3. l_VAR_helix_case_activity_sort = sort by 3 which is created_date,  sort by 6 (modified_date) does not work in this API
   4. l_VAR_helix_case_activity_nloop =  number of loop in order to get data from API
   5. Also hardcode on parameter "dataPageType" = "com.bmc.arsys.rx.application.record.datapage.RecordInstanceDataPageQuery" and "recorddefinition" = "com.bmc.dsm.social-lib:SCL_Posts"
6. Replace the value of invalid character
7. Extract data from json format by mapping id with name
8. tMap2 to change date format of created_date and modified_date
9. Generate output file and save to  DATA\DEV\DATA_OCEAN_DOMAIN_DT\Output
10. Update the offset number "nb" = "nb" + number of extraction line
11. Update "nb" = -1 when ((Integer)globalMap.get("tExtractJSONFields_3_NB_LINE")) <= 0 in order to exit the loop (No more data )
12. Upload the files all the folder( cs-ew1-prj-data-dm-dt-[dev]-staging) and eelete all the files in the folder (point number 9)

Flow job

• F062_Helix_case_activity

1. Check parameter l_VAR_helix_case_activity_reload that it is incremental or reload to get the name of HELIX_CASE_ACTIVITY_RELOAD or HELIX_CASE_ACTIVITY
2. Get the time from STG.incremetnal_load based on the name from point 1
3. Set the last load time
4. Set query and filter only post_type = system#email in order to get only email activity
5. Call the detail job and pass parameters
6. Call the standard job to upload the files from GCS to ODS
7. Delete the data if the category is not related to Procurement or Buy - Provisioning or it is not existing in case_itsm by this SQL below (for both ODS and STG)
   Delete FROM "+context.l_LOCAL_VAR_ODS_DATASET+"."+(String)globalMap.get("odsTable")+" where case_id in (select distinct case_id from "+context.l_LOCAL_VAR_ODS_DATASET+"."+(String)globalMap.get("odsTable")+" ca 
   left join ODS.ODS_HLX_0000_F001_I_H_Cases_ITSM itsm on ca.case_id = itsm.Request_ID
   where (Request_ID is null   
   or JSON_EXTRACT_SCALAR(jsonContent, \"$['po2_flag']\") = '2000' 
   or NOT (coalesce(JSON_EXTRACT_SCALAR(jsonContent, \"$['category_tier_1']\"),\"\") = \"Procurement\" 
            and coalesce(JSON_EXTRACT_SCALAR(jsonContent, \"$['category_tier_2']\"),\"\") = \"Buy - Provisioning\")));
8. Update max of created_date on HELIX_CASE_ACTIVITY_RELOAD or HELIX_CASE_ACTIVITY, Incase the last extraction has the max created_date same as before, it will send email to inform l_VAR_helix_case_activity_email_recipient  and then update the log. 

Access rights

Need to authorized to access the Helix API https://solvay-restapi.onbmc.com/api/jwt/login

Source

•       https://solvay-restapi.onbmc.com/api/rx/application/datapage?dataPageType=com.bmc.arsys.rx.application.record.datapage.RecordInstanceDataPageQuery&recorddefinition=com.bmc.dsm.social-lib:SCL_Posts    

Format

• JSON

Destination

Location

• Bucket = cs-ew1-prj-data-dm-dt-[dev]-staging/xxx
• DataOean GCP = prj-data-dm-dt-[env]
• STG Table name =  prj-data-dm-dt-[env].STG.STG_HLX_0000_0000_F001_I_H_case_activity
• ODS Table name =  prj-data-dm-dt-[Env].ODS.ODS_HLX_0000_F001_I_H_case_activity → ODS.V_ODS_HLX_0000_F001_I_H_case_activity 
• Self Service Dataset = prj-data-dm-dt-dev.DS_sls_transformation_office.V_ODS_HLX_0000_F001_I_H_case_activity

Format

• columnar format

Loading

1.1 Incremental Load

l_VAR_helix_case_activity_reload must be "incremental"

The extraction will start on created_date >= prj-data-dm-dt-dev.STG.incremental_loading on mata_last_process_date

1.2 Full load

N/A

1.3. Reloading data

1. Change context parameter l_VAR_helix_case_activity_reload to the condition that want to reload such as " and '6' >= %222024-10-01T00:00:00%22"
2. Change table prj-data-dm-dt-dev.STG.incremental_loading on mata_last_process_date to the start created_date that is required to reload where meta_file_name = HELIX_CASE_ACTIVITY_RELOAD
3.  Run the job F062_Helix_case_activity, the max date of created_date from new extraction will updated to STG.incremental_loading. Repeat the job if required. 

These setup will be the condition '3'>STG.incremental_loading.mata_last_process_date + l_VAR_helix_case_activity_reload 

Field ID meaning and example

Field ID

1.4 Plan to schedule

Schedule on Task F062_Helix_case_activity at 7:20 AM every Monday

1.5 Timing

5 minutes for incremental load

If selection to extract data more than 4 month, the API will time out. 

Criticality 

Low

Logging

select job.job_name , job.meta_start_date , 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 like '%case_act%'
and meta_step = 'Bucket to Staging'
and meta_start_date >   DATE_SUB ( CURRENT_TIMESTAMP () , INTERVAL 7   DAY )
order by meta_start_date desc