Talend project = MECANO

GCP Project : prj-data-dm-industrial-[env]

GCP Product: prj-data-maintenance-dash-[env]

Bucket = cs-ew1-prj-data-dm-industrial-[env]-staging/MECANO/

Weekly Load

Data Flow

A. F_MAIN_MECANO

A.1 F100_MEC_EXTRACT_BW_QUERY_TO_GSC

To extract data from Xtract to Bucket 

1. Compute of query parameter. 

To check it is incremental or reload the data by using l_LOCAL_VAR_RECOVERY_MODE (true = reload, false = incremental). More detail check on reload session

There are 3 main parameter groups for BW queries.

There are 3 groups of loading variables

Day(yyyyMMdd,yyyyMMdd) : QVMECANOBW_QRY_MVPMOR01_0002

• l_LOCAL_VAR_MVPMOR01__YYYYMMDD_FROM  eg. 20241001
• l_LOCAL_VAR_MVPMOR01__YYYYMMDD_TO  ***Must NOT over current Monday

Week (yyyyww) : The rest of queries

• l_LOCAL_VAR_HISTO_YEAR  eg. 2024
• l_LOCAL_VAR_HISTO_WEEK_END eg. 01

Month(yyyy0MM,yyyy0MM) : QVMECANO_BW_QRY_MVPMCO01_0001

• l_LOCAL_VAR_MVPMCO01_0001_HIST_K4YYYY0MM_PREV eg. K42024009
• l_LOCAL_VAR_MVPMCO01_0001_HIST_K4YYYY0MM_CURR eg. K42024010

If the l_LOCAL_VAR_RECOVERY_MODE = false (normal case that load weekly on Monday)

Day : Previous Monday to current Monday

Week : Last week (last Monday to Sunday)

Month : Load last month to current month

2. List of query to extract

It control by set of parameter

Xtract Job = l_VAR_mecano_xtract_[query name]_job = Xtract job name 

Valid         = l_VAR_mecano_xtract_[query name]_valid.  If it is 1, it will extract the data (check condition on "if" line)

tJava2 does not have anything

3. tJava3 just print what is the "Current Query"  in order to see on the log in TMC

4. Use reference job to call BW query and save the file to GCS

5. Move from staging bucket to folder MECANO

A.2 J201_STG_TO_ODS

To load the file from GCS to ODS

1. Control filename, STG, ODS table name, valid and separator

It is use the same context as job F100_MEC_EXTRACT_BW_QUERY_TO_GSC and hardcode the separator to ‰

2. Up load the file from GCS to ODS table, which copy from reference job but only this part is different

A.3 F300_MEC_LOAD_ALL_DIM_TABLE_PART_1

To load ODS to DM layer for:

• DIM_priority
• DIM_user_status
• DIM_system_status

A.4 F300_MEC_LOAD_ALL_DIM_TABLE_PART_2

To load ODS to DM layer for:

1. DIM_F_maintenance_activity_type : MaintActivityType_Key , MaintActivityType (only for PF1)
2. DIM_F_equipment : Equipment_Key, Equipment
3. DIM_F_maintenance_order: Maintenance_Order_Key, Maintenance_Order
4. DIM_F_planner_group: Planner_Group_Key, Planner_Group, Source_System_Key
5. DIM_F_order_type: Order_Type_Key, Order_Type (only for WP1)
6. DIM_F_functional_location: Functional_Location_Key, Functional_Location, Source_System_Key
7. DIM_F_revision: Revision_Key, Revision
8. DIM_F_work_center: Work_Center_Key, Work_Center
9. DIM_F_plant_section : Plant_Section_Key, Plant_Section, Source_System_Key

A.5 F310_MEC_LOAD_FACT_MAINTENANCE_STRATEGIC

To load ODS to DM layer for FACT_maintenance_strategic. There is only 1 query ODS_BWH_0000_F016_F_W_mec_mvpmco01_0001

1. Select data from ODS with condition

from "+context.l_CNX_GOOGLE_MECANO_ProjectID+"."+context.l_LOCAL_GOOGLE_MECANO_DATASET_ODS+".ODS_BWH_0000_F016_F_W_mec_mvpmco01_0001
inner join "+context.l_CNX_GOOGLE_MECANO_ProjectID+"."+context.l_LOCAL_GOOGLE_MECANO_DATASET_STAGING+".log_files on ODS_BWH_0000_F016_F_W_mec_mvpmco01_0001.meta_run_id=log_files.meta_run_id and log_files.meta_file_name LIKE 'MEC_IT_0000_0000_F016_"+context.Business_date+"_0000_F_W_MEC_MVPMCO01_0001%.csv' and log_files.meta_status in ('OK','NOK')

2. Save the result of the FACT join with dimension tables to context.l_LOCAL_PATHDIR_MECANO_DATA+context.l_LOCAL_PATHDIR_MECANO_DM+"FACT_maintenance_strategic.csv"

3. Put the file to buckets context.l_LOCAL_PATHDIR_MECANO_DATA+context.l_LOCAL_PATHDIR_MECANO_DM+"FACT_maintenance_strategic.csv"

4. Upload the file to WDL.FACT_maintenance_dash_strategic_cost_budget_tmp

5. Delete file from 2

6. Delete file from 4

7. Delete data of existing month from the selection of ODS on DM.FACT_maintenance_strategic

DELETE FROM  DM.FACT_maintenance_strategic 
WHERE ref_date IN (SELECT DISTINCT CAST(ref_date as Date format 'MON YYYY') as ref_date FROM WDL.FACT_maintenance_dash_strategic_cost_budget_tmp)
8. Insert the data from ODS to DM.FACT_maintenance_strategic

There is no requirement to delete data manually when reload, which different from efficiency or effectiveness

A.6 F320_MEC_LOAD_FACT_MAINTENANCE_EFFECTIVENESS

To load ODS to DM layer for FACT_maintenance_effectiveness. There is only 2 queries 

ODS_BWH_0000_F006_F_W_mec_mvpmcl01_0001

ODS_BWH_0000_F016_F_W_mec_mvpmor01_0002

A.7 F330_MEC_LOAD_FACT_MAINTENANCE_EFFICIENCY

To load ODS to DM layer for FACT_maintenance_efficiency. There is only 2 queries 

ODS_BWH_0000_F006_F_W_mec_mvpmcl01_0001

ODS_BWH_0000_F016_F_W_mec_mvpmor01_0002

Summary

Note: number of 1 - 9 are the dimension and Pri = priority, US = user status and SS = system status