Applies to:  CELONIS 4.0 CELONIS 4.2 CELONIS 4.3 CELONIS 4.4 

Description

SOURCE / TARGET operators create a temporary table with values from different activities (Rows), which can be used to do calculations based on two actions of a case.

In Celonis, a case is stored as a list of activities, which fits nicely in the relational storage model used by Celonis. Unfortunately, it makes it hard to do calculations based on two actions of a case, like calculating the time spent between two activities, because the math operators work only on single rows. But to calculate something based on two activities an operator needs input from two rows.

The problem is solved via a temporary table in which the values from two rows are moved in to one single row. The input from this temporary table can than be used by all operators. Creating a temporary table, with values from different activities (Rows), can be done by the Source and Target operators. The Source/Target operators regard a case as edges and nodes. Nodes are the activities. Edges connect the activities in the order in which they happened. An edge points from a source activity to a target activity.

Syntax

Adds the values from Column, which correspond to a source activity, to a temporary table based on the optional edge configuration and the optional filter column:

SOURCE ( table.column [, edge_configuration ] [, table.filter_column ] )

Adds the values from Column, which correspond to a target activity, to a temporary table based on the optional edge configuration and the optional filter column:

TARGET ( table.column [, edge_configuration ] [, table.filter_column ] )

The following combinations are supported:

  • edge_configuration: The following combinations are supported:
    • ANY_OCCURRENCE[] TO ANY_OCCURRENCE[]: Default configuration, edges are drawn between consecutive activities within cases.

    • FIRST_OCCURRENCE[] TO ANY_OCCURRENCE[]: From the first activity of each case an edge is drawn to all other activities in this case.

    • FIRST_OCCURRENCE[] TO ANY_OCCURRENCE_WITH_SELF[]: From the first activity of each case an edge is drawn to all activities in this case, including the first one.

    • ANY_OCCURRENCE[] TO LAST_OCCURRENCE[]: All activities, besides the last one of the case, draw an edge to the last activity of the case.

    • FIRST_OCCURRENCE[] TO LAST_OCCURRENCE[]: From the first activity of a case an edge is drawn to the last activity in this case.

Examples

How Source, Target Operators interpret the case by default:

Query
Column1
SOURCE("ACTIVITIES"."ACTIVITY")
Column2
SOURCE("ACTIVITIES"."TIMESTAMP")
Column3
TARGET("ACTIVITIES"."ACTIVITY")
Column4
TARGET("ACTIVITIES"."TIMESTAMP")
Input
ACTIVITIES
CASE_ID : STRINGACTIVITY : STRINGTIMESTAMP : DATE
'1''A'
Sun Jan 01 2017 13:00:00.000
'1''B'
Sun Jan 01 2017 13:15:00.000
'1''C'
Sun Jan 01 2017 13:45:00.000
Output
Result
Column1 : STRINGColumn2 : DATEColumn3 : STRINGColumn4 : DATE
'A'
Sun Jan 01 2017 13:00:00.000
'B'
Sun Jan 01 2017 13:15:00.000
'B'
Sun Jan 01 2017 13:15:00.000
'C'
Sun Jan 01 2017 13:45:00.000



This can be combined with further operators. In the following example is the time between two activities calculated. REMAP_TIMESTAMPS is required to convert the timestamp to a number such that the subtraction can be done.
Query
Column1
SOURCE("ACTIVITIES"."ACTIVITY")
Column2
TARGET("ACTIVITIES"."ACTIVITY")
Column3
TARGET(REMAP_TIMESTAMPS("ACTIVITIES"."TIMESTAMP", MINUTES)) - SOURCE(REMAP_TIMESTAMPS("ACTIVITIES"."TIMESTAMP", MINUTES))
Input
ACTIVITIES
CASE_ID : STRINGACTIVITY : STRINGTIMESTAMP : DATE
'1''A'
Sun Jan 01 2017 13:00:00.000
'1''B'
Sun Jan 01 2017 13:15:00.000
'1''C'
Sun Jan 01 2017 13:45:00.000
Output
Result
Column1 : STRINGColumn2 : STRINGColumn3 : INT
'A''B'
15
'B''C'
30



Calculate the number of minutes since case start:
Query
Column1
SOURCE("ACTIVITIES"."ACTIVITY")
Column2
TARGET("ACTIVITIES"."ACTIVITY")
Column3
TARGET(REMAP_TIMESTAMPS("ACTIVITIES"."TIMESTAMP", MINUTES), FIRST_OCCURRENCE[] TO ANY_OCCURRENCE[]) - SOURCE(REMAP_TIMESTAMPS("ACTIVITIES"."TIMESTAMP", MINUTES), FIRST_OCCURRENCE[] TO ANY_OCCURRENCE[])
Input
ACTIVITIES
CASE_ID : STRINGACTIVITY : STRINGTIMESTAMP : DATE
'1''A'
Sun Jan 01 2017 13:00:00.000
'1''B'
Sun Jan 01 2017 13:15:00.000
'1''C'
Sun Jan 01 2017 13:45:00.000
Output
Result
Column1 : STRINGColumn2 : STRINGColumn3 : INT
'A''B'
15
'A''C'
45



Join behavior

Source/Target generate temporary tables. For each used unique edge and filter column configuration a separate temporary table is created. Source/Target operators with identical Edge and Filter column add columns to the same temporary table. The temporary tables are joined to the case table.

Example

In this example several calls to the Source/Target operator are done. After each call the tables status is shown.

  • Temporary Edge Table is created. Source column is added to it: 
    SOURCE ( Activity, ANY TO ANY )

  • Target column is added to existing temporary table: 
    TARGET ( Activity, ANY TO ANY )

  • This column is added to a new temporary table, because the configuration is different to the previous ones: 
    SOURCE ( Activity, ANY TO ANY, Filter )

Multiple temporary tables can lead to problems if used within the same query. This happens if Source/Target operators with different Edgify and Filter configurations are used within one query. The generated temporary tables can by default not be joined together and an error is returned. If one really needs to have two different configurations, the values in the temporary tables have be pulled up with PU functions to the case table.

Filter Behavior

We have seen that the temporary edge tables are joined to the case table. This means that if a case is filtered out in the case table, all activities related to that case in the temporary tables are also filtered out. This holds also vice versa, so if all activities related to a case are filtered out, the case itself is also filtered out in the case table. If not all activities of a case are filtered out, the case stays in the case table.

Filter behavior example

  • No labels