General presentation
Objective of the application
This application is used to follow:
- the Quality Results for Shipment
- As source of MiniCus or MniTab application
- To follow the Limites, Quantitative and qualitative results of the Quality inspections.
- Statistical ratios and calculations on the quality inspections results
Tool Leader + IT leader of the application:
Usage information
History
This application has been created in 2006 for Rhodia data (WP1). It was upgraded for Solvay data (PF1) in 2017. At the same time data from both Rhodia and Solvay was consolidated and optimized in a unique multiprovider, and the number of used queries was reduced to keep only those necessary.
In January-February 2019, the application has been updated to integrate the SA&D replacement for the LIMS solution. Changes are mostly consigned to Business and Reporting layers, with a couple new queries used in daily broadcasts (enabled on March 11th 2019). Main process chain PC_QM_00 has been split, to separate updated Multiprovider MVQM01 from MVQM02 because of time constraints on the broadcasting.
In August 2019, after HANA migration complete, we fix the issue of master data display and consuming database space of QM inspection lot by delivery (MVQM02 flow) by using calculation view and composite provider CPQM01 in stead.
In November 2019, we try to migrate all inspection lot report MVQM01, ODS_QM09, ODS_QM08, and CUB_QM01 to CPQM02. We stop the loading in Jan 2021 and completely migrate by Mar 2021. This solution can improve loading from 3 hours to 3 minutes and save a lot of space.
Roles & Access
Roles and access
List of application role + menu role and explanation if we have several applications role with specials rules.
| Role Code | Role Description | Explanation |
|---|---|---|
| ZR_RCS_CA_M14 | QM - Quality Management | This role gives access to the Query and Workbooks through role menus. |
| ZBI_RCS_QM_A02 | Quality Results Application - End User role | This role controls access to the application. |
| ZR_*_CA_P01 | Analysis company - XXX | Role to control access to data based on the company code, with XXX being the name of the company code. There is a many role as there is company code. In the new multiproviders created, this role is not relevant. |
| ZR_*_CA_P02 | Analysis - Plant XXX - YYY | Role to control access to data based on the plant, with XXX being the name of the plant, and YYY its description. There is a many role as there is plants. In the new multiproviders created, the infoobject used for authorization is C_PLANT. |
Authorization objects
List of authorization objects mandatory for the application.
| Authorization object | Explanation |
|---|---|
| ZBI_QM | This object gives global access to objects relevant for authorization yet not relevant for the related multiprovider. This is especially true for the controlling area (CO_AREA) |
| ZXXXXCAP01 | This object gives access to the various infoobjects company code related and relevant for authorization, with XXXX being the company code considered. |
| ZXXXXCAP02 | This object gives access to the various infoobjects plant related and relevant for authorization, with XXXX being the plant considered. |
Dataflow overview
Reporting documentation drive folder:
Functional and Technical rules on Workbench + Reporting
Rules & Explanations
Inspection Results for deliveries (Sep 2019 version)
1. We use calculation view CV_QM_INPDVL_WP1 to join DBQM04 and DPSD001 for WP1
2. We use calcualtion view CV_QM_INPDVL_PF1 to join DBQM03 and DSO_DLV1 for PF1
QM HANA Solution to improve performance and mainly reduce the size of database. With this new solution, we can reduce time of loading and database size around 76 GB.
Calculation view : Key to link Inspection lot and delivery are Material and Batch
Composite provider CPQM01 created basing on 2 calculation views (CV_QM_INPDVL_WP1 / CV_QM_INPDVL_PF1)
Inspection Results for deliveries (Obsolete Sep 2019)
1: TRSF: DPSD001 -> DBQM06 (Solvay)
Look up on DSO DBQM03 - DSO QM - Business Layer : Inspection Results (Solvay) using batch number and material number from the deliveries to retrieve QM data. There is also a filter on the "generic inspection lot". Solvay is using some batch to do multiple deliveries. However this generates countless lines which would then be multiplied depending on the lines in DBQM03 (generating at worst more than 5 millions lines). Those generic lot are therefore filtered if the number of lines for a given couple batch / material is more than 30.
2: TRSF: DSO_DLV1 -> DBQM05 (Rhodia)
Look up on DSO DBQM04 - DSO QM - Business Layer : Inspection Results (Rhodia) using batch number and material number from the deliveries to retrieve QM data.
3: ODSO DSO_DLV1 -> ODSO DSO_QMTL
Look up on DSO ODS_QM09 - Inspection Results: Quantitative/Qualitative Data using batch number and material number from the deliveries to retrieve QM data.
6: ODSO DSO_QMTL -> CUBE CUB_QM03
7: TRSF: DBQM05 -> CRQM08 (Rhodia) and TRSF: DBQM05 -> CRQM09 (Rhodia) and TRSF: DBQM06 -> CRQM07 (Solvay) and TRSF: DBQM06 -> CRQM10 (Solvay)
Difference between historical and current data
7-a: TRSF: DBQM06 -> CRQM10 (Solvay) and TRSF: DBQM05 -> CRQM09 (Rhodia)
Loading of the inspection lots with a creation date /BIC/C_ENSTEHD greater than current date minus 2 years (ex. current date 16/02/2017 ==> Loading of data from 01/01/2015 and upwards)
7-b: TRSF: DBQM05 -> CRQM08 (Rhodia) and TRSF: DBQM06 -> CRQM07 (Solvay)
Loading of the inspection lots with a creation date /BIC/C_ENSTEHD smaller than current date minus 2 years (ex. current date 16/02/2017 ==> Loading of data older than 01/01/2015)
Inspection Results (Nov 2019 version)
4: ODS_QM09 8ODS_QM07 and ODS_QM08 8ODS_QM07
Transformation done but not used. Only the update rules are used
5: ODSO ODS_QM01 -> ODSO ODS_QM01 and ODSO ODS_QM07 -> ODSO ODS_QM07
Daily loading but few cases. Mostly irrelevant
7: TRSF: DBQM03 -> CRQM06 (Solvay) and TRDF: DBQM04 -> CRQM05 (Rhodia) and TRSF DBQM04 -> CRQM03 and TRSF DBQM03 -> CRQM04 (Solvay)
Difference between historical and current data.
7-a: TRSF: DBQM03 -> CRQM06 (Solvay) and TRDF: DBQM04 -> CRQM05 (Rhodia)
Loading of the inspection lots with a creation date /BIC/C_ENSTEHD greater than current date minus 3 years (ex. current date 16/02/2017 ==> Loading of data from 01/01/2015 and upwards)
7-b: TRSF DBQM04 -> CRQM03 and TRSF DBQM03 -> CRQM04 (Solvay)
Loading of the inspection lots with a creation date /BIC/C_ENSTEHD smaller than current date minus 3 years (ex. current date 16/02/2017 ==> Loading of data older than 01/01/2015)
7: TRSF: DBQM03 -> APQM02 (Solvay) and TRDF: DBQM04 -> APQM01 (Rhodia) and TRSF DBQM04 -> APQM03 and TRSF DBQM03 -> ABQM04 (Solvay)
Difference between historical and current data.
7-a: TRSF: DBQM03 -> APQM02 (Solvay) and TRDF: DBQM04 -> APQM01 (Rhodia)
Loading of the inspection lots with a creation date /BIC/C_ENSTEHD greater than current date minus 3 years (ex. current date 16/02/2017 ==> Loading of data from 01/01/2015 and upwards)
7-b: TRSF DBQM04 -> APQM03 and TRSF DBQM03 -> APQM04 (Solvay)
Loading of the inspection lots with a creation date /BIC/C_ENSTEHD smaller than current date minus 3 years (ex. current date 16/02/2017 ==> Loading of data older than 01/01/2015)
8: TRSF: DPQM09 -> DBQM04 (Rhodia) and TRSF: DPQM08 -> DBQM03 (Solvay)
- Deletion od data if results exist (field C_MRMITNI).
- CO_AREA determination
9: TRSF: DPQM07 -> DBQM03 (Solvay) and TRSF: DPQM08 -> DBQM03 (Solvay) and TRSF: DPQM10 -> DBQM04 (Rhodia) and TRSF: DPQM09 -> DBQM04 (Rhodia)
Mapping of a few fields with the values of QASE, QAMR or QASR, depending on the values of c_semesni (if value is set for QASE), c_secode1 (code QASE), c_srcode1 (Code QASR), C_SRMITNI (if value is set for QAMR)
10: TRSF: IH_QM_2LIS_05_QE1_RCS -> DPQM09 and TRSF: IH_QM_2LIS_05_QE1_SOLVAY -> DPQM08
Determining of the shift work and shift work day. Customized fields are get from class (SE24) = Z2LIS_05_QE1 and Z2LIS_05_QE2 instead of user exit (CMOD)
11: TRSF: 2LIS_05_QE1 / Rhodia -> IH_QM_2LIS_05_QE1_RCS and TRSF: 2LIS_05_QE1 Solvay -> IH_QM_2LIS_05_QE1 and TRSF: 2LIS_05_QE1 Solvay -> IH_QM_2LIS_05_QE1 and TRSF: 2LIS_05_QE2 - Solvay -> IH_QM_2LIS_05_QE2
Cleaning of data
Dependencies with other applications
Inspection Results
None
Inspection Results for deliveries
Both DSO DSO_DLV1 and DPSD001 (deliveries) need to be loaded before the upwards dataflow is loaded. This is done through the PC:
- DPSD001: PC_SD_01
- DSO_DLV1: PC_SD
Data loadings
Info providers and objects loaded
Loading frequency
Main Process Chains
| Process Chain | Code | Comments |
|---|---|---|
| META - D - QM - Master Chain | PC_QM_00 | Loading of data in delta mode |
| META - D - QM - Master Chain for MVQM01 | PC_QM_34 | Loading of data in delta mode, for MVQM01 (CRQM03/04/05/06) only. Process Chain runs every day at 3:20 AM (CET). |
| META - ADH - QM - Inspection Results Init | PC_QM_19 | Used to reload data for dataflow QM. Caution: The loading of the two DSO ODS_QM07 and ODS_QM01 for Rhodia is not included. |
| TD - ADH - QM - Deletion of Inspection Results data | PC_QM_18 | Used to delete data in infoproviders |
| TD - Y - QM - Inspection Results - Historic Data | PC_QM_07 | Updates the infocubes containing historical data for both Solvay and Rhodia Data with year - 3. The infocubes containing the last 5 years of data is emptied and reloaded accordingly. |
| TD - Y - QM - Qual. Insp. Res. for deliv. - Historic Data | PC_QM_17 | Updates the infocubes containing historical data for both Solvay and Rhodia Data with year - 3. The infocubes containing the last 5 years of data is emptied and reloaded accordingly. |
Sub Process Chains
| Process Chain | Code | Comments |
|---|---|---|
| TD - D - QM - Propagation Layer - Solvay | PC_QM_01 | Used to load Solvay data for the propagation layer for every QM projects |
| TD - D - QM - Propagation Layer - Rhodia | PC_QM_02 | Used to load Rhodia data for the propagation layer for every QM projects |
| TD - D - QM - Business Layer - Rhodia | PC_QM_03 | Used to load Rhodia data for the business layer for every QM projects |
| TD - D - QM - Business Layer - Solvay | PC_QM_04 | Used to load Solvay data for the business layer for every QM projects |
| TD - D - QM - Reporting Layer - Rhodia | PC_QM_05 | Used to load Rhodia data for the reporting layer. |
| TD - D - QM - Reporting Layer - Solvay | PC_QM_06 | Used to load Solvay data for the reporting layer. |
| TD - D - QM - BL - Inspection Results - Rhodia | PC_QM_08 | Used to load Rhodia data for the business layer for Inspection Results |
| TD - D - QM - BL - Inspect. Results for Deliveries - Rhodia | PC_QM_09 | Used to load Rhodia data for the business layer for Inspection Results for deliveries |
| TD - D - QM - BL - Inspect. Results for Deliveries - Solvay | PC_QM_10 | Used to load Solvay data for the business layer for Inspection Results for deliveries |
| TD - D - QM - BL - Inspection Results - Solvay | PC_QM_11 | Used to load Solvay data for the business layer for Inspection Results |
| TD - D - QM - PL - Inspection Results (Solvay) | PC_QM_12 | Used to load Solvay data for the propagation layer for Inspection Results |
| TD - D - QM - RL - Inspection Results - Rhodia | PC_QM_13 | Used to load Rhodia data for the reporting layer for inspection results. (CRQM03) |
| TD - D - QM - RL - Inspect. Results for Deliveries - Solvay | PC_QM_14 | Used to load Solvay data for the reporting layer for inspection results for deliveries. (CRQM07) |
| TD - D - QM - RL - Inspect. Results for Deliveries - Rhodia | PC_QM_15 | Used to load Rhodia data for the reporting layer for inspection results for deliveries. (CRQM08) |
| TD - D - QM - RL - Inspection Results - Solvay | PC_QM_16 | Used to load Solvay data for the reporting layer for inspection results. (CRQM04) |
| TD - Y - QM - RL - Inspection Results - Historic - Rhodia | PC_QM_20 | Used to load Rhodia historical data for the reporting layer for inspection results. (CRQM05) |
| TD - Y - QM - RL - Inspection Results Historic - Solvay | PC_QM_21 | Used to load Solvay historical data for the reporting layer for inspection results. (CRQM06) |
| TD - Y - QM - RL - Inspect. Results for Del. - Hist - Rhodia | PC_QM_22 | Used to load Rhodia historical data for the reporting layer for inspection results for deliveries. (CRQM09) |
| TD - Y - QM - RL - Inspect. Results for Del. - Hist - Solvay | PC_QM_23 | Used to load Solvay historical data for the reporting layer for inspection results for deliveries. (CRQM10) |
| MD - D - QM - Textes QM | PC_QM_24 | Loading of the texts for specific QM MD |
| TD - ADH - QM - BL - Insp Res for Del - Rhodia - Init < 2013 | PC_QM_25 | Used when loading Rhodia data for project inspection results for deliveries. Given the volumetry generated, it's easier to load the data year by year. This PC loads data depending on the creation date up to 31.12.2012 |
| TD - ADH - QM - BL - Insp Res for Del - Rhodia - Init >=2013 | PC_QM_26 | Used when loading Rhodia data for project inspection results for deliveries. Given the volumetry generated, it's easier to load the data year by year. This PC loads data depending on the creation date from 01.01.2013 |
| TD - ADH - QM - BL - Insp Res for Del - Solvay - Init < 2013 | PC_QM_27 | Used when loading Solvay data for project inspection results for deliveries. Given the volumetry generated, it's easier to load the data year by year. This PC loads data depending on the creation date up to 31.12.2012 |
| TD - ADH - QM - BL - Insp Res for Del - Solvay - Init >=2013 | PC_QM_28 | Used when loading Solvay data for project inspection results for deliveries. Given the volumetry generated, it's easier to load the data year by year. This PC loads data depending on the creation date from 01.01.2013 |
| MD - D - QM - Attributes/Texts 0PHYS_SP | PC_QM_32 | Used to load Attributes and Texts from PF1/WP1 for 0PHYS_SP (Physical Sample Number) |
| META - D - QM - SA&D Broadcasts | PC_QM_33 | Used to launch broadcast mailing on MVQM01 |
Average performance
/ ! \ Cubes CRQM03 to CRQM06 contain very large dimensions (in term of percentage of full data), and as such are very slow to reload (= 3 hours for 2 weeks). → With HANA solution on CPQM01, we reduce time to a few minutes
| Key Figure | Estimation |
|---|---|
| ~ Average Process Chain Runtime | |
| ~ Average nb of rows loaded per load | |
| ~ Total nb of rows loaded (if full) | |
| ~ Average Runtime for 10k lines |
Record Keeping
Reporting
Queries End User Documentation
Main queries
| BW_QRY_CPQM01_001 | BW - Quality Results for Deliveries (core query-HANA) |
| BW_QRY_CPQM02_0001 | BW - QM - Results (Core Query) - HANA |
| BW_QRY_CPQM02_0011 | BW - QM - Inspection Results by Lot (Core Query) - HANA |
| BW_QRY_CPQM02_0005 | BW - QM - Results - Column (Core Query) - HANA |
| BW_QRY_CPQM02_0013 | BW SILICA - Résultats QM - HANA |
| BW_QRY_CPQM02_0006 | BW - Analytical time (Core Query) - HANA |
| BW_QRY_CPQM02_0002 | BW-QM Statistics (Core Query) |
| BW_QRY_CPQM02_0001 | BW - QM Results (Core Query) |
| BW_QRY_CPQM02_0005 | BW - QM - Results - Column (Core Query) |
| BW_QRY_CPQM02_0007 | QM - Equip - Maintenance (Corr/Prev/Adj) Core Query - HANA |
| BW_QRY_CPQM02_9003 | QM - Laboratory Billing (by Determinations) Core Query- HANA |
| BW_QRY_CPQM02_9004 | QM - Laboratory Productivity (by Workcenter)Core Query- HANA |
| BW_QRY_CPQM02_9005 | QM - Laboratory Productivity (by Analyst) Core Query - HANA |
Main functionalities
Broadcast
Two Broadcast settings exist, respectively on queries BW - QM - SA&D Environment Service Data (Broadcast) (BW_QRY_MVQM01_0005) and BW - QM - SA&D Esco Report (Broadcast) (BW_QRY_MVQM01_0006).
Both broadcasts are designed to send Previous Day data on a selection of Plants to selected users (not defined yet), in PDF format, on a daily basis. In the future, additional settings with one or more Plants separated from the rest may be requested by users.
Maintenance
Known bugs
Recurring procedure
Planned Evolution
Transformation done but not used. Only the update rules are used