Skip to main content

Celonis Product Documentation

Activity Master Data Table

On this page, the App Store Team provides you with an overview of how to use the Activity Master Data Table. It can be used to:

  • Differentiate activities that happen at the item or header Level according to our standard script.

  • Provide estimations on how long it takes to complete an activity. Can be used for savings potential calculations.

  • It gives you the freedom to add additional columns to your projects such as documentation and more.

For more information, please contact the App Store team.

Some of the values used are best guesses or assumptions. Feel free to adapt the hard-coded values according to your rollout projects and customer feedback.

Motivation

Customers are frequently asking how to know if an activity was performed on the item or header level. This is very interesting in multiple topics, for instance:

  • Automation: Often the customer objects to the provided automation ratios provided by Celonis. Their main objection is that our analyses are not taking into account that if one activity on the header level is performed manually this might trigger the execution of many automated activities on the item level.

  • Business Case Calculations: You can take into account activities at the header or item level to avoid counting activities multiple times. Furthermore, time spent on performing activities can be taken into account to count savings, FTEs among other uses cases.

  • Activity calculation in the script: Instead of going through the script to understand at which level activity is calculated, you can simply check the Level in the Master Data Table.

Contents of the table

The Activity Master Data Table has a very simple structure. It consists of the following columns:

  • "LEVEL" - This contains the document level of the activity. Currently, only 'Item', 'Header', and 'Custom' are used. The sole purpose of the latter one is to catch activities for which a level has not been specified.

  • "AVG_TIME" - This column sets a time (minutes). It is used to provide an estimation of how long it takes to complete the activity.

  • "ACTIVITY_DE"

  • "ACTIVITY_EN"

Template

Vertica transformation script template- Create Table: Activity Master Data

DROP TABLE IF EXISTS _CEL_XXX_ACTIVITY_MASTER_DATA;

CREATE TABLE _CEL_XXX_ACTIVITY_MASTER_DATA AS (

SELECT DISTINCT
    "ACTIVITY_DE" AS "ACTIVITY_DE"
    ,"ACTIVITY_EN" AS "ACTIVITY_EN"
    ,CASE
         WHEN "ACTIVITY_DE" IN ()
         THEN 'Header'
         WHEN "ACTIVITY_DE" IN ()
         THEN 'Item'
         ELSE 'Custom'
         END AS "LEVEL"
    ,CASE
         WHEN "ACTIVITY_DE" IN ()
         THEN 5
         WHEN "ACTIVITY_DE" IN ()
         THEN 60
         END AS "AVG_TIME"

FROM _CEL_XXX_ACTIVITIES);
How to connect the table to the existing data model
  1. Define your activities, level (header or item for SAP systems) and any attributes you would like to include in the activities table. This step should be performed out of Celonis.

  2. Create the table and populate it using a Transformation or use the Import file functionality to import a csv file with all the information.

  3. Connect the new table in the Data Model.

The Activity Master Data table is simply connected to the designated Activity Table by using 'ACTIVITY_EN' (the activity name) as a foreign key.

22119578.png
Example of how it is implemented in Purchase-to-Pay for SAP ECC and Order-to-Cash SAP ECC

Vertica transformation script P2P SAP ECC - Create Table: Activity Master Data

DROP TABLE IF EXISTS _CEL_P2P_ACTIVITY_MASTER_DATA;
 
CREATE TABLE _CEL_P2P_ACTIVITY_MASTER_DATA AS (
SELECT DISTINCT
    "ACTIVITY_DE" AS "ACTIVITY_DE"
    ,"ACTIVITY_EN" AS "ACTIVITY_EN"
    ,CASE
         WHEN "ACTIVITY_EN" IN ('Cancel Invoice Receipt' ,'Change Approval for Purchase Order', 'Cancel Currency','Change Final Invoice Indicator' , 'Change Vendor' ,
                                  'Clear Invoice' , 'Record Invoice Receipt' , 'Send Purchase Order Update' ,'Set Payment Block' , 'Waiting for Approval (Initial)')
         THEN 'Header'
         WHEN "ACTIVITY_EN" IN ('Block Purchase Order Item', 'Cancel Goods Receipt' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' ,
                               'Change Delivery Indicator' , 'Change Outward Delivery Indicator' , 'Change Rejection Indicator' , 'Change Price' ,
                                  'Change Quantity' , 'Change Storage Location' , 'Create Purchase Order Item' , 'Create Purchase Requisition Item',
                               'Delete Purchase Order Item' , 'PO approved (1st stage)', 'PO approved (2nd stage)' , 'PO approved (direct)' ,
                               'Reactivate Purchase Order Item' , 'Receive Order Confirmation' , 'Record Goods Receipt' , 'Remove Payment Block' , 'Send Purchase Order')
         THEN 'Item'
         ELSE 'Custom'
         END AS "LEVEL"
    ,CASE
         WHEN "ACTIVITY_EN" IN ('Create Purchase Order Item' , 'Create Purchase Requisition Item' , 'Delete Purchase Order Item' , 'PO approved (1st stage)' ,
                               'Receive Order Confirmation' , 'Record Goods Receipt' , 'Record Invoice Receipt' , 'Remove Payment Block' , 'Send Purchase Order Update' ,
                                   'Set Payment Block' , 'Waiting for Approval (Initial)' , 'Change Outward Delivery Indicator')
         THEN 5
         WHEN "ACTIVITY_EN" IN ('Change Approval for Purchase Order' , 'Cancel Currency' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' ,
                              'Change Delivery Indicator' , 'Change Final Invoice Indicator' , 'Change Rejection Indicator' , 'Clear Invoice' )
         THEN 10
         WHEN "ACTIVITY_EN" = 'Send Purchase Order'
         THEN 15
         WHEN "ACTIVITY_EN" IN ('Cancel Goods Receipt' , 'Record Invoice Receipt' , 'Change Price' , 'Change Quantity' , 'Change Storage Location' ,
                                'PO approved (2nd stage)' , 'PO approved (direct)' , 'Reactivate Purchase Order Item')
         THEN 30
         WHEN "ACTIVITY_EN" IN ('Block Purchase Order Item' , 'Change Vendor')
         THEN 60
         ELSE 30
         END AS "AVG_TIME"
FROM _CEL_P2P_ACTIVITIES);

HANA transformation script P2P SAP ECC - Create Table: Activity Master Data

-- Query No: 33
CALL {{TARGET_SCHEMA}}.DROP_IF_EXISTS ('{{TARGET_SCHEMA}}', '_CEL_P2P_ACTIVITY_MASTER_DATA');
-- Query No: 34
CREATE COLUMN TABLE {{TARGET_SCHEMA}}._CEL_P2P_ACTIVITY_MASTER_DATA AS (
SELECT  DISTINCT 
        ACTIVITY_DE AS "ACTIVITY_DE"
    ,ACTIVITY_EN AS "ACTIVITY_EN"
    ,CASE
         WHEN ACTIVITY_EN IN ('Cancel Invoice Receipt' ,'Change Approval for Purchase Order', 'Cancel Currency','Change Final Invoice Indicator' , 'Change Vendor' ,
                                  'Clear Invoice' , 'Record Invoice Receipt' , 'Send Purchase Order Update' ,'Set Payment Block' , 'Waiting for Approval (Initial)')
         THEN 'Header'
         WHEN ACTIVITY_EN IN ('Block Purchase Order Item', 'Cancel Goods Receipt' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' ,
                               'Change Delivery Indicator' , 'Change Outward Delivery Indicator' , 'Change Rejection Indicator' , 'Change Price' ,
                                  'Change Quantity' , 'Change Storage Location' , 'Create Purchase Order Item' , 'Create Purchase Requisition Item',
                               'Delete Purchase Order Item' , 'PO approved (1st stage)', 'PO approved (2nd stage)' , 'PO approved (direct)' ,
                               'Reactivate Purchase Order Item' , 'Receive Order Confirmation' , 'Record Goods Receipt' , 'Remove Payment Block' , 'Send Purchase Order')
         THEN 'Item'
         ELSE 'Custom'
         END AS "LEVEL"
    ,CASE
         WHEN ACTIVITY_EN IN ('Create Purchase Order Item' , 'Create Purchase Requisition Item' , 'Delete Purchase Order Item' , 'PO approved (1st stage)' ,
                               'Receive Order Confirmation' , 'Record Goods Receipt' , 'Record Invoice Receipt' , 'Remove Payment Block' , 'Send Purchase Order Update' ,
                                   'Set Payment Block' , 'Waiting for Approval (Initial)' , 'Change Outward Delivery Indicator')
         THEN 5
         WHEN "ACTIVITY_EN" IN ('Change Approval for Purchase Order' , 'Cancel Currency' , 'Change Delivery Date (actual)' , 'Change Delivery Date (scheduled)' ,
                              'Change Delivery Indicator' , 'Change Final Invoice Indicator' , 'Change Rejection Indicator' , 'Clear Invoice' )
         THEN 10
         WHEN "ACTIVITY_EN" = 'Send Purchase Order'
         THEN 15
         WHEN "ACTIVITY_EN" IN ('Cancel Goods Receipt' , 'Record Invoice Receipt' , 'Change Price' , 'Change Quantity' , 'Change Storage Location' ,
                                'PO approved (2nd stage)' , 'PO approved (direct)' , 'Reactivate Purchase Order Item')
         THEN 30
         WHEN "ACTIVITY_EN" IN ('Block Purchase Order Item' , 'Change Vendor')
         THEN 60
         ELSE 30
         END AS "AVG_TIME"
FROM {{TARGET_SCHEMA}}._CEL_P2P_ACTIVITIES
);

Vertica transformation script O2C SAP ECC - Create Table: Activity Master Data

DROP TABLE IF EXISTS _CEL_O2C_ACTIVITY_MASTER_DATA;

CREATE TABLE _CEL_O2C_ACTIVITY_MASTER_DATA AS (
SELECT DISTINCT
    "ACTIVITY_DE" AS "ACTIVITY_DE"
    ,"ACTIVITY_EN" AS "ACTIVITY_EN"
    ,CASE
         WHEN "ACTIVITY_EN" IN ('Create Quotation' ,'Create Purchase Order', 'Create Picking', 'Quotation Statuses' , 'Sales Order Statuses' ,
                                 'Approve Credit Check' , 'Deny Credit Check' , 'Partially approve Credit Check' ,'Set Delivery Block' , 'Change Delivery Block',
                               'Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Record Goods Issue', 'Cancel Goods Issue', 'Create Billing Documents', 
                                'Create Other Billing Document', 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 'Change Inco Terms (Part 2)',
                                'Due Date Passed', 'Material Availability Date Passed',' Clear Invoice')
         THEN 'Header'
         WHEN "ACTIVITY_EN" IN ('Create Sales Order Item', 'Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' ,
                               'Change Price' , 'Change Division' , 'Book Delivery Shortage' , 'Change Delivery Amount' ,
                                  'Change Material' , 'Change Plant' , 'Change Route' , 'Create Delivery Documents',
                               'Create Other Delivery Documents')
         THEN 'Item'
         ELSE 'Custom'
         END AS "LEVEL"
    ,CASE
         WHEN "ACTIVITY_EN" IN ('Due Date Passed', 'Material Availability Date Passed')
         THEN 0
         WHEN "ACTIVITY_EN" IN ('Create Quotation' ,'Create Purchase Order', 'Quotation Statuses' , 'Sales Order Statuses' ,'Record Goods Issue', 'Create Billing Documents', 
              'Create Other Billing Document', 'Create Sales Order Item', 'Book Delivery Shortage' , 'Create Delivery Documents', 'Create Other Delivery Documents')
         THEN 5
         WHEN "ACTIVITY_EN" IN ('Partially approve Credit Check','Clear Invoice','Cancel Goods Issue')
         THEN 10
         WHEN "ACTIVITY_EN" IN ('Change Delivery Block','Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Change Delivery Amount' ,'Change Material' , 'Change Plant' , 
         'Change Route' , 'Create Picking','Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' ,'Change Price' ,'Change Division')
         THEN 30
         WHEN "ACTIVITY_EN" IN ('Approve Credit Check' , 'Deny Credit Check' , 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 
         'Change Inco Terms (Part 2)','Set Delivery Block')
         THEN 60
         ELSE 30
         END AS "AVG_TIME" --Minutes
FROM _CEL_O2C_ACTIVITIES
);

HANA transformation script O2C SAP ECC - Create Table: Activity Master Data

-- Statement: Create Table: _CEL_O2C_ACTIVITY_MASTER_DATA
-- Query No: 1
CALL {{TARGET_SCHEMA}}.DROP_IF_EXISTS ('{{TARGET_SCHEMA}}', '_CEL_O2C_ACTIVITY_MASTER_DATA');
-- Query No: 2
CREATE COLUMN TABLE {{TARGET_SCHEMA}}._CEL_O2C_ACTIVITY_MASTER_DATA AS (
SELECT  DISTINCT 
        ACTIVITY_DE AS "ACTIVITY_DE"
    ,ACTIVITY_EN AS "ACTIVITY_EN"
    ,CASE
         WHEN ACTIVITY_EN IN ('Create Quotation' ,'Create Purchase Order', 'Create Picking', 'Quotation Statuses' , 'Sales Order Statuses' ,
                                 'Approve Credit Check' , 'Deny Credit Check' , 'Partially approve Credit Check' ,'Set Delivery Block' , 'Change Delivery Block',
                               'Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Record Goods Issue', 'Cancel Goods Issue', 'Create Billing Documents', 
                                'Create Other Billing Document', 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 'Change Inco Terms (Part 2)',
                                'Due Date Passed', 'Material Availability Date Passed',' Clear Invoice')
         THEN 'Header'
         WHEN ACTIVITY_EN IN ('Create Sales Order Item', 'Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' ,
                               'Change Price' , 'Change Division' , 'Book Delivery Shortage' , 'Change Delivery Amount' ,
                               'Change Material' , 'Change Plant' , 'Change Route' , 'Create Delivery Documents',
                               'Create Other Delivery Documents')
         THEN 'Item'
         ELSE 'Custom'
         END AS "LEVEL"
    ,CASE
         WHEN "ACTIVITY_EN" IN ('Due Date Passed', 'Material Availability Date Passed')
         THEN 0
         WHEN "ACTIVITY_EN" IN ('Create Quotation' ,'Create Purchase Order', 'Quotation Statuses' , 'Sales Order Statuses' ,'Record Goods Issue', 'Create Billing Documents', 
              'Create Other Billing Document', 'Create Sales Order Item', 'Book Delivery Shortage' , 'Create Delivery Documents', 'Create Other Delivery Documents')
         THEN 5
         WHEN "ACTIVITY_EN" IN ('Partially approve Credit Check','Clear Invoice','Cancel Goods Issue')
         THEN 10
         WHEN "ACTIVITY_EN" IN ('Change Delivery Block','Remove Delivery Block', 'Change Delivery Block', 'Delete Delivery Note', 'Change Delivery Amount' ,'Change Material' , 'Change Plant' , 
         'Change Route' , 'Create Picking','Set Reason for Rejection' , 'Change Reason for Rejection' , 'Cancel Reason for Rejection' ,'Change Price' ,'Change Division')
         THEN 30
         WHEN "ACTIVITY_EN" IN ('Approve Credit Check' , 'Deny Credit Check' , 'Change Shipping Type', 'Change Freight Terms', 'Change Payment Terms', 'Change Inco Terms (Part 1)', 
         'Change Inco Terms (Part 2)','Set Delivery Block')
         THEN 60
         ELSE 30
         END AS "AVG_TIME" --Minutes
FROM {{TARGET_SCHEMA}}._CEL_O2C_ACTIVITIES

);
Outcome of the exemplary transformation snippet once it was executed:

ACTIVITY_DE

ACTIVITY_EN

LEVEL

AVG_TIME

Ändere Lieferplandatum

Change Schedule Line Date

Custom

30

Sende Bestellung

Send Purchase Order

Item

15

Lege BANF Position an

Create Purchase Requisition Item

Item

5

Erfasse Wareneingang

Record Goods Receipt

Item

5

Erfasse Leistungserfassungsblatt

Record Service Entry Sheet

Custom

30

Erfasse Rechnungseingang

Record Invoice Receipt

Header

5

Lege Bestellposition an

Create Purchase Order Item

Item

5

Lösche Bestellposition

Delete Purchase Order Item

Item

5

Sende Bestelländerung

Send Purchase Order Update

Header

5

Storniere Rechnungseingang

Cancel Invoice Receipt

Header

30

Ändere Preis

Change Price

Header

30

Ändere Freigabe für Bestellung

Change Approval for Purchase Order

Header

10

Storniere Wareneingang

Cancel Goods Receipt

Item

30

Ändere Menge

Change Quantity

Item

30

Using the Activity Master Data Table in KPI calculation (Exemplary use case: Ratio of automated activities)

One of the benefits of this helper table is being able to distinguish activities that are either performed on the header or item level. This applies to most of our use cases as we are still mainly focused on document data from SAP ECC.

Currently - in most cases - we do not distinguish between the document header and item level when calculating a KPI that is based on activity. One example of this is the calculation of the automation ratio. The current formula does not account for whether an activity is performed on the document header or on the document item level.

As a result, many customers object to the automation ratio calculation provided by Celonis.

PQL Formula App Store Analysis "Automation":

Ratio of automated activities (Automation Ratio)

AVG(
CASE
    WHEN "_CEL_P2P_ACTIVITIES"."USER_TYPE" IN (<%= auto_user_type %>)
    THEN
        1.0
    ELSE
        0.0
END
)

Using the "LEVEL" column from the Activity Master Data Table, the PQL statement can be adjusted to include a weighting of activities that are performed on the document header or document item level.

In development: Automation 2.0 :

New weighted ratio of automated activities (Automation Ratio)

SUM(
        CASE 
           WHEN "_CEL_P2P_ACTIVITIES"."USER_TYPE" IN (<%= auto_user_type %>) AND "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Item' 
           THEN 1.0
           WHEN "_CEL_P2P_ACTIVITIES"."USER_TYPE" IN (<%= auto_user_type %>) AND "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Header' 
           THEN 1.0 / (PU_COUNT("EKKO", "EKPO"."EBELP"))
        ELSE 0.0
        END
    )
    /
SUM(
        CASE 
           WHEN "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Item' 
           THEN 1.0
           WHEN "_CEL_P2P_ACTIVITY_MASTER_DATA"."LEVEL" = 'Header' 
           THEN 1.0 / (PU_COUNT("EKKO", "EKPO"."EBELP"))
        ELSE 0.0
        END
   )

This approach is not only limited to the automation use case. It can be applied to any use case that focuses specifically on an activity-based KPI.

Benefits in business case calculation

Another benefit of this table is an arguably more precise business case calculation within a Celonis analysis. Under the assumption that most of the processing times are estimated correctly and have been validated by the customer the calculated potential savings calculated this way are more precise and less prone to objections by a customer.

Each activity count of every distinct activity specified in the helper table is multiplied by the specified individual processing times. The result is a total processing time which is then multiplied by the average cost of labor per minute.

Q + A

Q: 'In the Analysis X when we use custom table _CEL_P2P_ACTIVITY_MASTER_DATA , it uses proxies for avg . time as 5- 60. I l believe this is minutes? is it a good benchmark to consider Y minutes for 'Activity A'?

A: The assumptions made in this additional table are more than debatable. Please feel free to change it during your rollout projects. We are also happy to adjust the hard-coded durations and levels of activities based on customer feedback in the standard scripts.