Overview

The variable to control the reload is l_LOCAL_VAR_RECOVERY_MODE

true = Reload

false = Incremental

This will control only PL_MECANO_DASH (weekly load).

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

** This will apply to all the queries that use Week parameter.

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

There are 2 data flow of loading

PL_MECANO_DASH (F_MAIN_MECANO) load weekly on Monday
PL_MECANO_DAILY_LOAD Every Tue, Wed, Thu and Fri impact only query QVMECANO_BW_QRY_MVPMOP04_0006

** Remark: The common confusion

Query QVMECANO_BW_QRY_MVPMOP04_0006 has the Week variable (yyyyww) but it uses the daily flow loading. Since this query needs to update every day.
Query QVMECANO_BW_QRY_MVPMOR01_0002 has the Day variable (yyyyMMdd) but it is a weekly load.

Name of Xtract job parameter

It is used to control the name of Xtract name to run on the job F100_MEC_EXTRACT_BW_QUERY_TO_GSC

l_VAR_mecano_xtract_DBPMMD01_0001_job
l_VAR_mecano_xtract_DBPMMD02_0001_job
l_VAR_mecano_xtract_DBPMMD05_0001_job
l_VAR_mecano_xtract_MVPMCL01_0001_job
l_VAR_mecano_xtract_mvpmco01_job
l_VAR_mecano_xtract_MVPMNO04_0001_job
l_VAR_mecano_xtract_MVPMNO04_0002_job
l_VAR_mecano_xtract_MVPMOP02_0010_job
l_VAR_mecano_xtract_MVPMOP04_0001_job
l_VAR_mecano_xtract_MVPMOP04_0002_job
l_VAR_mecano_xtract_MVPMOP04_0003_job
l_VAR_mecano_xtract_MVPMOP04_0005_job
l_VAR_mecano_xtract_MVPMOP04_0006_job
l_VAR_mecano_xtract_MVPMOR01_0002_job
l_VAR_mecano_xtract_MVPMOR04_0001_job

Valid parameter

It is used to control which query will be used to load by the job F100_MEC_EXTRACT_BW_QUERY_TO_GSC

l_VAR_mecano_xtract_DBPMMD01_0001_valid
l_VAR_mecano_xtract_DBPMMD02_0001_valid
l_VAR_mecano_xtract_DBPMMD05_0001_valid
l_VAR_mecano_xtract_MVPMCL01_0001_valid
l_VAR_mecano_xtract_mvpmco01_valid
l_VAR_mecano_xtract_MVPMNO04_0001_valid
l_VAR_mecano_xtract_MVPMNO04_0002_valid
l_VAR_mecano_xtract_MVPMOP02_0010_valid
l_VAR_mecano_xtract_MVPMOP04_0001_valid
l_VAR_mecano_xtract_MVPMOP04_0002_valid
l_VAR_mecano_xtract_MVPMOP04_0003_valid
l_VAR_mecano_xtract_MVPMOP04_0005_valid
l_VAR_mecano_xtract_MVPMOP04_0006_valid
l_VAR_mecano_xtract_MVPMOR01_0002_valid
l_VAR_mecano_xtract_MVPMOR04_0001_valid

F100_MEC_EXTRACT_BW_QUERY_TO_GSC

J201_STG_TO_ODS

Incremental load

Variable = l_LOCAL_VAR_RECOVERY_MODE = false

Talend job = F_MAIN_MECANO

Talend sub job = F100_MEC_EXTRACT_BW_QUERY_TO_GSC

This job will determine

Day (yyyyMMdd,yyyyMMdd): get last Monday to current Monday by using custom routine

String to_week=R001_MECANO.getCurrentMonday(TalendDate.getCurrentDate()) ;

String from_week=R001_MECANO.getpreviousMonday(TalendDate.getCurrentDate());

Week(yyyyww): Get the week of loading date - 1 day

int currentWeek = TalendDate.getPartOfDate("WEEK_OF_YEAR", TalendDate.getCurrentDate()) - 1;

String weekOfYear = (currentWeek > 0) ? String.format("%02d", currentWeek) : "52";

String param=TalendDate.getDate("YYYY")+ weekOfYear;

Month(yyyy0MM, yyyy0MM): get last month to current month based on loading date

String prev="K4"+TalendDate.formatDate("yyyy0MM", TalendDate.addDate(TalendDate.getCurrentDate(), -1,"MM"));

String cur="K4"+TalendDate.formatDate("yyyy0MM", TalendDate.getCurrentDate());

Reload

DAIRY FLOW

It is not allowed to reload. It must use Weekly flow instead.

The daily flow will load only TALEND_QVMECANO_BW_QRY_MVPMOP04_0006

WEEKLY FLOW

Variable = l_LOCAL_VAR_RECOVERY_MODE = true

Talend job = F_MAIN_MECANO

Talend sub job = F100_MEC_EXTRACT_BW_QUERY_TO_GSC

This job will determine

Day (yyyyMMdd,yyyyMMdd): get last Monday to current Monday by using custom routine

String from_week=context.l_LOCAL_VAR_MVPMOR01__YYYYMMDD_FROM

String to_week=context.l_LOCAL_VAR_MVPMOR01__YYYYMMDD_TO

Remark:

Must NOT over current Mon otherwise, it will be duplicate
1 Month will have size around 20 MB and 50k rows

Week(yyyyww): Get the week of loading date - 1 day

yyyyww = l_LOCAL_VAR_HISTO_YEAR + l_LOCAL_VAR_HISTO_WEEK_END

Month(yyyy0MM, yyyy0MM): get last month to current month based on loading date

String prev = context.l_LOCAL_VAR_MVPMCO01_0001_HIST_K4YYYY0MM_PREV;

String cur = context.l_LOCAL_VAR_MVPMCO01_0001_HIST_K4YYYY0MM_CURR;

For example, today is 4 Dec 2024 (Wed) we need to reload

F04_QVMECANO_BW_QRY_MVPMNO04_0001

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.01/2.02/2.05_hist:

nb_of_notification_created
nb_of_notif_in_progress
nb_of_notif_in_progress_without_work_order
nb_of_notif_in_progress_with_delay
nb_of_notif_in_progress_delay_1_to_30_days
nb_of_notif_in_progress_delay_31_to_90_days
nb_of_notif_in_progress_delay_more_than_91_days
nb_of_notif_in_progress_no_required_end_date

Validation

select reference_week,

sum(nb_of_notification_created) nb_of_notification_created,

sum(nb_of_notif_in_progress) nb_of_notif_in_progress,

sum(nb_of_notif_in_progress_without_work_order) nb_of_notif_in_progress_without_work_order,

sum(nb_of_notif_in_progress_with_delay) nb_of_notif_in_progress_with_delay,

sum(nb_of_notif_in_progress_delay_1_to_30_days) nb_of_notif_in_progress_delay_1_to_30_days,

sum(nb_of_notif_in_progress_delay_31_to_90_days) nb_of_notif_in_progress_delay_31_to_90_days,

sum(nb_of_notif_in_progress_delay_more_than_91_days) nb_of_notif_in_progress_delay_more_than_91_days,

sum(nb_of_notif_in_progress_no_required_end_date) nb_of_notif_in_progress_no_required_end_date

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.01/2.02/2.05_hist'

group by reference_week

order by reference_week desc

BW Result

F05: QVMECANO_BW_QRY_MVPMNO04_0002

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.01/2.02/2.05_det:

nb_of_notification_created
nb_of_notif_in_progress
nb_of_notif_in_progress_without_work_order
nb_of_notif_in_progress_with_delay
nb_of_notif_in_progress_delay_1_to_30_days
nb_of_notif_in_progress_delay_31_to_90_days
nb_of_notif_in_progress_delay_more_than_91_days
nb_of_notif_in_progress_no_required_end_date

Validation

select reference_week,

sum(nb_of_notification_created) nb_of_notification_created,

sum(nb_of_notif_in_progress) nb_of_notif_in_progress,

sum(nb_of_notif_in_progress_without_work_order) nb_of_notif_in_progress_without_work_order,

sum(nb_of_notif_in_progress_with_delay) nb_of_notif_in_progress_with_delay,

sum(nb_of_notif_in_progress_delay_1_to_30_days) nb_of_notif_in_progress_delay_1_to_30_days,

sum(nb_of_notif_in_progress_delay_31_to_90_days) nb_of_notif_in_progress_delay_31_to_90_days,

sum(nb_of_notif_in_progress_delay_more_than_91_days) nb_of_notif_in_progress_delay_more_than_91_days,

sum(nb_of_notif_in_progress_no_required_end_date) nb_of_notif_in_progress_no_required_end_date

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.01/2.02/2.05_det'

and reference_week between '41.2024' and '48.2024'

and planning_plant_key like '%GRU%'

group by reference_week

order by reference_week

BW Result

Reload

Normally this query will update the kpi_no = '2.01/2.02/2.05_det'

This data will be available on the DM.FACT_maintenance_efficiency only the latest week.

There will be a section to delete the whole data from this query when l_LOCAL_VAR_RECOVERY_MODE == false (incremental load every Monday)

Therefore,

if we reload the data for the current week, just run the PL_MECANO_DASH with l_LOCAL_VAR_RECOVERY_MODE == false

if we reload the data for the previous weeks, it is not required for this query. However, to reload for other queries, mark “valid” flag to not reload for QVMECANO_BW_QRY_MVPMNO02_0002.

delete from DM.FACT_maintenance_efficiency where kpi_no='2.01/2.02/2.05_det'"

F06: QVMECANO_BW_QRY_MVPMCL01_0001

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_effectiveness

This query will impact on kpi_no = 3.02

planned_activity_nb_of_calls
planned_activity_executed_nb_of_calls
ongoing_activity_nb_of_calls
activity_delayed_nb_of_calls
activity_delayed_1_30_days_nb_of_calls
activity_delayed_31_90_days_nb_of_calls
activity_delayed_more_than_90_days_Nb_of_Calls
planned_activity_nb_of_hours
Planned_activity_executed_nb_of_hours

Validation

select reference_week,

sum(planned_activity_nb_of_calls) planned_activity_nb_of_calls,

sum(planned_activity_executed_nb_of_calls) planned_activity_executed_nb_of_calls,

sum(ongoing_activity_nb_of_calls) ongoing_activity_nb_of_calls,

sum(activity_delayed_nb_of_calls) activity_delayed_nb_of_calls,

sum(activity_delayed_1_30_days_nb_of_calls) activity_delayed_1_30_days_nb_of_calls,

sum(activity_delayed_31_90_days_nb_of_calls) activity_delayed_31_90_days_nb_of_calls,

sum(activity_delayed_more_than_90_days_Nb_of_Calls) activity_delayed_more_than_90_days_Nb_of_Calls,

sum(planned_activity_nb_of_hours) planned_activity_nb_of_hours,

sum(planned_activity_executed_nb_of_hours) planned_activity_executed_nb_of_hours

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '3.02'

and reference_week between '41.2024' and '48.2024'

and planning_plant_key like '%GRU%'

group by reference_week

order by reference_week

ODS

SELECT meta_run_id,SUM(SAFE_CAST(Planned_Activity__Nb_of_Calls as float64)) Planned_Activity__Nb_of_Calls FROM `prj-data-dm-industrial-dev.ODS.ODS_BWH_0000_F006_F_W_mec_mvpmcl01_0001`

where Snapshot_Week_Key = '41.2024'

and Planning_Plant_Key like '%GRU%'

group by meta_run_id

BW Result

Reload

For example to reload 41.2024

Stop the loading in TMC.
it is required to delete the data from DM.FACT_maintenance_effectiveness.

delete

from `prj-data-dm-industrial-dev.DM.FACT_maintenance_effectiveness`

where reference_week = '41.2024'

and kpi_no = '3.02'

'

Change the variable

l_LOCAL_VAR_RECOVERY_MODE = true

l_LOCAL_VAR_HISTO_YEAR = 2024

l_LOCAL_VAR_HISTO_WEEK_END = 41

Control the query to be run on the “valid”

to select in the job in case we don’t want to reload all the queries.

mark “valid” flag to not reload for QVMECANO_BW_QRY_MVPMNO02_0002 since it is required only latest week

F07: QVMECANO_BW_QRY_MVPMOP04_0001

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.04

nb_operation_planned
nb_operation_planned_good_day
nb_operation_reactives

F08: QVMECANO_BW_QRY_MVPMOP04_0002

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.06/2.07/2.09

nb_operation_scheduled_kpi6
nb_operation_well_confirmed_kpi6
nb_operations_in_delay
remaining_work_backlog_h
remaining_work_backlog_man_days

Validate

select reference_week,

sum(nb_operation_scheduled_kpi6) nb_operation_scheduled_kpi6,

sum(nb_operation_well_confirmed_kpi6) nb_operation_well_confirmed_kpi6,

sum(nb_operations_in_delay) nb_operations_in_delay,

sum(remaining_work_backlog_h) remaining_work_backlog_h,

sum(remaining_work_backlog_man_days) remaining_work_backlog_man_days,

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.04'

and reference_week between '41.2024' and '48.2024'

and planning_plant_key like '%GRU%'

group by reference_week

order by reference_week

F09: QVMECANO_BW_QRY_MVPMOP04_0003

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.06

nb_operation_unscheduled
nb_operation_planned
nb_operation_planned_good_day
nb_operation_planned_good_week
nb_operation_reactives
hours_of_operations_planned
hours_of_operations_planned_good_day
hours_of_operations_planned_good_week
hours_of_operations_reactives

Validate

select reference_week,

sum(nb_operation_unscheduled) nb_operation_unscheduled,

sum(nb_operation_planned) nb_operation_planned,

sum(nb_operation_planned_good_day) nb_operation_planned_good_day,

sum(nb_operation_planned_good_week) nb_operation_planned_good_week,

sum(nb_operation_reactives) nb_operation_reactives,

sum(hours_of_operations_planned) hours_of_operations_planned,

sum(hours_of_operations_planned_good_day) hours_of_operations_planned_good_day,

sum(hours_of_operations_planned_good_week) hours_of_operations_planned_good_week,

sum(hours_of_operations_reactives) hours_of_operations_reactives,

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.06'

and reference_week between '41.2024' and '48.2024'

and planning_plant_key like '%GRU%'

group by reference_week

order by reference_week

F11: QVMECANO_BW_QRY_MVPMOP04_0005

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.08

remaining_work_hours
available_capacity
available_capacity_unit

Validate

DPL

with temp as(

select reference_week, work_center,

sum(remaining_work_hours) remaining_work_hours,

max(available_capacity) available_capacity,

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.08'

and reference_week between '26.2024' and '50.2024'

and planning_plant_key like '%GRU%'

group by reference_week,work_center)

select reference_week,

sum(remaining_work_hours) remaining_work_hours,

sum(available_capacity) available_capacity,

from temp

group by reference_week

order by reference_week

ODS

select SnapShot__Week__Key, meta_run_id, sum(safe_cast(Remaining_Work__h as float64)), sum(safe_cast(Available_capacity as float64))

from `prj-data-dm-industrial-dev.ODS.ODS_BWH_0000_F011_F_W_mec_mvpmop04_0005`

where Planning_Plant_Key like '%GRU%'

and SnapShot__Week__Key like '%2024%'

group by SnapShot__Week__Key, meta_run_id

order by SnapShot__Week__Key

Reload

For example to reload 41.2024

Stop the loading in TMC.
it is required to delete the data from DM.FACT_maintenance_efficiency.

delete

from `prj-data-dm-industrial-dev.DM.FACT_maintenance_efficiency`

where reference_week = '41.2024'

and kpi_no = '2.08'

Change the variable

l_LOCAL_VAR_RECOVERY_MODE = true

l_LOCAL_VAR_HISTO_YEAR = 2024

l_LOCAL_VAR_HISTO_WEEK_END = 41

Control the query to be run on the “valid”

F100_MEC_EXTRACT_BW_QUERY_TO_GSC

J201_STG_TO_ODS

mark “valid” flag to not reload for QVMECANO_BW_QRY_MVPMNO02_0002 since it is required only latest week

F12_QVMECANO_BW_QRY_MVPMOP04_0006

Selection parameter = WEEK(yyyyww) / Need to delete max reference date

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.13

nb_operation_planned
nb_operation_planned_good_day
nb_operation_reactives

Reload 1 week file size around 12 MB

Loading time around 6 min

Validation

select reference_week,

sum(nb_operation_planned) nb_operation_planned,

sum(nb_operation_planned_good_day) nb_operation_planned_good_day,

sum(nb_operation_reactives) nb_operation_reactives,

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.13'

and reference_week between '41.2024' and '48.2024'

and planning_plant_key like '%GRU%'

group by reference_week

order by reference_week

The loading will get the data from ODS_BWH_0000_F012_F_W_mec_mvpmop04_0006 only the reference_date > max(reference_date) from DM.FACT_maintenance_efficiency. Therefore, it will load only the new data.

SELECT max(reference_date) AS max_reference_date

FROM DM.FACT_maintenance_efficiency

WHERE kpi_no='2.13'

Select xxxx from ODS.ODS_BWH_0000_F012_F_W_mec_mvpmop04_0006

inner join STG.log_files on ODS_BWH_0000_F012_F_W_mec_mvpmop04_0006.meta_run_id=log_files.meta_run_id

and log_files.meta_file_name='MEC_IT_0000_0000_F012_"+context.Business_date+"_0000_F_W_MEC_MVPMOP04_0006.csv'

and log_files.meta_status in ('OK','NOK')

WHERE PARSE_DATE('%d.%m.%Y', SnapShot__Date)>'" + TalendDate.formatDate("yyyy-MM-dd",((java.util.Date)globalMap.get("max_ref_date")))

Reload

Stop the loading in TMC.
it is required to delete the data from DM.FACT_maintenance_efficiency. Since this query will impact only query = MVPMOP04_0006 , kpi_no = 2.13. Therefore, the deletion script should be:

It is required to to delete the prj-data-dm-industrial-dev.DM.FACT_maintenance_efficiency

delete

from `prj-data-dm-industrial-dev.DM.FACT_maintenance_efficiency`

where reference_date >= '2024-11-04'

and kpi_no = '2.13'

The selection of reference_date must be matched by the week. For example, if we want to reload 2024-11-08, which is missing. We need to select reference_date >= '2024-11-04'

This is because we can reload it from week 45.2024 (2024-11-04 to 2024-11-10) to today

Change the variable

l_LOCAL_VAR_RECOVERY_MODE = true

l_LOCAL_VAR_HISTO_YEAR = 2024

l_LOCAL_VAR_HISTO_WEEK_END = 45

Control the query to be run on the “valid”

to select in the job in case we don’t want to reload all the queries.

mark “valid” flag to not reload for QVMECANO_BW_QRY_MVPMNO02_0002 since it is required only latest week

Test data when loading with week 45.2024. We will have data correctly same as BW in ODS

BW query result.

F13: QVMECANO_BW_QRY_MVPMOR04_0001

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.12/2.03/2.14

nb_of_orders_in_progress
kpi_2_11_nb_of_orders_executed_with_a_required_end_date
kpi_2_11_nb_of_orders_executed_before_required_date
kpi_2_11_nb_of_orders_executed_without_a_required_end_date
kpi_2_11_number_of_orders_executed

Validate

select reference_week,

sum(nb_of_orders_in_progress) nb_of_orders_in_progress,

sum(kpi_2_11_nb_of_orders_executed_with_a_required_end_date) kpi_2_11_nb_of_orders_executed_with_a_required_end_date,

sum(kpi_2_11_nb_of_orders_executed_before_required_date) kpi_2_11_nb_of_orders_executed_before_required_date,

sum(kpi_2_11_nb_of_orders_executed_without_a_required_end_date) kpi_2_11_nb_of_orders_executed_without_a_required_end_date,

sum(kpi_2_11_number_of_orders_executed) kpi_2_11_number_of_orders_executed,

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.12/2.03/2.14'

and reference_week between '41.2024' and '48.2024'

and planning_plant_key like '%GRU%'

group by reference_week

order by reference_week

F14: QVMECANOBW_QRY_MVPMOR01_0002

Selection parameter = DAY(yyyyMMdd,yyyyMMdd)

Fact table = FACT_maintenance_effectiveness

This query will impact on kpi_no = 3.03

work_orders

Validate

select reference_date,

sum(work_orders) work_orders,

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.11'

and reference_date between '2024-11-07' and '2024-12-01'

and planning_plant_key like '%GRU%'

group by reference_date

order by reference_date

F15: QV_BW_QRY_MVPMOP02_0010

Selection parameter = WEEK(yyyyww)

Fact table = FACT_maintenance_efficiency

This query will impact on kpi_no = 2.11

barcode_scan_rate
nb_operations_well_scanned
nb_operations_completed

Validate

select reference_week,

sum(barcode_scan_rate) barcode_scan_rate,

sum(nb_operations_well_scanned) nb_operations_well_scanned,

sum(nb_operations_completed) nb_operations_completed

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '2.11'

and reference_week between '41.2024' and '48.2024'

and planning_plant_key like '%GRU%'

group by reference_week

order by reference_week

Reload

For example to reload 41.2024

Stop the loading in TMC.
it is required to delete the data from DM.FACT_maintenance_efficiency.

delete

from `prj-data-dm-industrial-dev.DM.FACT_maintenance_efficiency`

where reference_week = '41.2024'

and kpi_no = '2.11'

Change the variable

l_LOCAL_VAR_RECOVERY_MODE = true

l_LOCAL_VAR_HISTO_YEAR = 2024

l_LOCAL_VAR_HISTO_WEEK_END = 41

Control the query to be run on the “valid”

F100_MEC_EXTRACT_BW_QUERY_TO_GSC

J201_STG_TO_ODS

mark “valid” flag to not reload for QVMECANO_BW_QRY_MVPMNO02_0002 since it is required only latest week

F16: QVMECANO_BW_QRY_MVPMCO01_0001

Selection parameter = MONTH(yyyy0MM,yyyy0MM)

Fact table = FACT_maintenance_dash_strategic

This query will impact on kpi_no = 2.11

actual_amount
plan_amount

Validate

select reference_date,currency_key,currency_type_key,

sum(actual_amount) actual_amount,

sum(plan_amount) plan_amount,

from `prj-data-maintenance-dash-dev.DPL.V_FACT_maintenace_dash_copy`

where kpi_no = '1.10'

and reference_date between '2024-09-01' and '2024-12-01'

and planning_plant_key like '%GRU%'

group by reference_date,currency_key,currency_type_key

order by reference_date,currency_type_key

This query will select by month and the date will be only on the 1st of each month. And it will delete the month on the FACT_maintenance_dash_strategic. Therefore, no need to select delete.

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

)

WDL.FACT_maintenance_dash_strategic_cost_budget_tmptable will have data from the last loading from ODS (truncate)

Reload

For example, need to reload Oct 2024

Stop the loading in TMC.
Change the variable

l_LOCAL_VAR_RECOVERY_MODE = true

l_LOCAL_VAR_MVPMCO01_0001_HIST_K4YYYY0MM_PREV = 2024010

l_LOCAL_VAR_MVPMCO01_0001_HIST_K4YYYY0MM_CURR = 2024010

Control the query to be run on the “valid”

F100_MEC_EXTRACT_BW_QUERY_TO_GSC

J201_STG_TO_ODS

“valid” flag to not reload for QVMECANO_BW_QRY_MVPMNO02_0002 since it is required only latest week

Page tree

Mecano - Reload on Mecano query

Overview

Incremental load

Reload

DAIRY FLOW

WEEKLY FLOW

F04_QVMECANO_BW_QRY_MVPMNO04_0001

F05: QVMECANO_BW_QRY_MVPMNO04_0002

Reload

F06: QVMECANO_BW_QRY_MVPMCL01_0001

Reload

F07: QVMECANO_BW_QRY_MVPMOP04_0001

F08: QVMECANO_BW_QRY_MVPMOP04_0002

F09: QVMECANO_BW_QRY_MVPMOP04_0003

F11: QVMECANO_BW_QRY_MVPMOP04_0005

Reload

F12_QVMECANO_BW_QRY_MVPMOP04_0006

Reload

F13: QVMECANO_BW_QRY_MVPMOR04_0001

F14: QVMECANOBW_QRY_MVPMOR01_0002

F15: QV_BW_QRY_MVPMOP02_0010

Reload

F16: QVMECANO_BW_QRY_MVPMCO01_0001

Reload