...
Note: number of 1 - 9 are the dimension and Pri = priority, US = user status and SS = system status
Naming convention on BW provider such as MVPMNO04
...
C. F_500__REFRESH_MAINT_DASH_VIEW
This job will run sql query to delete prj-data-maintenance-dash-[env].DM.FACT_maintenace_dash and fill the table again with the view prj-data-maintenance-dash-[env].DM. FACT_maintenace_dash with parameter l_LOCAL_VAR_FACT_HISTORY_NUM_MONTH in order to control how many months that need for the final table for dashboard. Normally it is 6 month
...
To update authorization from gsheet (sheet name = "for training sessions and acces") : save to prj-data-dm-industrial-[env].WDL.WRK_maintenance_dash_access to assign permissions to end users
...
select reference_week , sum ( nb_of_notification_created ) nb_of_created , sum ( nb_of_notif_with_attachement ) nb_of_attachement
from `prj-data-dm-industrial-proddev.DM.FACT_notification_planning`
where reference_week in ( '10.2025' , '11.2025' , '12.2025' )
and nb_of_notification_created = 1
group by reference_week
order by reference_week ;
...
select instance_ident_a_in_bor_compat_persistent_object_references from `prj-data-dm-industrial-proddev.DM.DIM_generic_object_services`
where instance_ident_a_in_bor_compat_persistent_object_references like '%1102765340'
...
select RELTYPE , INSTID_A , TYPEID_A from prj-data-dm-industrial-proddev.ODS.ODS_SPF_0000_F001_I_D_SRGBTBREL
where INSTID_A like '%1102765340'
...
1. Reload DIM_generic_object_services by change following parameter:
l_VAR_SPF_SRGBTBREL_dm_reload_condition = QUALIFY ROW_NUMBER() OVER (PARTITION BY CONCAT(meta_source_system,CLIENT,BRELGUID) ORDER BY meta_ods_insert_date DESC) = 1 "where UTCTIME > 20250301000000"
l_VAR_SPW_SRGBTBREL_dm_reload_condition = QUALIFY ROW_NUMBER() OVER (PARTITION BY CONCAT(meta_source_system,CLIENT,BRELGUID) ORDER BY meta_ods_insert_date DESC) = 1 "where UTCTIME > 20250301000000"
To reload all data that more than 1 Mar 2025 from ODS both PF1 and WP1 to DIM_generic_object_services by running job F100_DIM_generic_object_service_SRGBTBREL_SAP_TO_DM.
...
2. Reload FACT_notification_planning by changing parameter:
l_VAR_BWH_notification_planning_dm_reload_condition = "and SnapShot__Week = '11.2025'"
To reload ODS_BWH_0000_F005_F_W_mec_mvpmno04_0002 only week 11.2025 to mapping with DIM_generic_object_services to find the number of attachment again.
...
4. Test with script above again, the number of attachment should be increased on week 11.2025
5. Run job F_500_REFRESH_MAINT_DASH_VIEW to update prj-data-maintenance-dash-dev.DM.FACT_maintenace_dash
Logging
Loading job
in `prj-data-dm-industrial-[environment].STG.[table]`
...