Update frequency
Refresh twice a day from BW query to data lake. Every time the data is refreshed, the price forecast is being recalculated. 
| Requirements |
|---|
| Descriptions |
|---|
| Comments | Source |
|---|
Technical name of the source in transparency
Transformation
Material
For sale transactional data (COPA03) →
WP1: Production_plant_in_Material_W_Key
PF1: Origin_Plant_Key
For forcast COPC07
Manufacturing_Plant_Key
----------------------------------
Not relevant in this Q (planned for q4) → COPC04→ WP1 : C_PLANT PF1: ORIGIN PLANT
forecast→ copa07
Depends on what transactional data we want to use in the fact table we use various queries. For example
There is file to categorize p&l element in transparency dashboard
| Fields in the BW (Sales Forecast Revenue QV_BW_QRY_CPCOPC07_0001) | Transformation | ODS | ||||
|---|---|---|---|---|---|---|
| Loading source | In sales forecast query there are two loading sources where the forecast transactional data are populated including DYNASYS and ORDERBOOK | In the context of price forecast in the transparency dashboard the ORDERBOOK as the source is irrelevant | QV_BW_QRY_CPCOPC07_0001 | [C_SOURCE] Loading Source | Values include DYNASYS and ORDERBOOK Exclude loading source= ORDERBOOK from the query | ok |
| Running period | We are interested in the closed running month which is one month before the current month. 7th of each month the running period is closed. (E.g. Today being Sep 9, the closed running period is Aug) | QV_BW_QRY_CPCOPC07_0001 | [C_RUNPER] Run Period (calmonth | Extract only the last running period | ok | |
| Value Component | QV_BW_QRY_CPCOPC07_0001 | [C_VALCOMP] Value Component | Used only to filter Net price | ok | ||
| SAP active price | Note: Net price in the query known as SAP active price for which the value component field in the query (column above) is equal to 2 . The unit is euro (other currency?) per kg | This field is only relevant for calculating forecast price: 4) Forecast price calculation FLOW | QV_BW_QRY_CPCOPC07_0001 | \Measures\Net price [C_VALCOMP] Value Component | \Measures\Net price filtered on [C_VALCOMP] Value Component=2 For TS, if there is no record of value component=2, the filter is on value component key=15 | |
| Forecast month | QV_BW_QRY_CPCOPC07_0001 | [CPCOPC02P] Further Characteristics\[0CALMONTH] Forecast Month | ||||
| Fcst Volume (KG) | This is the forecast sale quantity. It is directly retrieved from Forecast query. | [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\Measures\Quantity | ||||
| Fcst Sales (€) | This is the forecast sale. It is calculated in the transparency dashboard. Fcst Sales (€) =Fcst Volume (KG)* Fcst price (€/KG) Important Note: there is a measure in BW (Snapshot\Measures\Amount) but it has NOT been used in the transparency dashboard | |||||
| Fcst price (€/KG) | This is the unit price forecast which calculated in transparency dashboard | ''\Measures\Net price'' is a forecast unit price reported in the forecast query monthly basis for 18 months ahead this measures is used to calculated the Fcst price.
| Useful to keep it in the table as we'll certainly use it for reporting purpose. | Link to the logic https://app.diagrams.net/#G1GuqjSLqoK1zjw2jr4DRt07uxWzGFZEK6 | ||
| CPC | For Novecare CPC is the combination of ship-to, material, and incoterm. | Note: For other GBUs like SpP the definition of CPC is different | CPC (Ship-to+Material+incoterms) Ship-to [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[CPCOPC02P] Further Characteristics\[C_SHIPTID] Ship-to party Material [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[CPCOPC02P] Further Characteristics\[C_MATNR2] Material Incoterms [C_INCOTRM]. | |||
Ship-To | [C_SHIPTID] Ship-to party | |||||
Sold-To | [C_SOLDID] Sold-to party | |||||
Ship-To KA | Important note: It is coming from order line transaction from copa03. The ship to ka assigned to the Last order line of a CPC from COPA03. It's a little bit complicated. As we lack the information, we retrieve it from historical sales. To proceed, we go through the same as "last price logic" but instead of retrieving a price, we just extract the dimension value. Multiple fields will be certainly retrieved that way (except if already part of a master data). | |||||
Manufacturing Plant | [C_MANPLNT] Manufacturing Plant | |||||
Shipping Plant | [C_PLANT] | |||||
Incoterms | [C_INCOTRM]. | NG(04/09): Should we use IC_INCOTERM or use C_INCDYNS/NR and which field to use part of CPC | ||||
Product | [C_MATNR2] Material (key & name) | |||||
| Unit of measure | [QV_BW_QRY_CPCOPC07_0001] FL-Sales Revenues Forecast Snapshot\[0UNIT] Unit of measure |
Converting currency → monthly value is collected.
Car 3 is used for historical and forecast sales.
Using the current month and future months the forecast rate the closed month is applied. (Closed month in this case is one month before the current month).
Every day the master data in transparency dashboard is refreshed from BW . It means that every day the rule of converting using CAR 3 is applied. Currently, the last rate updated for each month will be important to keep and expose to views and no need to keep the history.
Always data will be extracted from query and then we applied the business transformation, is converted based on the rule above according to CAR3