The new wiki link for this data flow is here:

01-DynaSys Reporting 

Please update the new doc there and no longer here.

General presentation

Objective

of the application

"DynaSys Reporting" application deals with :

1. extract forecasts & few master information from DynaSys system (DP/DiP-PP modules) using an Hana SDI access on the DynaSys datahub
2. provide Core queries to GBU users to follow their activity
3. provide technical queries to fill external tool like QlikSense, Dataïku, Tableau (via Google Cloud Platform or GCP)

                                                                                              (migration QV>Tableau in progress)

Main contacts are : 

Usage information

History

Roles & Access

Roles and access

See also file maintained by Authorization team : BW Catalog of Roles / link: https://drive.google.com/open?id=10GEfKYqrT1eeTO_uHYAheL1GX7L5y_pvH0KQU64qh5I

Authorization objects

Reporting is limited to authorized GBU (Global Business Unit)

Dataflow overview

Important : these google slides are the main document to consider for the reporting topic. It has to be updated for any change to facilitate maintenance

List of DynaSys views used for extraction

Functional and Technical rules

Rules & Explanations

IECRA determination (implemented since 13/11/2017)

RCS notion, IECRA is only determined for RCS records.

1. Retrieve the division (0DIVISION) from material (C_MATNR2)

2. Get IECRA (0G_CWWE01) from masterdata C_CDSA which is distribution channel (0DISTR_CHAN) compounds with division (0DIVISION).

Determination done at Business Level (EndRoutine)

BU (C_DYN_010) / GBU (CPFCTR1_2) determination

Both BU and GBU are determined at Business Level (EndRoutine) by reading mastersdata C_DYN_005 / Material with System Extension.

The link Material / BU is given by DynaSys interface part : DSO DBDYN02.

The link BU/GBU is given by C_SUBACT2 / Sub-Activity 2

BU = ERP Group of activities (C_PFCTR2)

GBU = BFC Global Business Unit (CPFCTR1_2)

Rule for RCS records was changed the 13/11/2017 (item #58). BU/GBU is now determined reading IECRA (0G_CWWE01)

Sold-to (C_SOLDID) determination

The Sold-to is determined at Business Level (EndRoutine) by reading mastersdata C_DYN_014 / Ship-to with System Extension.

The link Ship-to / Sold-to is given by DynaSys (table EXP_DP_SHIPTO) and stored daily in masterdata C_DYN_014.

Commercial Product (C_PROD) determination

The commercial product is needed to be able to use attributs of masterdata C_GBR14 / GBR: Customer end-use Mat Grp (Ship-to)

The commercial product is determined by reading masterdata C_MATNR2 / Material

Sales Office (C_SAL_OFF) / Sales Document Type (C_DOCTYP2) / Company Code (C_COMPCDE) determination

Sales Office, Sales Document Type, Company code can not be determined for DynaSys, they are set by constant DYN because axis are needed in reporting Sales Follow-up.

Caution Sales Office is autorization relevant for Sales Reporting (Obas, Global Sales), it has been added in Application role, auhorization object ZBI_SD

Company Code also, it will be added ( item #39)

FORECAST ACCURACY - End of Month View (C_FCSTMTH)

For daily dynamic flow, it corresponds to current month/year

For snapshot flow, it corresponds to previous month of the snapshot

In case of a manual reload the value can be changed by using Global Filter 

FORECAST ACCURACY - Concerned Period (C_FCGMTH)

For daily dynamic flow, it is set with 'M' (mean view of the month 0CALMONTH

For snapshot flow, it is calculated :

1) If period (0CALMONTH) = end of month view (C_FCSTMTH) => Concerned Period (C_FCGMTH) is set to 'M' means value of the month

2) If period (0CALMONTH) < end of month view (C_FCSTMTH) => Concerned Period (C_FCGMTH) is set to 'N/A' means Not Applicable, SnapShot done after period

3) If period (0CALMONTH) > end of month view (C_FCSTMTH) => 

a) compute NB Month =  (period (0CALMONTH) - end of month view (C_FCSTMTH) ) - 1

b) if NB Month between -999 and -1 => concatenate 'M' with NB Month into Concerned Period  (C_FCGMTH) 

    else Concerned Period (C_FCGMTH)  is set to 'N/A' 

Caution : Concern period M-1 does not exists.

               Concern period M for Forecast is considered as M-1 in BW queries.

Examples:

DiP-PP - Datafields type & key figures

For the DiP reporting we use only two key figures :

• K_DYN_022 : Amount in €
• K_DYN_021 : Quantity in KG

The quantity or the amount is assigned to a specific Kpi determined by the field DataField Type (C_DATATYP).

In BW we've created one restricted key figure per Datafield type.

The list of the different Datafields loaded from Dynasys and their respective RKF is available here.

To know if the Datafield type is a quantity (assignment of the value to K_DYN_021) or an amount (assignment of the value to K_DYN_021), we're using Global filters entries.

Stream : DPS
Rule : DIP_KF_VAL

In this rule, we've listed the Datafield type expressed in value (amount). 

This Global filter variables are used in the transformation between AADPDY06 & APDPDY07 to make the assignment.

Some datafield types are also defined in hours on Dynasys. There is no changes on the value but only on the unit. All datafields defined with the "H" unit are listed in the global filter rule : DIP_KF_HR

In this case the unit assigned to the key Figure will be "H" (hour) . This assignment is done in the end routine of the transformation between AADPDY06 & APDPDY07.

DiP-PP - Shipping Site (Plant) & Receiving Site (Site) 

Transactional  data :

For the DiP reporting, some datafields are defined with only a Shipping site and some with also a Receiving site.

Datafields source :

• view EXP_DIP_BW_SKU_BW for datafields type without receiving type
• view EXP_DIP_BW_SKU_SITE_BW for datafields type with a receiving type

Following the cases, Shipping site and Receiving site can be :

1. A site (plant)
2. A ShipTo (customer)

The assignment is done in the propagation Layer :

• AADPDY06 -> APDPDY07 :
  • Shipping site : SHIPPINGSITE_COD => C_DYN_068
• AADPDY07 -> APDPDY08 :
  • Shipping site : SHIPPINGSITE_COD => C_DYN_068
  • Receiving site : RECEIVINGSITE_CO => C_DYN_025

Master data :

Somes attributes associated to Shipping and Receiving site are send by Dynasys tool with the datasource DTS_CV_EXP_DIP_BW_MD_SKU_BW based on the Dynasys view EXP_DIP_BW_MD_SKU_BW.

The content of the view is stored in the master data C_DYN_074 (Shipping site).

There is also another master data C_DYN_084 (Receiving site) which is in reference of C_DYN_074

To determine if a Shipping (receiving) Site is a plant or a ship-to, we'll use the attribute "TYPE_OF_SITE" C_DYN_078.

IF TYPE_OF_SITE = SHIPTO => Shipping or receiving site is a ShipTo code

ELSE => Shipping site or receiving site is a Plant code

This test is used in the calculation view CV_DY_DIP_REPORTING to catch attributes from C_PLANT or C_SHIPTO master data following the record :

It is also used to know where the description have to be loaded.

IF it's a shipTo, the description is loaded from C_CUSTID.

If it's a plant, the description is loaded from C_PLANT.

The check is done in the transformation between the different master data :

• C_PLANT => C_DYN_074 (Shipping Site description if it's a plant)
• C_SHIPTO => C_DYN_074 (Shipping Site description if it's a shipTo)
• C_PLANT => C_DYN_084 (Receiving Site description if it's a plant)
• C_SHIPTO => C_DYN_084 (Receiving Site description if it's a shipTO)

BW objects assignments are described in details here.

There is another specific master data C_DYN_071 (material /plant) that is loaded from the ADSO APDPDY06 (view EXP_DIP_BW_SKU_BW).

A filter at the DTP level load only these datafields in the master data :

• PRICE_ADVISOR => C_DYN_080
• PRICE_ADVISOR_IMPACT => C_DYN_081
• SHSRLT => C_DYN_072
• STD_COST_SAP =>C_DYN_082

Assignment between transactional and master data :

The assignment is done in the calculation view in the projection of the ADSO ABDPDY53 :

• C_DYN_074 (master data Shipping Site) = C_DYN_068 (Transactional data)
• C_DYN_084 (master data Receiving Site) = C_DYN_025 (Transactional data) 

DiP-PP - Logsys assignment

The assignment of LOGSYS field is done on BW between acquisition and propagation layer reading by default the extension of the material code send by Dynasys.
=> See the end routine of the transformation between ADSO AADPDY06 → ADSO APDPDY07

But for some Datafields type, there is no Material code, we must use and read the extension of Shipping site code send by Dynasys to determine the LOGSYS
Datafields coming from the view SKU_MAIN_SITE always have a Material Code assigned.
See below the list of Datafields type without Material code :

In the end routine, we are using a Global filter entry with the list of these datafield type to determine if we are usgin the material or the shipping code to determine the logsys :

Stream : DPS
Rule : DIP_LOGSYS

AERO- Transcodification for BAAN Ship-to

Ship-to BAAN are longer than SAP Ship-to (10 characters). For technical reason and to fit into the 10 character SAP Ship-to, a transcodification has been handle on DynaSys side.

BW receives directly result of the transcodification, and consider it as the Ship-to.

BEM  xxxx CUS xxx can be found in masterdata C_DYN_024

AERO- How Program/Market assignement works ?

Always refer to Dataflow for a better understanding => DynaSys Reporting Dataflow

1) Program/Market assignment is managed in DynaSys. at Material/Ship-to/Distribution Channel/Main spec/Month level

2) assignments are uploaded into BW daily from monday to friday at 10.15pm from DynaSys view EXP_SAP_PROG_BW

NB : on BW a rule put value DUMMY if assignement is provided from DynaSys without program or if the sum of percentages are different from 100%.

AERO- How conversion to Base Unit or Sales Unit works ?

BAAN quantities are provided in Sales Unit (SU).

As this point was discoverd late during the project, and as BW model is mostly designed to collect only quantities in KG, BAAN quantities are stored in KG keyfigures.

So keep in mind, the unit is Not the correct one for all KG keyfigures

AERO- How the Final Forecast Cost is calculated ?

The Final Forecast Cost is a key figure available in the report :

We also have 3 characteristics available and associated to these key figures :

Storage on BW

The material cost are loaded at the beginning of every month in the cube CUB_PCP01.

it means the product cost is depending of the month, each month we can have the same value or a different one.

in SAP, we can check the cost in MM03 => Sheet : Costing 2.

Example : Material 160704 / Plant : 8350

 => 

On the BW Dynasys application, we load these same costs and stored them in the ADSO APDPDY05.

The ADSO is used in the calculation view CV_DY_BL_DYN22_CPDYN11 :

Final Forecast Calculation

The Final Forecast Cost amount is calculated like below :

FF Cost = Final Forecast (BaseUoM) * Product Cost

The FF Cost is calculated for each month in the past and also in the future.

As the product cost is not existing for the month in the future, we have copied the cost of the current month to the other month in the future.

Example : We are on August 2021, the lastest Cost value available is the cost of August 2021. They are are copied until the end of Current Year + 2 (rule defined with business)

Each month, the product costs in the future are re-calculated based on the latest cost values loaded and available.

We also apply the latest product cost to the previous month of the same year.

Example 1 : We are on August 2021, the lastest Cost value available is the cost of August 2021. Cost Values are copied on January 2021, February 2021 etc...

Example 2 : We are in January 2022, the cost values of december 2021 will not be updated with the cost values of january 2022. 
We keep the last version of cost for december 2021 and copy them in all the months of the year 2021.

On BW, it's done in the transformation of ADSO APDPDY05 on itself.

The calculation of the FF Cost is done at the calcualtion view level because we need Final Forecast (BaseUoM) quantities :

Exchange Rate

As the product cost are defined in the local currency, we have given the possibility to convert in a target currency (USD in our case) at the Query level :

We are using the same exchange rate for the amount measures in the report => CAR4.

Please find below a summary of the FF Cost calculation based on the previous rules exposed.

Example : Vision of August 2021, if we run the report for year 2021

CRM Prospect transcodification (18C>11C)

Prospects were not part of initial DynaSys project, so Ship-to lenght has been based on SAP Ship-to, 10 characters (11 characters to add extension B or R).

In reality CRM Prospect are defined in CRM on 18 characters (contact on CRM side josiane.peytraud@solvay.com)

A transcodification on 11 characters has been put un place on BW site to fit BW object defined on 11 characters.

Class ZBW_DPS_DYNASYS_UTILS => method GET_PROSPECT_TRANSCODED

* Some explanation from CRM team about CRM Prospect Code
* Positions 1,2,3   = type of object or table.For prospect, value is always 001
* Positions 4,5     = organisation, value is currently 24 or 1p for CRM Core
* Position 6        = reserved for SalesForce
* Position 7 to 15  = counter but sensible to upper/lower case
* Position 16,17,18 = important for unicity if upper/lower case is not kept

CRM Prospect Code is on 18 characters, has to be cut  to fit 11 characters
Ex= 0011p00001WzSnTAAV => 001WZSNTAAV

Dependencies with other applications

Data loadings

Process Chains

Loading frequency

Average performance

Record Keeping

Reporting

Queries End User Documentation

Main queries

Main functionalities

Broadcast

Maintenance

Tips & Tricks

Extract of 2019 Gross History as a reference

 File with 2019 GH by GBU to have an idea of volume & net sales

Forecast Accuracy reference.xlsx

Known bugs

Wrong amounts in Local Currency Yen (Currency JPY) - Issue closed

For low currency like JPY, SAP has its own internal format to store amounts.

Transformation has been adapted.

See routine

Caution this internal format has to be applied ONLY to keyfigures type Currency (CURR)

For keyfigures type Float (FLTP) routine has been removed as amounts in JPY where divided by 100. Check keyfigure definition.

Example to check

Material 32787 / Ship-to 88719 / Distrib Channel 7K / Source System RCS / GBU Aroma PA

Material 135918 / Ship-to 2066945 / Distrib Channel 7F / Source System RCS / GBU Aroma PA

Recurring procedure

How to request support Interface/Reporting to BW team ? (access, assistance...)

see this page

Reload a monthly snapshot from flat file 

Caution, the flow is always changing. The current mapping might not be up-to date and need to be adapted.

A snapshot reload by flat file has to be validated in WBQ first

File structure to use

https://drive.google.com/file/d/11QAqsvnBf9-Uayasyl4SprUqs7RPaHXl/view

Particularities :

• decimal point has to be used
  
• GH quantity has to be up-to date, as it will then be considered as the more accurate one
• file should not contain ANY prospects (otherwise data activation is not possible)

Upload manually DPDYN01 / DYN - Reporting (SnapShots)

Then follow upper steps as in the process chain  "DPS Dynasys: META - M - 6.Reporting DynaSys (Snapshots)" / PC_DPS_DYNASYS_09

Many reloads have been done to correct FA data or after organization changes

• April 2017 - SILICA - End of March snapshot reloading
  Reprise EXP_FORE SILICA.csv

• April 2022 - Merge CH to TS - all 2022

CH cleaning.csv

Reprise CH vers TS.csv

• July 2022 - TS (CH) - 6th March snapshot reload (request from Laure Pichot-Plasse / Aleksis Parfens)

Reprise TS - July 2022.csv

• Oct 2022 - Demerge TS to CH for periods from 10.2022
• April/May 2023 - rework on TS>CH demerge, period 01 to 09.2022 has been moved back to CH due to Sales Rep change which was not reflecting on FA from 01 to 09.2022

• July 2023 - PO2 Project - transcodification of PF1 DFU to new WP1 DFU

list of DFU to be transcoded (source DynaSys team)

1) prepare DFU file : split DFU new by Material/Ship-to/DC + split DFU old by Material/Ship-to/DC

2) extract all records from DPDYN01 regarding the Material/Ship-to/DC list (export xlsx)

NB: this extract contains also DFU which does not need to be transcoded (other combination Material/Ship-to/DC)

3) keep only DFU which need to be transcoded (build DFU as "198724S:2900375S@CSHHP" and do a vlookup with "DFU file")

4) copy/past records, sort by DFU/Month/Source System (WBP_HANAIV before SDK_DYNASY), remove duplicates

5) to limite number of volumes End of Month View > Forecast Month can be removed (these records will lead to a concerned period = N/A and have to be ignored for FA)

6) add a vlookup with "DFU file" to get new material, new ship-to, new DC

7) reorganize xlsx with following format

8) split file by source system SDK_DYNASY + WBP_HANAIV

9) change Global Filter to load SDK_DYNASY

Global Filter DPS/DYNASYS, put SDK_DYNASY

10) load the SDK_DYNASY

11) change back Global Filter to load WBP_HANAIV

Global Filter DPS/DYNASYS, put back WBP_HANAIV

12) load the WBP_HANAIV

13) Check result in DBDYN13 comparing few old DFU, vs new DFU

14) Build a file to delete old DFU, and follow deletion procedure

15) Recheck in DBDYN13

16) Reload CRDYN13

Reload a monthly snapshot from DynaSys table EXP_FORE_BW

If it is concerning GBUs (except CM AERO), snapshot to reload has to be previously removed from all level :

• DBDYN13 / DYN - Reporting (SnapShots) : delete full request then the delta request
• DPDYN01 / DYN - Reporting (SnapShots) : delete delta for GH line creation then the full request
• + PSA (not mandatory as DTP is taking only last request)

If it is also concerning CM AERO, do it also for

• ABDYN23 / DYN - Reporting AERO (Snapshots)
• APDYN07 / DYN - Reporting AERO (SnapShots)
• + PSA (not mandatory as DTP is taking only last request)

If reload is done the same month than the deleted snapshot, skip unwanted steps in the chain PC_DPS_DYNASYS_09 (transaction RSPC)

 Via SE37, Module RSPC_API_CHAIN_START, run the PC_DPS_DYNASYS_09

Follow chain using monitor (ST13) and when finished check the Forecast Accuracy in the BW report.

Import :

1) unskip steps

2 ) manually schedule again PC_DPS_DYNASYS_09 for next month

To do this, first, transaction RSPC, menu execution>remove from schedule (steps will become grey instead of green), then change starter by right click

 change the dates to next month

Execution > Schedule

3 ) If the recover is not done the 6th of the month, you have to inform Tableau's team to reload the application (Tableau should be uploaded the 7th of the month, as it was for the QV application)

*** Service one ticket *** to be updated with an example

Additional information in case recover is concerning the previous month

If much later, change Global Filter to set the wanted month and activate the line, and run chain PC_DPS_DYNASYS_09

 Caution variable has to be unactivate afterwards, otherwise following snapshot will be fix to that month!

Field 'Active' = Y or N

For info, the variable is used to fix "End of Month View" (C_FCSTMTH)

Increase/decrease DR upload from DynaSys

Change values in Global Filter (C_GLBFILT)

Put Active = 'N' to unactive an existing loading.

Add a new line for an extra loading. Please keep the same logic, all loading times are recorded chronologically to make reading easier.

 Do not forget to r ecord your modifications in paragraph "Process Chains" in PC_DPS_DYNASYS_15 comments

Increase/decrease DIP/PP  upload from DynaSys

Change values in Global Filter (C_GLBFILT)

Stream = DPS

Rule = PCH_DYN15M

Put Active = 'N' to unactive an existing loading.

Add a new line for an extra loading. Please keep the same logic, all loading times are recorded chronologically to make reading easier.

Add or remove email address in distribution list for DR data availability  (All GBU except CM (aero))

NB: Distribution list exists on all systems, but user list is only maintained in production system WBP

Transaction SO23 

Name DL_DP_001

Add or remove email adresses. 

Do not forget to r ecord your modifications in that paragraph

List of user email :

SodaAsh&Derivated (SD):

• carlos.lau@solvay.com 
• alessandro.caleffi@solvay.com => added 25/01/2018
• alessandro.marconcini@solvay.com => added 25/01/2018
• geert.schodts@solvay.com => added 25/01/2018
• irina.piticas@solvay.com => added 15/07/2019
• bianca.cavagioni@solvay.com => added30/11/2020
• philipp.kunze@solvay.com

AROMA (PA):

• marie.pereira@solvay.com => added 28/05/2018 
• teresa.hipsley@solvay.com => added 27/02/2019
• carolyn.wacker@solvay.com

Special Chem (CH):

• joerg.braunschweig@solvay.com
• christoph.piesbergen@solvay.com

Technology Services :

• anne.delrue@solvay.com=> added 21/06/2018 
• laure.pichot-plasse@solvay.com => added 05/12/2017
• stacey.walker-robertson-contractor@solvay.com  => added 21/06/2018
• vanessa.goulart@solvay.com

• Kevin.Richardot@solvay.com => addded 18/03/2022

• aleksis.parfens@solvay.com
• laura.labbadi@solvay.com

Oil&Gas (OG):

• john.hodges@solvay.com  => added 21/06/2018

Digital Technology :

• maggie.ma@solvay.com  => added  07/08/2019
• etienne.ribet-ext@solvay.com => added 02/01/2020
• nathalie.perrillatcollomb@solvay.com => added 18/08/2021
• valerie.bainbridge@solvay.com
• anass.dalili@solvay.com
• charlotte.bruchet@solvay.com

Removed 

• ann.bailly-ext@solvay.com        removed 12/02/2019
  

Add or remove email address in distribution list for DR data availability (GBU CM (aero))

NB: Distribution list exists on all systems, but user list is only maintained in production system WBP

Transaction SO23 

Name DL_DP_002

Add or remove email adresses. 

Do not forget to r ecord your modifications in that paragraph

List of user email :

Composite Materials (CM):

• amanda.pullen@solvay.com => added 22/01/2020
• kp.nagabhushana@solvay.com => added 22/01/2020
• patricia.harrison@solvay.com => added 22/01/2020
• penny.wu@solvay.com => 18/02/2022
• susan.lloydkane@solvay.com

Digital Technology:

• maggie.ma@solvay.com  => added 22/01/2020
• etienne.ribet-ext@solvay.com => added 22/01/2020
• valerie.bainbridge@solvay.com
• nathalie.perrillatcollomb@solvay.com => added 18/08/2021
• anass.dalili@solvay.com
• charlotte.bruchet@solvay.com

Removed 

Do not forget to r ecord your modifications in that paragraph

Add or remove email address in distribution list for DiP-PP reporting

NB: Distribution list exists on all systems, but user list is only maintained in production system WBP

Transaction SO23 

Name DL_DP_004

Do not forget to r ecord your modifications in that paragraph

Digital Technology:

• nathalie.perrillatcollomb@solvay.com 
• arnaud.regnault-de-la-mothe@solvay.com 
• duygu.karaca-ext@solvay.com  
• francois.le@solvay.com
• marie.baer@solvay.com
• soukaina.elmouaddib-ext@solvay.com
• valentin.mazet-ext@solvay.com
• anass.dalili@solvay.com
• maggie.ma@solvay.com

Removed 

Do not forget to r ecord your modifications in that paragraph

Modify email message for DR data availability sent to users (All GBU except CM (aero))

Email message has to be modified in WBD and moved to WBQ, then WBP.

 RSA1 / Chain PC_DPS_DYNASYS_15

Change to 'change' mode

right click => maintain message => Successful => Edit Document 

Do not forget to r ecord your modifications in that paragraph.

Modify email message for DR data availability sent to users (GBU CM (aero)

Email message has to be modified in WBD and moved to WBQ, then WBP.

 RSA1 / Chain PC_DPS_DYNASYS_15

Change to 'change' mode

right click => maintain message => Successful => Edit Document 

Do not forget to r ecord your modifications in that paragraph.

Modify email message for DIP/PP data availability sent to users 

Email message has to be modified in WBD and moved to WBQ, then WBP.

 RSA1 / Chain PC_DPS_DYNASYS_15

Change to 'change' mode

right click => maintain message => Successful => Edit Document 

Do not forget to r ecord your modifications in that paragraph.

Clean Forecast Accuracy for all GBU except CM-AERO (ABAP ZBW_DYN018)

Careful, this action is definitive !!!!

Program ZBW_DYN018 / DYN - Forecast Accuracy cleaning (Selective del in DBDYN13 + DPDYN01)

This program has been created to delete data from Forecast Accuracy DSOs.

1) prepare input file as following template

prospect reference (like 0011p00002banehAAA) have to be removed from the list

fill interval of months (even if deletion is asked for a single month)

2) run abap

3) reload CRDYN13 cube

May 2021 : Cleaning has been done for ODYSSEY project (NOVECARE) as Open Gate was not going to keep DynaSys

> Genthin from January 1, 2021 [Carve out]
> UP / Halifax from March, 1rst [Carve out]

ODYSSEY FA Genthin Cleaning.xlsx

ODYSSEY FA UPHalifax Cleaning.xlsx

May 2021 : Cleaning has been done for Silica

Silica FA Cleaning.xlsx

July 2021 : Cleaning has been done for SA&D LATAM / ODYSSEY / WAREHOUSE MULTIMODAL LOGISTICS (NOVECARE) / SILICA

FA Cleaning ODYSSEY & others 072021.xlsx

Feb 2022 : Cleaning has been done for Novecare / RASAL Plant from JAN 2022

FA Cleaning RASAL.xlsx

April 2022 : Cleaning has been done for Novecare / RASAL Plant from JAN 2022 (2nd round)

CS-Analysis-RasalCleaning-March22.xlsx

March 2023 : Augeo products moved from Novecare to Coatis. DFU were set to un-active on DynaSys. FA needed to be cleaned from JAN 2023

Cleaning has been done for AUGEO materials identified (DFU list provided by DynaSys) from JAN 2023 

AUGEO PF1 CLEANING.xlsx

AUGEO WP1 CLEANING.xlsx

AUGEO WP1 CLEANING O&G DFU.xlsx (cleaning will be seen in BW/Qlik with April 2023 upload). CS has validated DFU 138115R:2034644R@06R O&G can be cleaned.

WO0000000319691 => duplicate DFU (and hybride)

Perioding cleaning to limit DSO/Cubes volume

For FA and BR/MTP reports, 

• only GH for current year + 3 years of history is needed
• only FA for  current year + 2 years of history is needed
• only FA for last 6 snapshots is needed 

A selective deletion can be done every year to reduce significantly the volume of records in DSOs/Cubes

July 2023 - Cleaning : Removing Coating / PAX and non useful data 

Gain reduction of 33% for DBDYN13, 50% for DPDYN01

Another challenge would be to remove 30 millions of records in DBDYN13 for concerned period = 'N/A'  (Work identified in JIRA)

MTP - How to change archive month for current year

The month of MTP archive is running yearly, the 6th of the month entered in variable MTP_LOAD

The month was set to October => 10

If it is decided to change it. It can be done by changing the Global Filter.

Caution, if the change is done after the 6th of October, the archive done the 6th of October will have to be removed first.

MTP - How to reload a MTP archive

Remove the old archive with a selective deletion of ABDPDY04  (to check how to do it with ADSO)

Change the year for the archive and set Active = Y in Global Filter (DPS/PHOTO_MTPY)

Load ABDPDY04 via flat file flow

Do not forget to inactivate Global Filter (DPS/PHOTO_MTPY) afterwards (otherwise it will be erased by next automatic MTP archive)

2020 has been manually loaded via flat file Reprise EXP_MTP History_CH_2020.csv

2021 was wrongly filled with oct 2022 data

2022 was manually archived the 19th of April 2023

Ship-to/Forecast Customer descriptions onshot loading for CM-AERO and BAAN/PE1 cases

Situation : Ship-to or Forecast Customer description are missing in AERO Demand Review report (BW_QRY_CPDYN11_0006)

This is usually due to mixte DFU (RCS material (extension R) with BAAN or PE1 ship-to (extension B)

1) File format expected

BAAN Customer Forecast Description.csv

2) check file - listed customers should not already have description in table /BIC/TC_CUSTID for language = 'EN'

Caution : if description already exists for the source system, if you load a new description it will impact all BW application. Object C_CUSTID is widely used.

3) load file

=> emty PSA

=> IP: DTS_C_CUSTID_TEXT (PC_FILE) - Full  (change File Name first)

=> check PSA

=> WBP DTP: DTS_C_CUSTID_TEXT (PC_FILE)-> C_CUSTID

4) Description are instantly available in reporting

Main Evolutions (planned or done)

Decommissioning of BW query. Done for Demand Review S1.2023

The Demand Review (Core query) has to be decommissioned, replaced by existing Budget Review (Core query)

Reason : keyfigures/axis of DR are included in BR. There is no real need to maintain the DR query.

To handle the decommission : 

Step 1- Creation of a procedure to help users to switch existing workbooks which are based on the Demand Review query to
the Budget Review query => Wiki Change the Query Source In an Existing Workbook

Step 2- Collection of usage statistics via a workbook based on query  BW_QRY_CUB_0TCT_C01_0002_GTT / GTT2 –BI Statistics report

Workbook => Query Usage Statistics.xlsx

Example of workbook execution

Step 3- Store informations in a google sheet (link) to follow decommissioning

a) Copy usage statistics

b) List users information in a separated tab

c) Add email, name and GBU

Based on the User ID, get the email and GBU using the BMC helix platform as shown below:

Search the ID of the user in the search section while filtering on “People”:

d) In usage statistics tab, use a vlookup to fill email & GBU

Step 4- Communication to sent to users listed in the usage statistics

Step 5- Ask DynaSys DP Application owner to share also the info during regular meeting with the GBU

Step 6- Every begin of month, redo from step 2- and send a reminder to users which are still identified in the usage statistics.

Exclude users from DT team as they are aware of the decommissioning.

Step 7- If report is still mainly used by a GBU, ask DynaSys DP Application owner to emphasize the information

Step 8- create a ticket to ask to remove the decommissioned query from role menu (this can be done in advance)

Step 9- create a ticket to ask for query name change as Obsolete (to be done)

Step 10- update WIKI and documentation