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 a column, which correspond to a source activity, to a temporary table based on the optional filter column and the optional edge configuration:

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

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

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

• table.column: Data transformed to SOURCE / TARGET layout.
• table.filter_column: Rows in which the value of the filter column are null, are ignored by the SOURCE / TARGET operators.
• table.edge_configuration: Describes which edges between the activities of a case are considered. See below for detailed explanation.

Examples

Edge Configuration

With this parameter the edge configuration can be defined. The edge configuration describes which edges between the activities of a case are considered.

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

    

Example

Configuration propagation

SOURCE / TARGET can be configured in many ways. Except in very rare corner cases, it is desired to apply the same configuration to all SOURCE / TARGET operators in a table or a filter statement. Therefore a configuration has to be specified only once and is automatically propagated to all other occurences of SOURCE / TARGET in the same table or filter statement. A configuration is only propageted to SOURCE / TARGET operators which don't have a own configuration and it is also never done across a filter and a table statement, even if they are part of the same component.

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_OCCURRENCE[] TO ANY_OCCURRENCE[] )

  

• SOURCE column is added to existing temporary table:

  TARGET ( Activity, ANY_OCCURRENCE[] TO ANY_OCCURRENCE[] )

  

• This column is added to a new temporary table, because the configuration is different to the previous ones:

  SOURCE ( Activity, Filter, ANY_OCCURRENCE[] TO ANY_OCCURRENCE[] )

  

Multiple temporary tables can lead to problems if used within the same query. This happens if SOURCE / TARGET operators with different edge 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.

Examples

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.