1) CS price optimization scope
In the forecast fact tables, there will be missing attributes that are available either in dimension tables or the historical sales fact table (P&L). List below:
| Attributes | Short term action (Q4) | Ticket | Mid term/Long term | Dimension table in data like | Link with Historical sales fact table | Link with Forecast fact table | Availability in COPC07 | Availability in COPA03 | |
|---|---|---|---|---|---|---|---|---|---|
| Group of activity | Keep the design as it is | Unique at CPC | V_DIM_activity1 | GBU_Key | GBU_Key | Yes | Yes | ||
| Country | Keep the design as it is | V_DIM_Customer | Customer ship to/sold to _Key | Customer ship to/sold to _Key | yes | Yes | |||
| GBU region | Add as an attribute in the forecast fact table | https://solvayagile.atlassian.net/jira/software/c/projects/PC/issues/PC-468 | Unique per GBU and customer | Available | Not available | no (transformation rules on Zone attribute available in the query) | no (transformation rules on Zone attribute available in the query) | ||
| GBU customer segment | To add in the CPC table: (DIM_customer_material_rel) Add as an attribute in the CPC table and source it from salesforce CORE (CORE table: BU_CUSTOMER_SEGMENTATION) or BW Cube (not query) | Unique per GBU and customer | Available | Not available | No | Yes | |||
| Product brand name | To add in the CPC table: (DIM_customer_material_rel) Using the same methodology as the last invoice price calculation to identify the most recent line item associated with a specific combination of ship-to and material in Copa03. Once this line item is identified, retrieve the product brand name ([4CPCOPA03-TGB2_C_GBR19]GBR: GBU Material Group Ship-to) associated with the line item. | Unique per combination of GBU and product/material | Available | Not available | No | Yes | |||
| End use | To add in the CPC table: (DIM_customer_material_rel) For all GBUs except Spp Using the same methodology as the last invoice price calculation to identify the most recent line item associated with a specific combination of ship-to and material in Copa03. Once this line item is identified, retrieve the [4CPCOPA03-TGB2_C_GBR4] GBR: Corporate Enduser (Ship-To)_W, end use associated with the line item. For SPP use sales force icare. There is a table in icare that holds end use for the combination of customer and product. SELECT End_Use__c,Material_Group__c,Sap_Code__c as Sold_to FROM End_Use__c | Unique per combination of customer ship to and material To look for the relation with the customer | V_DIM_gbr_enduse | Available | Not available | No | Yes (Rules) | ||
| GBU product family | To add in the CPC table: (DIM_customer_material_rel) | Unique per combination of GBU and product/material. One product/material can have various product family from one GBU to another | Available | Not available | No | Yes (transformation applies) 2) Transactional data - Historical sales | |||
| Enterprise segment | To add in the CPC table: (DIM_customer_material_rel) Using the same methodology as the last invoice price calculation to identify the most recent line item associated with a specific combination of ship-to and material in Copa03. Once this line item is identified, retrieve the Enterprise segment ([C_GBR21__C_ENTSEG]) associated with the line item. | Available | Not available | No | Yes | ||||
| Market | One market has multiple end use | V_DIM_gbr_enduse | End_use_key | Not available | |||||
| Ship to KA/Final consignee | Available | Not available |