TIERS - Transport Integrated and Enhanced Reporting Solution

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 using delta process via process chain schedule.

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).
• One common & consolidated reporting space.

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 analysis was developed in 2007 to attend the Rhodia users of Logistic processes. The solution had usage several DSO’s and just one cube with many detailed information. The number of tickets about inconsistentes of data and bad performance some of triggers to develop a new application, in this case much more robust and covering also the Solvay data and its users.

The TIERS project was started in the begin of 2015, using a remodeling by layers Layered Scalable Architecture (LSA), in our case: propagation, business, reporting and virtual layers. The current transportation datasources from Rhodia were reused with low change and all datasources from Solvay were activated with low enhancements. Some additional master data were created to avoid duplication of data in different providers, like shipment document, shipment cost item and delivery item.

The development of project was request by the logistic areas and Jean Baptiste concentrated all the requests and generated the functional specification.

The project was started in April/2015 and it was deployed in December/2015 with all users trainned.

General Rules

Roles and access

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

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

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.

See also file maintained by Authorization team : 

• BW Catalog of Roles: Authorization catalog
• BW Authorization:      BW Authorizations

Dataflow overview

Functional and Technical rules on Workbench + Reporting

Rules & Explanations

Main documentation:

Preparation

• Kickoff
• Project Quality Plan
• Design Validation

Design

• Functional Specification
• Map of Datasources
• Profiles Management
• Queries Definition
• Technical documentation

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

        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).

BW Quantity Conversion

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) :

 

BW Currency Conversion

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)

• There is an integration with SPRINT to store the profit center for purchase services

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)

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

Rhodia CUB_DIPL (industrial dashboard)

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 inside 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

For details about application load monitoring, click in following link: 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 historical deletion should be done by user with Freshdesk ticket.

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

Main functionalities

• E Learning - user guide

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

Reporting

Calculated & Restricted key figures

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

 

3 - Shipment status and events 

• 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

Queries used to load QV applications

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)

 

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

List recurring procedures

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

https://wiki.solvay.com/display/TECHREP/BW+Authorizations?src=contextnavpagetreemode