Skip to main content

Celonis Product Documentation

CALC_THROUGHPUT
Description

Throughput is used to calculate, for each case, the time between two activities. From which activity the calculated throughput time should start and at which it should end can be configured through range specifiers. The resulting column is temporarily added to the case table and returns the throughput time between the two specified activities per case.

Syntax
  CALC_THROUGHPUT ( begin_range_specifier TO end_range_specifier, timestamps [, activity_table.string_column ] )

  • begin_range_specifier: FIRST_OCCURRENCE['activity'] | LAST_OCCURRENCE['activity'] | CASE_START | ALL_OCCURRENCE['']

    • FIRST_OCCURRENCE['activity']: Throughput time starts at the first occurrence of the specified activity type.

    • LAST_OCCURRENCE['activity']: Throughput time starts at the last occurrence of the specified activity type.

    • CASE_START: Throughput time starts at the first activity of the case.

    • ALL_OCCURRENCE['']: Has the same meaning as CASE_START. The string parameter is ignored, but has to be specified. [ DEPRECATED | Use CASE_START instead.]

  • end_range_specifier: FIRST_OCCURRENCE['activity'] | LAST_OCCURRENCE['activity'] | CASE_END | ALL_OCCURRENCE['']

    • FIRST_OCCURRENCE['activity']: Throughput time ends at the first occurrence of the specified activity type.

    • LAST_OCCURRENCE['activity']: Throughput time ends at the last occurrence of the specified activity type.

    • CASE_END: Throughput time ends at the last activity of the case.

    • ALL_OCCURRENCE['']: Has the same meaning as CASE_END. The string parameter is ignored, but has to be specified. [ DEPRECATED | Use CASE_END instead.]

  • timestamps: Integer column of an activity table, often REMAP_TIMESTAMPS is used to convert a TIMESTAMP column.

  • activity_table.string_column: A string column of the same activity table as provided in the timestamps column. By default, the activity column of this activity table is used. The range specifiers are evaluated based on this column.

If start and end activity is conflicting, meaning the end activity is before the start activity the throughput calculation returns null for the conflicting case. Null is also returned if a case has only one activity.

If the activity name used in FIRST_OCCURRENCE or LAST_OCCURRENCE does not exist, then a warning is displayed. In this scenario the result value will be null.

Syntax for OCPM use cases

CALC_THROUGHPUT can also be used to calculate the time between two events that are not directly related to the same object in an OCPM data model.

Warning

This version can only be used with an OCPM data model.

Warning

This version implicitly creates an event log for this OCPM-specific syntax of CALC_THROUGHPUT, which may affect the query performance.

 CALC_THROUGHPUT ( object_table_name , begin_event_specifier TO end_event_specifier , time_unit )

  • object_table_name: The name of the object table, specifying the lead object that all events should be projected to.

  • begin_event_specifier: FIRST_OCCURRENCE['event'] | LAST_OCCURRENCE['event'] | CASE_START

    • FIRST_OCCURRENCE['event']: Throughput time starts at the first occurrence of the specified event.

    • LAST_OCCURRENCE['event']: Throughput time starts at the last occurrence of the specified event.

    • CASE_START: Throughput time starts at the first event of the case (i.e., the created event log of the object).

  • end_event_specifier: FIRST_OCCURRENCE['event'] | LAST_OCCURRENCE['event'] | CASE_END

    • FIRST_OCCURRENCE['event']: Throughput time ends at the first occurrence of the specified event.

    • LAST_OCCURRENCE['event']: Throughput time ends at the last occurrence of the specified event.

    • CASE_END: Throughput time ends at the last event of the case (i.e., the created event log of the object).

  • time_unit: The time unit in which the throughput time should be calculated. One of DAYS, HOURS, MINUTES, SECONDS or MILLISECONDS.

Use Cases
Examples

[1]

The throughput is calculated for the first A activity and the first B activity.

Query

Column1 

         CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'A' ] TO FIRST_OCCURRENCE [ 'B' ] , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : int

1

[2]

Here there is an attempt to calculate the throughput between last B and last A. Since the starting activity (last B) comes after the ending activity (last A), there is a conflict and NULL is returned.

Query

Column1 

         CALC_THROUGHPUT ( LAST_OCCURRENCE [ 'B' ] TO LAST_OCCURRENCE [ 'A' ] , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1

null

[3]

In the special case that start and end activity are the same, null is returned. In this example start and end are the same because the first and last activity A are pointing to the same activity log entry.

Query

Column1 

         CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'A' ] TO LAST_OCCURRENCE [ 'A' ] , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'C'

Tue Jan 01 2019 13:03:00.000

Result

Column1

null

[4]

The throughput is calculated for the last A activity and the last B activity.

Query

Column1 

         CALC_THROUGHPUT ( LAST_OCCURRENCE [ 'A' ] TO LAST_OCCURRENCE [ 'B' ] , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : int

4

[5]

The throughput is calculated for the first A activity and the last B activity.

Query

Column1 

         CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'A' ] TO LAST_OCCURRENCE [ 'B' ] , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : int

7

[6]

The throughput is calculated for the first A1 activity and the last B1 activity with a custom activity expression.

Query

Column1 

         CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'A1' ] TO LAST_OCCURRENCE [ 'B1' ] , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) , "Table1"."ACTIVITY" || '1' )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : int

7

[7]

The throughput is calculated from the beginning of the case till its end.

Query

Column1 

         CALC_THROUGHPUT ( CASE_START TO CASE_END , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:03:00.000

'1'

'B'

Tue Jan 01 2019 13:07:00.000

Result

Column1 : int

7

[8]

CALC_THROUGHPUT query using non-existing activity in FIRST_OCCURRENCE: Empty result and warning.

Query

Column1 

         CALC_THROUGHPUT ( FIRST_OCCURRENCE [ 'X' ] TO LAST_OCCURRENCE [ 'B' ] , REMAP_TIMESTAMPS ( "Table1"."TIMESTAMP" , MINUTES ) )

Input

Output

Table1

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

'1'

'A'

Tue Jan 01 2019 13:00:00.000

'1'

'B'

Tue Jan 01 2019 13:01:00.000

'1'

'A'

Tue Jan 01 2019 13:02:00.000

'1'

'B'

Tue Jan 01 2019 13:04:00.000

'1'

'A'

Tue Jan 01 2019 13:08:00.000

'1'

'B'

Tue Jan 01 2019 13:16:00.000

Result

Column1

null

Warning

CALC_THROUGHPUT: Source activity [X] could not be found in activity column ["Table1"."ACTIVITY"].

[9]

Calculates how long it takes until the last DeliveryItem for a SalesOrder is created.

Query

Column1 

         "o_celonis_SalesOrder"."ID"

Column2 

         CALC_THROUGHPUT ( o_celonis_SalesOrder , FIRST_OCCURRENCE [ 'e_celonis_CreateSalesOrder' ] TO LAST_OCCURRENCE [ 'e_celonis_CreateDeliveryItem' ] , DAYS )

Input

Output

e_celonis_AddSalesOrderItem

ID : string

TIME : date

SalesOrderItem_ID : string

'E010'

Thu Feb 02 2023 00:00:00.000

'O10'

'E011'

Fri Mar 03 2023 00:00:00.000

'O11'

'E012'

Sat Mar 04 2023 00:00:00.000

'O12'

'E013'

Sun Mar 05 2023 00:00:00.000

'O13'

'E014'

Mon Mar 06 2023 00:00:00.000

'O14'

'E015'

Fri Apr 07 2023 00:00:00.000

'O15'

e_celonis_CreateDeliveryItem

ID : string

TIME : date

DeliveryItem_ID : string

SalesOrderItem_ID : string

'E100'

Fri Feb 03 2023 00:00:00.000

'100'

'O10'

'E101'

Sat Mar 04 2023 00:00:00.000

'101'

'O11'

'E102'

Sun Mar 05 2023 00:00:00.000

'102'

'O12'

'E103'

Mon Mar 06 2023 00:00:00.000

'103'

'O13'

'E104'

Thu Mar 16 2023 00:00:00.000

'104'

'O14'

'E105'

Sun Apr 16 2023 00:00:00.000

'105'

null

e_celonis_CreateSalesOrder

ID : string

TIME : date

SalesOrder_ID : string

'E001'

Wed Feb 01 2023 00:00:00.000

'O01'

'E002'

Wed Mar 01 2023 00:00:00.000

'O02'

'E003'

Sat Apr 01 2023 00:00:00.000

'O03'

o_celonis_DeliveryItem

ID : string

SalesOrderItem_ID : string

'100'

'O10'

'101'

'O11'

'102'

'O12'

'103'

'O13'

'104'

'O14'

'105'

'O15'

o_celonis_SalesOrder

ID : string

'O01'

'O02'

'O03'

o_celonis_SalesOrderItem

ID : string

SalesOrder_ID : string

'O10'

'O01'

'O11'

'O02'

'O12'

'O02'

'O13'

'O02'

'O14'

'O02'

'O15'

'O03'

r_e_celonis_CreateSalesOrder_SalesOrderItem

ID : string

SalesOrderItem_ID : string

'E001'

'O10'

'E002'

'O11'

'E002'

'O12'

'E002'

'O13'

'E002'

'O14'

'E003'

null

Foreign Keys

o_celonis_SalesOrder.ID

o_celonis_SalesOrderItem.SalesOrder_ID

o_celonis_SalesOrderItem.ID

o_celonis_DeliveryItem.SalesOrderItem_ID

o_celonis_SalesOrder.ID

e_celonis_CreateSalesOrder.SalesOrder_ID

o_celonis_SalesOrderItem.ID

e_celonis_AddSalesOrderItem.SalesOrderItem_ID

o_celonis_SalesOrderItem.ID

e_celonis_CreateDeliveryItem.SalesOrderItem_ID

o_celonis_DeliveryItem.ID

e_celonis_CreateDeliveryItem.DeliveryItem_ID

e_celonis_CreateSalesOrder.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.ID

o_celonis_SalesOrderItem.ID

r_e_celonis_CreateSalesOrder_SalesOrderItem.SalesOrderItem_ID

Result

Column1 : string

Column2 : int

'O01'

2

'O02'

15

'O03'

null

See also: