General presentation

Objective of the application

This application was made to give and share within the industrial Rhodia family important KPIs to follow.

In addition to the KPIS which were more or less know by all the industrial manager, a new axis was put in place, the Value Stream (VS).

A Value Stream is defined by a consistent set of products manufactured on a given industrial asset. 

Reporting coordinator in charge of this application is Célia Gonzalez (from I&P Stream)

Usage information

460 users are splited in the world.

They are Value Stream manager, plant manager or industrial director.

There is no critical period, this dashboard is uploaded once a month, the first week-end after the 6th working day, which is generaly the second weekend of the month.

History

This application has been developped in 2007-2008.

The industrial project leader was Jean-Louis Tartarin and the IS project Leader was Sylvie Kramdi.

The two developpers were Olivier Morard (Keyrus) for the data modeling and Nathanaël Quinson (Keyrus) for the Bex query and the dashboard in the workbook.

Roles & Access

Roles and access

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

Authorization objects

List of autorisation objects mandatory for the application.

Dataflow overview

Source systems:

• WP1 via WBP, there is no direct extraction from WP1 during the process chain, we just use all the DSO already loaded from the different applications
• APO for the schedulling adherence
• Oracle database of the Pilotages for the Customer OTIF
• Flat Files:
  • Overall equipment effectiveness (OEE)
  • JNI

The Global overview is the following one:

Reporting documentation drive folder:

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Fixed costs Rules: In order to have simple rules, we have chosen to have multiples steps (working DSO) but with only one transformation rule in each.

Level 0: OD_CNPFA, this DSO is loaded with a delta mode from ODS_CCA9 which is used in CBS and contains all the fixed costs by cost centers.

There are 2 filters in this delta extraction:

The currency type = 10 in order to extract only the records which have the company currency (ODS_CCA9 contains also the controlling area currency)

The fiscal year period between Januray 2009 to december 2500, just to initialize the first delta extraction from January 2009.

In the start routine of the transformation, we also ahev a rule to delete all the records which are not "direct production cost center". To do this, the C_CCDPROD attribute of the Cost center (C_COSTCTR) is tested and we keep only the 'X' value.

Level 1: OD_CNPFC is loaded by a full update process from OD_CNPFA with the following selections:

The version of the CCR (0VERSION = 000)

The actual posting (forecast, budget, etc... are excludined) (0VTYPE = 010)

The fiscal year period with the 12 months of Y-1 and the months of the current year. This selection is calculated automatically via a variable.

In the start routine of the update rule all the records which are not related to fixed costs are deleted. The selection is done on the costelement group (C_COSTELE__C_CELTHF1) with the following values:

• • ZRCS-AC
  • ZRCS-ACAMO
  • ZRCS-ABCNP
  • ZRCS-ABAMO

Level 2: OD_CNPF2 is loaded by a full update process from OD_CNPFC but also with a full update on itself with the following selections:

From OD_CNPFC: Fiscal Year = Y and Y-1

From OD_CNPFC, in the start routine there is also a rule to distritube the actualized fixed costs (ZRCS-AC and ZRCS-ACAMO) bewteen the value stream using a key determined with the absorption groups (ZRCS-ABCNP and ZRCS-ABAMO).

We also delete all the records which do not have the Value Stream filled.

From OD_CNPF2, in the start routine, we are calculating the fixed costs for the multi Value Stream only and only for the actual data (selection on fixed cost group = ZRCS-AC and ZRCS-ACAMO).

Level 3: CUB_VS is loaded in a full data loading using the Fiscal year period as selection. In the start routine we still have a rule to delete records which not have VS filled.

Variable costs Rules: The variable costs are collected from different sources:

• • Purchase Orders
  • Material movements
  • Manufactoring costs

OD_FVMK is used as a master data where all the costing variant and vrsion are stored by period and company.

OD_CPVS1 is emptied during each data loading process.

• • Loading from Purchase Order (ODS_SCL1)
• The data loading is done in a full data loading with a selection on the posting date (=Y and Y-1)
• Only sub-contracting services Purchase Orders are taken into account (0ITM_CAT=3) and only if the process order is also finished (C_PRODORD__COORD_TYPE <> 'Z100')
• Finally all the records which have an empty controlling area are deteled.
• Loading Manufactoring costs (CUB_PC001)

• The data loading is done in a full data loading with the following selections:
  • Fiscal year period equal to current and previuos year (automaticlly set via a variable)
  • Currency type equal to the controlling object currency (0CURTYPE = 070)
  • Value type of the reporting equal to actual figures (0VTYPE = 010)
• In the start routine, we have a rule to take into account only the variable costs for manufactoring orders which are finished:
• Variable costs are records which have the cost componant group informed (0COSTCOMP__0CCOMPGRP_1 <> null)
• Finished manufactoring order are identified once the type is different as Z100 (C_PRODORD__COORD_TYPE <> 'Z100')
• Loading Manufactoring costs (ODS_IC01)
• The data loading is done in a full data loading with the following selections:
  • posting date between the first day of Y-1 and the last day of the last ended period (automaticlly set via a variable)
  • Movement type corresponding to variable costs (0MOVETYPE = 101, 102, 261, 262, 531, 532, 543, 544, 545, 546)
• In the start routine, several selections are done:
  • Manufacturing process is finished (PRODORDER type <> Z100)
  • Movements which doesn't have order process number (C_PRODORD empty) are deleted
  • For Subcontracting (movement type 543/544) we delete line which concerns Material Type ZHIB, ZERS and ZGEN. Those material type should not be count in Dashboard for CP
  • We keep too data from movement type 545/546 (Subcontracting Co-Production or Subcontracting By-Prodcution

The MATY KPI: This KPI is used in the dashboard and we will display two indicators:

• • The best Performance (ODS_QUMV)
    • The test to keep the best profmance is done in the end routine.
  • The minimum consumption (ODS_QUM2)

OD_CPV2 is emptied during each data loading process and reloaded from OD_CPVS1 in a full update mode without selection because OD_CPVS1 contains only Y and Y-1.

• • In the start routine there is a selection to keep only records related to Packaging or Raw material (Component type = P or R).
  • All records which has quantity and amount on consumption equal to zero are also deleted. 
  • In the start routine we do a look-up in the DSO ODS_PCP3 to read the split between  Variable costs (CP, fixed costs (CNP) and depreciation (AMO)
  • In the end routine, we convert all the quantity in Tonnes (TO)

Production quantity Rules:

The production data are loaded in 3 steps:

• • From ,ODS_IC01 to ODS_PRVS
    • Before each data loading, the DSO ODS_PRVS is emptied
    • ODS_PRVS id loaded from ODS_IC01 with an automatic selection on the calendar Month (0CALMONTH interval calculated via a variable)
    • In the start routine, there are other selections
    • We keep only the record related to consumption (0STOCKCAT=V and 0STOCKTYPE=V)
    • We keep only the material type which are Raw Material or Packaging or Non Rhodia materials (C_MATNR__MATL_TYPE =ZMAT or ZVER or ZUNB), 
      • Main production is identified by the material movements 101 and 102
      • Sub-contracting by-production is identified by the material movements 545 and 546 and the co-product attribute of the material not equal to 'C' (C_MATPLNT__C_COPROD <> 'C')
      • Sub-contracting co-production is identified by the material movements 545 and 546 and the co-product attribute of the material equal to 'C' (C_MATPLNT__C_COPROD = 'C')
      • By-production is identified by the material movements 531 and 532 and the co-product attribute of the material not equal to 'C' (C_MATPLNT__C_COPROD <> 'C')
      • Co-production is identified by the material movements 531 and 532 and the co-product attribute of the material equal to 'C' (C_MATPLNT__C_COPROD = 'C')
      • We also have the quantity which are calculated in AIT (Tons of Activ ingredient)
      • We also have TURN
  • From ODS_PRVS to ODS_PRVS itself
    • This full data loading is done to identify the internal movement in the VS but for different plant and different companies
  • From ODS_PRVS to CUB_VS
    • There is no specific rules, it is a one to one mapping

OTIF Supplier Rules:

The Supplier OTIF is a KPI to measure if our supplier are delivering our raw material on time with the good quantity.

This data loading is done from DBSDTR03 with a selection on the calendar date (between the first day or Y-1 and the last day of the previous month of the year)

Then it is a direct link for the shipment costs

OTIF Customer Rules:

The customer OTIF is already made in an Oracle table, here we just have to extract the content of the table and to load in one to one mode

Stock inventory Rules:

For the inventory, we have a dedicated DSO to store the agregation by Value Stream, because the number of records into ODS_IC01 is very very big.

OD_VSSTK is loaded in delta mode after each ODS_IC01 data loading.

Then the CUB_VS is loaded once a month in a full mode with selection 

Forecast Accuracy Rules:

OEE Rules:

JNI Rules:

CCR Rules:

Dependencies with other applications

This industrial dashboard is dependant to:

• TIERS for the transportation costs
• SPRINT for the raw material costs
• Inventory management for the stock material mouvement
• Production Planing for the production process Orders
• CBS for the fixed costs

Data loadings

Info providers and objects loaded

 There are two dedicated process chain to load the industrial dashboard during the weekend.

Today this dashboard is launched manually because we need to make sure that the weekly CO-PC  process chain launched friday at 6:00 pm CET is successfully finished.

Loading frequency

This dashboard is uploaded once a month, the first week-end after the 6th working day, which is generaly the second weekend of the month.

Due to the control of the successfull CO-PC chain, the dashboard process chains are launched manually.

Average performance

The time for this process chain is never the same because each month, the selection is different.

Begining of January, we will extract 24 months (full Y and Y-1), so it is the longuest process chain, more than 24 hours.

At the opposite, in february it will be the fastest because only 13 months will be lauded (Jan for Y and full Y-1)

Reporting

Queries End User Documentation

The End users are not "playing" with the BEx Analysis/Analyser but with a dashboard developped in an Excel workbook.

All the documentation is in this GDoc document: 

Main queries

All the queries used in the dashboard are documented in the following document: 

Main functionalities

The user workbooks are dashboard with a lot of formula which are described in the previous files.

Broadcast

No more broacast.

The VS dashboard was broiadcasted to a dedicated eRoom where the users was able to acces and didn't refresh the dasboard by themselve.

Maintenance

• How to update the Value Stream master data
• How to update the Energy Efficiency Conversion rate

Planned Evolution

No evolutions are planned since the Industrial dashboard should be replaced partiially with the i-Mep project.