APO> BW loadings are stopped  from 7th of June

General presentation

Objective of the application

This application extract the forecasts from the application APO. The data is loaded and aggregated in BW and made accessible through a multiprovider which regroups data from three application. APO, Dynasys and Logility. In time, application Logility will be terminated and the data coming from APO should be integrated in Dynasys.

Tool Leader + IT leader of the application: Celia Gonzales

Usage information

History

Golive In May 2017

Roles & Access

Roles and access

List of application role + menu role  and explanation if we have several applications role with special rules.

This should be the same as what's done for Dynasys.

Authorization objects

List of autorisation objects mandatory for the application.

This should be the same as what's done for Dynasys.

Dataflow overview

Reporting documentation drive folder:

https://drive.google.com/open?id=0BxkySrtHryoBeG05a0ljVWJpSmM

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

1 : Cleaning of incoming data before propagation datastore objects

C_MATNR2: adjust the format of the material number to the right number of digit, adding/removing zeros in the process.

0LOGSYS : Determination of system source

C_SHIPTID & C_SOLDID (only for Special Chemical): Removing of source system extension and adjustment of the format 

2 : Determination of previous period for C_FCSTMTH when loading the snapshots

It's usually the previous month. It can however be fixed to a specific value through the use of filters in masterdata C_GLBFILT

*DSO Business layer has key figures as Summation not Overwrite, it is required to delete old data before reload.

3 : Loading of attributes from MD for all DSOs

• BFC Global Business Unit (CPFCTR1_2) is retrieved from the corresponding attribute of Material Plant (C_MATPNT2).

C_MATPNT2 is determined through the coupling of material and plant and is used for business assignments (ERP or BFC). It contains multiple attributes mainly loaded through either the standard attributes from datasource 0mat_plant and the MD C_SUBACT2 (a mix of Profit center (WP1) and Business area (PF1, PI1 and RHO systems)).

• Customer Group - Enterprise (C_CUSTGRP) is retrieved from the corresponding attribute of Customer - Enterprise Axis (C_CUSTENT).
• Company code (C_COMPCODE) is retrieved from the corresponding attribute of Main Shipping Plant (C_MPPLANT).
• Sales Office (C_SAL_OFF) and Sales Document type (C_DOCTYP2) have been initialised with value APO.

4 : Loading of attributes from MD for loading coming from RCS (Every DSO except the two for Special Chemicals)

• Division (0DIVISION) is retrieved from the corresponding attribute of Material (C_MATNR2).
• Sub-Activity (0G_CWWE01), also known as Iecra, is retrieved from the corresponding attribute of Technical GBR Deter CDSA Payer (C_CDSA).

C_CDSA contains the distribution channel coupled with division. The IECRA can also be found in C_MATPNT2, but it's value is wrong.

5 : Loading of attributes from MD for loading coming from SOLVAY (Only the DSO for Special Chemicals)

• Material Group (C_MAT_GRP) is retrieved from the corresponding attribute of Material (C_MATNR2).
• Ship-To-Country (C_SHTCTRY) is retrieved from the corresponding attribute of Ship-To-Party (Core) (C_SHIPTID).
• Sub-Activity (0G_CWWE01) is retrieved from the corresponding attribute of Material Plant (C_MATPNT2).

IECRA is specified in this dataflow as an information but is not reliable. IECRA is not an organizational structure used in Solvay.

• Commercial Product/Material Group (C_PROD) is retrieved from the corresponding attribute of Material (C_MATNR2).

Commercial Product for RCS and Material Group for Solvay

6 : Keyfigures in summation when loading the business datastore objects

Data in APO can't really be aggregated in a DSO as every characteristic is key (like in an infocube). This means that the semantic keys which were determined for the propagation level is not absolute. Since the DSO are in write optimized, there is no loss of data thanks to the techical keys. It is necessary however to load the key figures in summation at business level.

7 : Determination of Concerned Period (C_FLGMTH) for dynamic dataflow

We are always loading data for current month M

8 : Determination of Concerned Period (C_FLGMTH) for snapshot dataflow

The month view is determined depending on the difference between the period 0CALMONTH of the forecast and the considered period C_FCSTMTH (date of the photo).

IF 0CALMONTH = C_FCSTMTH THEN C_FLGMTH = 'M'

ELSE IF 0CALMONTH > C_FCSTMTH THEN C_FLGMTH = 'M-2, M-3,...' (There is no M-1)

ELSE C_FLGMTH = 'N/A'

Examples:

9 : Determination of GH quantity and bias for snapshot dataflow

For each snapshot loaded in a business datastore object, we rework the entire datastore object. We update the Gross History quantity for past snapshots using the latest GH quantity depending on the keys of the DSO.

There is 4 bias which are calculated:

• The bias for the final forecast is calculated through the following:  ABS ( FF Quantity - GH quantity).
• The bias for the sales team forecast is calculated through the following:  ABS ( STF Quantity - GH quantity).
• The bias of the statistical forecast is calculated through the following:  ABS ( STATF Quantity - GH quantity).
• The bias of the demand review forecast (shifted forecast) is calculated through the following: ABS (DRF Quantity - GH quantity). *Only for special chem 

Dependencies with other applications

This dataflow is using the same multiprovider than the dataflow for dynasys. Therefore when modifying multiprovider MVDYN11, beware of the impacts.

The loading of the snapshot part is done on the same day than dynasys.

Qlikview is also use query of APO QV_BW_QRY_MVDYN11_0005

Data loadings

Info providers and objects loaded

There is a high volume of data for the snapshot dataflow, which is why a clean-up was established during the loading in the PC. We keep only 36 months of history. The deletion process is done on every layer.

As the snapshot and dynamic dataflow use the same source, a decision tree was used to stop one loading if the other is already on-going.

Loading frequency

There is two master PC:

• The loading of the dynamic data is done once a day
• The loading of the snapshot data is done on the 6th of each month

Average performance

Record Keeping

36 Months of history.

The cleanup is made in the process chains. Cleanup for business and reporting layers is made in the corresponding PC, but there is a specific PC for propagation level, which is launched when the whole dataflow's loading ended.

DSO Backup

The DSO DBAPO012 is available if necessary to backup the data of a dso on business layer. This is used especially when there is a need to change the structure of a core DSO. Data must be backed up DBAPO012 before modification of a dso structure, then reloaded from DBAPO012 to datastore after modification.

Kick-off of APO data in Dynasys

Ultimately, the data provided in APO will be delivered through Dynasys. When this happens, the loadings in APO must be stopped. If not, this will generates duplicate data at reporting level.

This happened for example for the APO flow Polyamide Downstream. Part of the data currently being loaded through APO was then being loaded through Dynasys. This means the previous loading at propagation layer needed to be modified. The rules were given by functional User. It entails using two infopackages to load two subsets of the data:

• The first subset is filtered on the hard coded enterprise code ENTRP00030 and a list of sales groups specified in Global filter
• The second subset is filtered on a list of given enterprise codes specified in the global filter

Of course for future needs the filters to be applied may be partly or completely different

Reporting

Queries End User Documentation

See Query definition under Dynasys.

Main queries

Main functionalities

Broadcast

Maintenance

Beware of the impacts when working on the multiprovider or the queries, as they are also used by Dynasys.

Known bugs

• The sommation for the key figures coming from special Chemicals is not done. 
• Demand review forecast can't be reload. Therefore, it can be use from Apr 2018.  Data before Apr 2018 will be incorrect since the first load has the rule "Over write" on the DSO level and it can't be fixed by reloading. 

Recurring procedure

Procedure to reload manually APO Novecare during current month.

 The normal APO snapshots loading takes place on the 6th of each month , but It may happen that the Business requires a new snapshot reload during the same month. The procedure below details how this is done :

The data flow for APO Novecare is as follows :

Z20_DP_PLAREA (Data Source) => DPAPO008 (DSO) ==> DBAPO007 (DSO) => CRAPO005 (InfoCube).

In order to be able to do data reloads in the WBP system, you need to log on as a Firefighter by using transaction /n/Virsa/VFAT .,See documentation on following link : https://wiki.solvay.com/pages/viewpage.action?spaceKey=TECHREP&title=How+to+use+Firefighter+on+WBP

Please follow these steps :

1 -  Deletion of the data loaded on the 6th of  the month  :

Highlight the data load that occurred on the 6th of the month and delete it manually in DSO DPAPO008.

For DBAPO007 and CRAPO005 do a complete deletion of data .

Start by deleting the load in DBAPO007 to be able to delete the load of DPAPO008.

Then delete data completely in DSO DPAPO007 and InfoCube CRAPO005.

Highlight the Infoprovider , right-click, then Delete data

2 -  Data reload from Data Source 9AZ20_DP_PLAREA. :  Delete the data in PSA.

3 - Run InfoPackage  IP: 9AZ20_DP_PLAREA - APO - Full

This IP is the most time consuming. It will take anything between 6 to 8 hours for about 15 million records .

4 - Then load DPAPO008 :  

' Run DTP DTP: 9AZ20_DP_PLAREA / APO -> DPAPO008 - Full. This should take about  1h1/2

Then activate DSO.

5 - Load DSO DBAPO007.

Run DTP  DTP: DPAPO008 - > DBAPO007 - Delta. This will take under 4 hours. Then activate DSO

Then update again DPAPO007 on itself : Run DTP DTP: DBAPO007 -> DBAPO007 - Full - GH Value and Bias update

6 - Load InfoCube CRAPO005.

Delete indexes on InfoCube CRAPO005 in Manage => Performance tab

Then delete also indexes on InfoCube  CRAPO005 on the BW Accelerator System :

Highlight the Cube, then right-click, then 

Then on following screen delete BIA indexes on CRAPO005 :

Then Run DTP  DTP: DBAPO007 -> CRAPO005 - Full M to M-6

Rebuild indexes on InfoCube CRAPO005 on the same fashion as described above for index deletion.

End of Procedure.

Planned Evolution

+

APO> BW loadings are stopped  from 7th of June

General presentation

Objective of the application

This application extract the forecasts from the application APO. The data is loaded and aggregated in BW and made accessible through a multiprovider which regroups data from three application. APO, Dynasys and Logility. In time, application Logility will be terminated and the data coming from APO should be integrated in Dynasys.

Tool Leader + IT leader of the application: Celia Gonzales

Usage information

History

Golive In May 2017

Roles & Access

Roles and access

List of application role + menu role  and explanation if we have several applications role with special rules.

This should be the same as what's done for Dynasys.

Authorization objects

List of autorisation objects mandatory for the application.

This should be the same as what's done for Dynasys.

Dataflow overview

Reporting documentation drive folder:

https://drive.google.com/open?id=0BxkySrtHryoBeG05a0ljVWJpSmM

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

1 : Cleaning of incoming data before propagation datastore objects

C_MATNR2: adjust the format of the material number to the right number of digit, adding/removing zeros in the process.

0LOGSYS : Determination of system source

C_SHIPTID & C_SOLDID (only for Special Chemical): Removing of source system extension and adjustment of the format 

2 : Determination of previous period for C_FCSTMTH when loading the snapshots

It's usually the previous month. It can however be fixed to a specific value through the use of filters in masterdata C_GLBFILT

*DSO Business layer has key figures as Summation not Overwrite, it is required to delete old data before reload.

3 : Loading of attributes from MD for all DSOs

• BFC Global Business Unit (CPFCTR1_2) is retrieved from the corresponding attribute of Material Plant (C_MATPNT2).

C_MATPNT2 is determined through the coupling of material and plant and is used for business assignments (ERP or BFC). It contains multiple attributes mainly loaded through either the standard attributes from datasource 0mat_plant and the MD C_SUBACT2 (a mix of Profit center (WP1) and Business area (PF1, PI1 and RHO systems)).

• Customer Group - Enterprise (C_CUSTGRP) is retrieved from the corresponding attribute of Customer - Enterprise Axis (C_CUSTENT).
• Company code (C_COMPCODE) is retrieved from the corresponding attribute of Main Shipping Plant (C_MPPLANT).
• Sales Office (C_SAL_OFF) and Sales Document type (C_DOCTYP2) have been initialised with value APO.

4 : Loading of attributes from MD for loading coming from RCS (Every DSO except the two for Special Chemicals)

• Division (0DIVISION) is retrieved from the corresponding attribute of Material (C_MATNR2).
• Sub-Activity (0G_CWWE01), also known as Iecra, is retrieved from the corresponding attribute of Technical GBR Deter CDSA Payer (C_CDSA).

C_CDSA contains the distribution channel coupled with division. The IECRA can also be found in C_MATPNT2, but it's value is wrong.

5 : Loading of attributes from MD for loading coming from SOLVAY (Only the DSO for Special Chemicals)

• Material Group (C_MAT_GRP) is retrieved from the corresponding attribute of Material (C_MATNR2).
• Ship-To-Country (C_SHTCTRY) is retrieved from the corresponding attribute of Ship-To-Party (Core) (C_SHIPTID).
• Sub-Activity (0G_CWWE01) is retrieved from the corresponding attribute of Material Plant (C_MATPNT2).

IECRA is specified in this dataflow as an information but is not reliable. IECRA is not an organizational structure used in Solvay.

• Commercial Product/Material Group (C_PROD) is retrieved from the corresponding attribute of Material (C_MATNR2).

Commercial Product for RCS and Material Group for Solvay

6 : Keyfigures in summation when loading the business datastore objects

Data in APO can't really be aggregated in a DSO as every characteristic is key (like in an infocube). This means that the semantic keys which were determined for the propagation level is not absolute. Since the DSO are in write optimized, there is no loss of data thanks to the techical keys. It is necessary however to load the key figures in summation at business level.

7 : Determination of Concerned Period (C_FLGMTH) for dynamic dataflow

We are always loading data for current month M

8 : Determination of Concerned Period (C_FLGMTH) for snapshot dataflow

The month view is determined depending on the difference between the period 0CALMONTH of the forecast and the considered period C_FCSTMTH (date of the photo).

IF 0CALMONTH = C_FCSTMTH THEN C_FLGMTH = 'M'

ELSE IF 0CALMONTH > C_FCSTMTH THEN C_FLGMTH = 'M-2, M-3,...' (There is no M-1)

ELSE C_FLGMTH = 'N/A'

Examples:

9 : Determination of GH quantity and bias for snapshot dataflow

For each snapshot loaded in a business datastore object, we rework the entire datastore object. We update the Gross History quantity for past snapshots using the latest GH quantity depending on the keys of the DSO.

There is 4 bias which are calculated:

• The bias for the final forecast is calculated through the following:  ABS ( FF Quantity - GH quantity).
• The bias for the sales team forecast is calculated through the following:  ABS ( STF Quantity - GH quantity).
• The bias of the statistical forecast is calculated through the following:  ABS ( STATF Quantity - GH quantity).
• The bias of the demand review forecast (shifted forecast) is calculated through the following: ABS (DRF Quantity - GH quantity). *Only for special chem 

Dependencies with other applications

This dataflow is using the same multiprovider than the dataflow for dynasys. Therefore when modifying multiprovider MVDYN11, beware of the impacts.

The loading of the snapshot part is done on the same day than dynasys.

Qlikview is also use query of APO QV_BW_QRY_MVDYN11_0005

Data loadings

Info providers and objects loaded

There is a high volume of data for the snapshot dataflow, which is why a clean-up was established during the loading in the PC. We keep only 36 months of history. The deletion process is done on every layer.

As the snapshot and dynamic dataflow use the same source, a decision tree was used to stop one loading if the other is already on-going.

Loading frequency

There is two master PC:

• The loading of the dynamic data is done once a day
• The loading of the snapshot data is done on the 6th of each month

Average performance

Record Keeping

36 Months of history.

The cleanup is made in the process chains. Cleanup for business and reporting layers is made in the corresponding PC, but there is a specific PC for propagation level, which is launched when the whole dataflow's loading ended.

DSO Backup

The DSO DBAPO012 is available if necessary to backup the data of a dso on business layer. This is used especially when there is a need to change the structure of a core DSO. Data must be backed up DBAPO012 before modification of a dso structure, then reloaded from DBAPO012 to datastore after modification.

Kick-off of APO data in Dynasys

Ultimately, the data provided in APO will be delivered through Dynasys. When this happens, the loadings in APO must be stopped. If not, this will generates duplicate data at reporting level.

This happened for example for the APO flow Polyamide Downstream. Part of the data currently being loaded through APO was then being loaded through Dynasys. This means the previous loading at propagation layer needed to be modified. The rules were given by functional User. It entails using two infopackages to load two subsets of the data:

• The first subset is filtered on the hard coded enterprise code ENTRP00030 and a list of sales groups specified in Global filter
• The second subset is filtered on a list of given enterprise codes specified in the global filter

Of course for future needs the filters to be applied may be partly or completely different

Reporting

Queries End User Documentation

See Query definition under Dynasys.

Main queries

Main functionalities

Broadcast

Maintenance

Beware of the impacts when working on the multiprovider or the queries, as they are also used by Dynasys.

Known bugs

• The sommation for the key figures coming from special Chemicals is not done. 
• Demand review forecast can't be reload. Therefore, it can be use from Apr 2018.  Data before Apr 2018 will be incorrect since the first load has the rule "Over write" on the DSO level and it can't be fixed by reloading. 

Recurring procedure

Procedure to reload manually APO Novecare during current month.

 The normal APO snapshots loading takes place on the 6th of each month , but It may happen that the Business requires a new snapshot reload during the same month. The procedure below details how this is done :

The data flow for APO Novecare is as follows :

Z20_DP_PLAREA (Data Source) => DPAPO008 (DSO) ==> DBAPO007 (DSO) => CRAPO005 (InfoCube).

In order to be able to do data reloads in the WBP system, you need to log on as a Firefighter by using transaction /n/Virsa/VFAT .,See documentation on following link : https://wiki.solvay.com/pages/viewpage.action?spaceKey=TECHREP&title=How+to+use+Firefighter+on+WBP

Please follow these steps :

1 -  Deletion of the data loaded on the 6th of  the month  :

Highlight the data load that occurred on the 6th of the month and delete it manually in DSO DPAPO008.

For DBAPO007 and CRAPO005 do a complete deletion of data .

Start by deleting the load in DBAPO007 to be able to delete the load of DPAPO008.

Then delete data completely in DSO DPAPO007 and InfoCube CRAPO005.

Highlight the Infoprovider , right-click, then Delete data

2 -  Data reload from Data Source 9AZ20_DP_PLAREA. :  Delete the data in PSA.

3 - Run InfoPackage  IP: 9AZ20_DP_PLAREA - APO - Full

This IP is the most time consuming. It will take anything between 6 to 8 hours for about 15 million records .

4 - Then load DPAPO008 :  

' Run DTP DTP: 9AZ20_DP_PLAREA / APO -> DPAPO008 - Full. This should take about  1h1/2

Then activate DSO.

5 - Load DSO DBAPO007.

Run DTP  DTP: DPAPO008 - > DBAPO007 - Delta. This will take under 4 hours. Then activate DSO

Then update again DPAPO007 on itself : Run DTP DTP: DBAPO007 -> DBAPO007 - Full - GH Value and Bias update

6 - Load InfoCube CRAPO005.

Delete indexes on InfoCube CRAPO005 in Manage => Performance tab

Then delete also indexes on InfoCube  CRAPO005 on the BW Accelerator System :

Highlight the Cube, then right-click, then 

Then on following screen delete BIA indexes on CRAPO005 :

Then Run DTP  DTP: DBAPO007 -> CRAPO005 - Full M to M-6

Rebuild indexes on InfoCube CRAPO005 on the same fashion as described above for index deletion.

End of Procedure.

Planned Evolution

+