General presentation

The application loads data of the transportation shipments, deliveries and shipment costs from ECC (Solvay and Rhodia) into BW.

It is done daily with using delta process.

List of contacts:

Jean Baptiste Cercueil (SBS logistics Excellence) : Business Application Owner
Sandra Silveira (SBS Information Services) : Reporting Coordinator
Guillaume Thevenet (SBS Information Services) : BW Coordinator
Nestor Araujo (SBS Information Services) : BW Technical Developer

Objective of the application

One unique reporting solution for the group
Rationalization of the “live” solutions and reduction of evolutions requested
Maintainable and sustainable solution.
Enabler for productivity actions on Shipment costs
Drillable solution - allowing possibility to detect & analyse problems by showing the link from aggregated figures down to document in SAP.
Few “core” queries/workbooks design for application (=> Use BW analysis functionalities and not just as extractor).
No common & consolidated reporting.

Reasons

Revamped reporting BW solution based on RCS (WBP-TR002) but built on old historical architecture (no project on this application for years) - mainly designed for cost reporting.

Only aggregated monthly data available on PF1.

Specific solution (cross legacies) built for CO2 footprint with activation of some standard extractors on PQ1.

More than 250 existing queries in SAP => almost impossible to maintain on an efficient way, most of then not or almost not documented.

Some previous experiences of common transversal reporting tools : WISE (working cap.) and SPRINT (Purchasing and Procurement).

“Drillable” solution - allowing possibility to detect & analyse problems by showing the link from aggregated figures down to document in SAP.

Few “core” queries/workbooks design for application (=> Use BW analysis functionalities and not just as extractor).

Usage information

Around 300 users, worldwide with daily update

History

The Transportation Costs was developed to attend Rhodia users in 2007 using several DSO’s and just one cube with many detailed information. The number of tickets about inconsistente of data and performance issue were the triggers to develop a new application, in this case covering also the Solvay data.

The TIERS project was started to remodel the Rhodia transportation data and to add all content of Solvay using the standard datasources. Additional master data were created to avoid duplication of data in different providers.

The project was request from logistic areas and Jean Baptiste concentrated the requests and generated the functional specification.

The project started in April/2015 and it was released in production in December/2015.

Roles & Access

Roles and access

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

Role Code

Role Description

Explanation

ZR_RCS_CA_M072

TR - Transportation costs New

role menu

ZBI_RCS_TR_A08

New Transport Costs Analysis

- End User Role

role menu for TIERS, gives access to all cubes under infoarea IA_SD_TR*

Authorization Object ZBI_TR2 / Authorization on Company Code

This role has to be used with the role menu transportation costs

NB: The role menu ZR_RCS_ALL_MENU "Role composite with all role Menu and perimeters" gives access to all role menu. When asking for a new role menu, do not forget it is added to that one.

NB: ZBI_RCS_TR_A02 & ZR_RCS_TR_A02 are obsolete

Authorization objects

List of autorisation objects mandatory for the application.

Authorization object

Explanation

Company Code (C_COMPCDE)

ZR_TOUT_CA_P01 => All Companies "Solvay"

ZR_*_CA_P01

ZR_ZFR3_CA_P01 => Analysis company Rhodia Opérations ( ZFR3)

See also file maintained by Authorization team : BW Catalog of Roles / link: Authorization catalog

Dataflow overview

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Main documentation:

Preparation

Design

Deployment

E Learning

Rule to take the GBU code

Read the field C_SUBACT2 (profit center) from master data C_MATPNT3 using plant and material as key.

  SELECT  logsys
              /bic/c_plant
              /bic/c_matpnt3
              /bic/c_pfctr1
              /bic/c_subact2
              co_area
          INTO  TABLE itb_matpnt2
          FROM  /bic/pc_matpnt3
         FOR ALL ENTRIES IN itb_dpsdtr02
          WHERE /bic/c_plant   = itb_dpsdtr02-c_plant
            AND /bic/c_matpnt3 = itb_dpsdtr02-c_matpnt2
            AND logsys         = itb_dpsdtr02-logsys
            AND objvers        = 'A'.

  READ TABLE itb_matpnt2 INTO itb_matpnt2_w
          WITH TABLE KEY logsys    = <result_fields>-logsys
                         c_plant   = <result_fields>-/bic/c_plant
                         c_matpnt2 = itb_dpsdtr02_w-c_matpnt2.
        IF sy-subrc = 0.
          <result_fields>-/bic/c_pfctr1 = itb_matpnt2_w-c_pfctr1.
          <result_fields>-/bic/c_subact2 = itb_matpnt2_w-c_subact2.
          <result_fields>-co_area   = itb_matpnt2_w-co_area.

Rule to determine the partner code

  IF itb_dpsdtr02_w-c_vendid  IS NOT INITIAL.
          <result_fields>-/bic/c_pnty3py     = 'VEND'.
          <result_fields>-/bic/c_3rparty  = itb_dpsdtr02_w-c_vendid.
          SHIFT <result_fields>-/bic/c_3rparty LEFT DELETING LEADING '0'   .
        ENDIF.
        IF itb_dpsdtr02_w-c_shiptid   IS NOT INITIAL.
          <result_fields>-/bic/c_pnty3py     = 'CUST'.
          <result_fields>-/bic/c_3rparty  = itb_dpsdtr02_w-c_shiptid.
          SHIFT <result_fields>-/bic/c_3rparty LEFT DELETING LEADING '0'   .
        ENDIF.

Rule to determine the departure and destination code

IF itb_dpsdtr02_w-c_dpnode2 IS NOT INITIAL.

          <result_fields>-/bic/c_pntydp   = 'NODE'.
          <result_fields>-/bic/c_pntdp2    = itb_dpsdtr02_w-c_dpnode2.
        ENDIF.
        IF itb_dpsdtr02_w-c_dpshpn2  IS NOT INITIAL.
          <result_fields>-/bic/c_pntydp   = 'SHIPP'.
          w_shppnt   = itb_dpsdtr02_w-c_dpshpn2.
          <result_fields>-/bic/c_pntdp2    = w_shppnt.
        ENDIF.
        IF itb_dpsdtr02_w-c_dpplnt2  IS NOT INITIAL.
          <result_fields>-/bic/c_pntydp   = 'PLANT'.
          <result_fields>-/bic/c_pntdp2    = itb_dpsdtr02_w-c_dpplnt2.
        ENDIF.
        IF itb_dpsdtr02_w-c_dpvend2   IS NOT INITIAL.
          <result_fields>-/bic/c_pntydp   = 'VEND'.
          <result_fields>-/bic/c_pntdp2    = itb_dpsdtr02_w-c_dpvend2.
          SHIFT <result_fields>-/bic/c_pntdp2 LEFT DELETING LEADING '0'.
        ENDIF.
        IF itb_dpsdtr02_w-c_dpcust2   IS NOT INITIAL.
          <result_fields>-/bic/c_pntydp   = 'CUST'.
          <result_fields>-/bic/c_pntdp2    = itb_dpsdtr02_w-c_dpcust2.
          SHIFT <result_fields>-/bic/c_pntdp2 LEFT DELETING LEADING '0'.
        ENDIF.

Rule to determine the reference month

***********************************************************
*  Determine Calendar day and Forward Agent
***********************************************************

  IF itb_dpsdtr01_w-dtcomplact IS NOT INITIAL. " (shipment completion date)
          <result_fields>-rstt_rmnth = itb_dpsdtr01_w-dtcomplact(6).
        ELSE.
          IF itb_dpsdtr01_w-dtcompl_pl IS NOT INITIAL.
            <result_fields>-rstt_rmnth = itb_dpsdtr01_w-dtcompl_pl(6).   " (shipment planned completion date)
          ELSE.
            <result_fields>-rstt_rmnth = itb_dpsdtr01_w-erdat(6).   " (shipment creation date)
          ENDIF.
        ENDIF.

Unit conversions

The Unit conversion is done in the Bex queries (Quantity Conversion Type), after user chooses the unit conversion on reports

Transactionnal data are stored in sales unit

if base unit / po unit is managed in dimension MASS, conversion within table T006 works well
if base unit / po unit is not managed in dimension MASS, conversions for KG/LB/TO have to be managed on ERP side (MM03 / Alternative Conversion).

There are many "special" units on Solvay ERP, ie: 1KG, 1TN, VKG, KRE,

Important :

Quantities are not converted when loading data, and are not store into cubes (The conversion is done when running the report)

So, if a conversion is missing for a material ask the material referent to

1- manage the conversion on ERP Side by managed the material master (MM03) / unit of measure tab

2- the day after, when the DSO UOMCMAT2 is filled, the conversion is applied in the reports. It applies to whole data without any reloading.

The conversion type created by TIERS queries is QCT_MATNR4 (see below the setup for it).

Currency conversion

The reports are all based on amounts in Local Currency Those amounts come directly from RCS/PF1. If user selects a currency in the prompt the query will convert, otherwise it will return the values in local currency.

This conversion is based on the ZRH2 rate (coming from RCS).

See private documentation on BW Currency Conversion

The current available rates applied to ALL data (even past data) independently of the date.

For base amount we use the shipment cost Amount, shipment condition amount and purchase values.

We applied conversion type ZRH2 for transportation costs - CTK_ZRH2TR (see setup below) :

Dependencies with other applications

It shares extractors (2LIS_08TRTK, 2LIS_08TRTLP, 2LIS_08TRFKZ) with the legacy TR application for RCS and PF1.
It was created some master data in order to have the attributes shared with several applications: shipment doc, delivery doc and shipment cost doc.
Data is consumed by Qlikview dashboard. The loadings in QV are daily.
There is no links in the loadings between SAP, BW and QV. It run on a fixed scheduled, the loadings must be finished when the next start.
There is an integration with SPRINT (purchase schedule lines) to store there the service purchases (freight)

Rhodia PO schedule lines (DB_PUSL1)

Solvay PO schedule lines (DB_PUSL2)

There is an integration with Value Stream to store there the cost of transports

Rhodia CUB_VS (value stream)

Rhodia CUB_DIPL (industrial dashboard)

There is an integration with Industrial dashboard to store there the cost of transports

Data loadings

Info providers and objects loaded

1 - Main Process Chain

This process chain is responsible to trigger and syncronize all TIERS processes chains for Solvay and Rhodia. It is scheduled to run every workday inside the Daily process chain (RSP_DAILY) after general master data PC, around 3AM (French time).

PC_SD_TIERS_001: SD TIERS Daily Main PC

2 - Propagation Layer

This process chain is responsible to run the loads between source system and first layer in BW.

PC_SD_TIERS_020 à SD TIERS Daily Solvay Propagation PC
PC_SD_TIERS_010 à SD TIERS Daily Rhodia Propagation PC

3 - Business Layer

This process chain is responsible to load the second layer in BW taking data from propagation layer.

PC_SD_TIERS_021 à SD TIERS Daily Solvay Business Rule PC
PC_SD_TIERS_011 à SD TIERS Daily Rhodia Business Rule PC

4 - Master data

This process chain is responsible to load all the master data attributes in BW.

PC_SD_TIERS_MD_022 à SD TIERS Daily Solvay Master Data PC
PC_SD_TIERS_MD_002 à SD TIERS Daily Rhodia Master Data PC

5 - Text & maintenance

This process chain is responsible to load all the master data texts in BW.

PC_SD_TIERS_MD_TEXT022 à SD TIERS Daily Solvay Text PC
PC_SD_TIERS_MD_TEXT002 à SD TIERS Daily Rhodia Text PC
PC_SD_TIERS_DELETE à SD TIERS Daily Deletion PSA/Change Log

Loading frequency

Daily during the daily process chain

Average performance & monitoring

Daily process chain is loaded in 2 hours with around 30k lines in DELTA mode for a total of 10M lines in cubes.

See below an extract of 1 month of monitoring

Application Monitoring

Record Keeping

User defined that 3 full years stored is enough to be maintained in BW.

There is no automatic process to remove historical data, so any request about it should be asked to user openning a ticket in Freshdesk.

Modeling

Info areas

Master Data

Propagation Layer

Business Layer

Reporting Layer

Virtual Layer

Multi provider

MVSDTR01 - Shipm & Delivery & Cost & Cond & PO/Inv (Sol&Rho)

Data Flow

Dimensions

Key figures

Reporting

Queries End User Documentation

Main queries

1 - Shipment cost details analysis

Technical Name: BW_QRY_MVSDTR01_0010

Propose a detailed analysis of shipment cost accruals, detailled by materials. Make shipment costs accruals understandable.
Allow benchmarking between materials, between customers.
Give some possibilities of drill down to get details on a macro figure.

Public

Sites log managers / GBU log process teams
Base for Qlikview reportings

2 - Shipment cost vs invoice analysis

Technical Name: BW_QRY_MVSDTR01_0011

Propose advanced cost analysis functionalities including invoice / accrual comparision and other ratios that can't be available at material level.
Detect / evaluate unbilled payables.
Audit tarrif calculation

Public

Finance / controlling
Sites & GBU logistics managers
Data log and support teams

3 - Shipment status and events

Technical Name: BW_QRY_MVSDTR01_0012

Propose tracking of events on shipments. Without cost considerations.
Propose a reporting on volumetry : Nb of files (delivery or shipment) per flow, average value on some indicators...
Load plan of shipping points

Public

LOG data and support teams
GBUs sites log managers and sites operations supervisors
Transport managers

4 - CO² Emissions TIERS

Technical Name: BW_QRY_MVSDTR01_0013

5 - Shipment Events TIERS

Technical Name : BW_QRY_MVSDTR02_0001

Matrix between queries and info objects

Query description	Cost deatailed analysis	Accrual vs invoice	Statuses and events	Event reporting	CO2 Footprint
Technical name	MVSDTR01_0010	MVSDTR01_0011	MVSDTR01_0012	MVSDTR002_0001	MVSDTR01_0013

General "Core entry keys"	COMPANY	COMPANY	COMPANY	COMPANY	COMPANY
	PROMPT UNIT	PROMPT UNIT	PROMPT UNIT		PROMPT UNIT
	PROMPT CURRENCY	PROMPT CURRENCY
	System origin	System origin	System origin	System origin	System origin
	BFC GBU	BFC GBU	BFC GBU	BFC GBU	BFC GBU
	Site (PUSITE) - C-PSITE	Site (PUSITE)	Site (PUSITE)	Site (PUSITE)	Site (PUSITE)
	Actual completion Year / Month	Actual completion Year / Month	Actual completion Year / Month	Actual completion Year / Month	Actual completion Year / Month
	Shipment departure country	Shipment departure country	Shipment departure country	Shipment departure country	Shipment departure country
	Shipment destination country	Shipment destination country	Shipment destination country	Shipment destination country	Shipment destination country
	Forwd. Agent	Forwd. Agent	Forwd. Agent	Forwd. Agent

	Product Hierarchy
Query Specific axes	Material plant
	Bulk / Packed	PO number	Route	Event type	Zone
	3RD Party	Shpiment cost item category	DG class	Event reason for deviation
	Shpiment cost item category

	Transportation Mode	Transportation Mode	Transportation Mode	Transportation Mode	Transportation Mode
For those who want to detail	Plant	Plant	Plant	Plant	Plant
	Departure Geo Zone	Departure Geo Zone	Departure Geo Zone	Departure Geo Zone	Departure Geo Zone
	Transportation planning point	Transportation planning point	Transportation planning point	Transportation planning point	Transportation planning point
	Planed completion date	Planed completion date	Planed completion date	Planed completion date	Planed completion date
	Actual completion date	Actual completion date	Actual completion date	Actual completion date	Actual completion date
	Shipment number	Shipment number	Shipment number	Shipment number	Shipment number
	Shipping condition	Shipping condition	Shipping condition	Shipping condition	Shipping condition
	Shipment type	Shipment type	Shipment type	Shipment type	Shipment type

"By default" shown Measures (among those available)
	Accrual Net value shipment (CP)	Accrual Net value shipment (CP)	Gross weight	Nb of event	Gross Weight
	Gross Weight (PU)	Invoiced value (CP)	Nb of shipments	Event duration	Distance
	Net Weight (PU)	Net weight (PU)	Nb of containers		CO2 Tons
	Cost per Unit (Net)	Gross Weight (PU)	Nb of deliveries
	Cost per unit (Gross)	Cost per unit (Net)	Nb of HU
		Cost per unit (Gross)	# ship relevant for loading duration
		Invoice - accrual Gap	# ship relevant for On site duration
		Provisionning accuracy	# ship relevant for Queue time
			# ship relevant for Trnasit time
			Average loading duration
	Keep all others as optional	Keep all others as optional	Average On site duration
			Average Queue time
			Average Transit duration
			% OTD
			% OTS
			Average distance

"By default" shown Rows (among those available)
	Source sytem	Source System	Source sytem	Source System	Source system
	Site (CPLANT PSITE)	Site (CPLANT PSITE)	Site (CPLANT PSITE)	Site (CPLANT PSITE)	Site
	Shipment destination Country	Forward agent	Forward agent	Forward agent	Ship Shipping type
				Event type
				Event reason

All navigational attributes :
Organization	Source system	Source system	Source system	Source system	Source system
	BFC GBU	BFC GBU	BFC GBU	BFC GBU	BFC GBU
	Plant	Plant	Plant	Plant	Plant
	Site (Plant C-PSite)	Site (Plant C-PSite)	Site (Plant C-PSite)	Site (Plant C-PSite)	Site (Plant C-PSite)
	Company code	Company code	Company code	Company code	Company code
Shipment	Shipment number	Shipment number	Shipment number	Shipment number
	Departure country	Departure country	Departure country	Departure country
	Destination country	Destination country	Destination country	Destination country
	Forwarding agent	Forwarding agent	Forwarding agent	Forwarding agent
	Transportation planning point	Transportation planning point	Transportation planning point	Transportation planning point
	Actual Completion date	Actual Completion date	Actual Completion date	Actual Completion date
	Actual completion date (Month) YYYY/MM	Actual completion date (Month) YYYY/MM	Actual completion date (Month) YYYY/MM	Actual completion date (Month) YYYY/MM
	Actual completion date (Quarter) YYYY/Q	Actual completion date (Quarter) YYYY/Q	Actual completion date (Quarter) YYYY/Q	Actual completion date (Quarter) YYYY/Q
	Actual completion date Year (YYYY)	Actual completion date Year (YYYY)	Actual completion date Year (YYYY)	Actual completion date Year (YYYY)
	Actual date for Start of shipment	Actual date for Start of shipment	Actual date for Start of shipment	Actual date for Start of shipment
	Planned shipment completion date	Planned shipment completion date	Planned shipment completion date	Planned shipment completion date
	Planned shipment completion date (YYYY-MM)
	Shipment destination point	Shipment destination point	Shipment destination point	Shipment destination point
	Shipment destination point type	Shipment destination point type	Shipment destination point type	Shipment destination point type
	Shipment departure point	Shipment departure point	Shipment departure point	Shipment departure point
	Shipment departure point type	Shipment departure point type	Shipment departure point type	Shipment departure point type
	Route	Route	Route
	Shipment packaging material	Shipment packaging material	Shipment packaging material
	Shipment shipping type	Shipment shipping type	Shipment shipping type
	Transportation mode	Transportation mode	Transportation mode	Transportation mode	Transportation mode
			Departure point - TR zone
			Departure point - Zip code
			Destination point TR zone
			Destination point Zip code
		Shipment Shipping line	Shipment Shipping line
Shipment stage	Ship Stage sequence number	Ship Stage sequence number	Ship Stage sequence number
	Ship stage departure point	Ship stage departure point	Ship stage departure point
	Ship stage departure point type	Ship stage departure point type	Ship stage departure point type
	Ship stage destination point	Ship stage destination point	Ship stage destination point
	Shipment stage destination point type	Shipment stage destination point type	Shipment stage destination point type
	Leg indicator	Leg indicator	Leg indicator
Delivery	Delivery
	Delivery Item
	Delivery distribution channel
	Material
	Material plant
	DG class		DG class
	UN number		UN number
	3rd Party		3rd Party
	3rd Party type		3rd Party type
			3rd partry - TRzone
			3rd party - Location
			3rd party - Zip code
	Transportation group (Bulk / Packed)		Transportation group (Bulk / Packed)
	Transportation group detail		Transportation group detail
	Material hierarchy		Material hierarchy
Shipment x delivery	Shipping condition	Shipping condition	Shipping condition
Shipment cost item	Shipment cost number	Shipment cost number
	Item number	Item number
	Service agent	Service agent
	Shipment cost item category	Shipment cost item category
	Shipment cost posting date	Shipment cost posting date
	Shipment cost posting date - Month (YYYY/MM)	Shipment cost posting date - Month (YYYY/MM)
		PO Number
		Invoice Number
Cost condition	Condition type	Condition type
Event				Event actual start date (day)
				Event actual end date (day)

Main KPI calculations

Main functionalities

E Learning - user guide

The application is based on 6 main datasources based on SAP tables:

2lis_08TRTK – Shipment Header
2lis_08TRTLP – Shipment & Deliveries
2lis_08TRFKZ – Shipment Costs
DTS_TRKONV – Shipment Conditions
DTS_TRFI_ESSR – Invoices & Purchases History
DTS_TRFI_VFKP – Invoices & Purchases Issues

Main dimensions for the data:

C_POINT2 – Connection Point (it can be a customer, vendor, plant, node point or shipping point)
C_3RPARTY – 3rd Party (Cust / Vend) - it cab be a vendor or a customer
C_ROUTE – Route
C_SHIPNU2 – Shipment Number
C_SHCSTI2 – Shipment Cost item
C_DELITM2 – Delivery Item
C_CNDTYP2 – Condition Type
C_SHPTP_2 – Shipping Type
C_SHPMTP2 – Shipment Type
C_SHCITTY – Item category shipment costs
C_VTADD01 – Ship. Shipping Line
C_SHIPCD2 – Shipping conditions
0TRZONE – Transportation Zone
C_TRPPNT2 – Transportation planning point
C_VSTGA – Event Reason f.deviation
C_EVTTYP – Event Type

Broadcast

None

Maintenance

Known bugs

Shipment conditions deleted in ERP (WP1 & PF1) normally are not deleted in BW also, because as the extractor is generic, the delta is not over controlled. If document is deleted in ERP (shipment cost), in this case the shipment condition is deleted in BW.
Sometimes the unit conversion is not done in query due the unit is not mapped in table UOMCMAT2.

Recurring procedure

Planned Evolution

Most part of enhancements in standard extractors should be removed in future in order to obtain a better performance.
The old transportation flow (CUB_TR001, CUB_TR002, CUB_TR005, CUB_TR006, CUB_TR007, CUB_TR008) will be deleted and removed from schedule in 2016.
Some broadcasts existed in old transportation flow should be developed in new TIERS.

Useful transactions to be used for checking data

Transaction	Description	Corresponding table	Comments
VT03N	Shipments	VTTK, VTTS	in ERP
VI03	Shipment Costs	VFKK, VFKP	in ERP
VL03N	Deliveries	LIKP, LIPS	in ERP
VA03	Sales Orders	VBAK, VBAP	in ERP
ME23N	Purchase Orders	EKKO, EKPO, EKBE	in ERP
RSUOM	Unit conversion	T005	in BW
RSCUR	Currency conversion	TCURR	in BW

Page tree

BW TIERS ( DRAFT ) (Transport Integrated and Enhanced Reporting Solution)

General presentation

Objective of the application

Reasons

Usage information

History

Roles & Access

Roles and access

Authorization objects

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Rule to take the GBU code

Rule to determine the partner code

Rule to determine the departure and destination code

Rule to determine the reference month

Unit conversions

Currency conversion

Dependencies with other applications

Data loadings

Info providers and objects loaded

1 - Main Process Chain

2 - Propagation Layer

3 - Business Layer

4 - Master data

5 - Text & maintenance

Loading frequency

Average performance & monitoring

Modeling

Info areas

Master Data

Propagation Layer

Business Layer

Reporting Layer

Virtual Layer

Multi provider

Reporting

Queries End User Documentation

Main queries

1 - Shipment cost details analysis

2 - Shipment cost vs invoice analysis

3 - Shipment status and events

4 - CO² Emissions TIERS

5 - Shipment Events TIERS

Matrix between queries and info objects

Main KPI calculations

Main functionalities

Broadcast

Maintenance

Known bugs

Recurring procedure

Planned Evolution

Useful transactions to be used for checking data