Skip to main content

PQL46 (PQL Function Library - CPM 4.6)

Segregation of duties

Applies to: CELONIS 4.5 CELONIS 4.6

Description

This example shows how to identify violations of a segregation of duties policy with PQL.Segregation of duties enforces that within one case certain activities are not executed by the same department. For example there could be the policy that an approval can not be granted by the same department that requests the approval.To analyze if the duties are separated, we assign labels to each case:]

  • Segregation of duties fulfilled: Selected activities were performed by different departments, e.g. Approval Requested (Department 1) → Approval Granted (Department 2)

  • Segregation of duties violated: The same department performed the selected activities, e.g. Approval Requested (Department 1) → Approval Granted (Department 1)

Challenge
Identify cases in which the segregation of duties is violated for the activity pair 'Approval Requested' and 'Approval Granted'. One can assume that an 'Approval Granted' activity or the case end will eventually follow after an 'Approval Requested' activity. In between, any kind of activity can happen.
Solution

[1] The segregation of duties problem can be solved using the following query. In this example, case 1 obeys segregation of duties ('Approval Requested' and 'Approval Granted' was done by different departments), and case 2 violates it ('Approval Requested' and 'Approval Granted' was done by the same department).

Query

Filter

FILTER SOURCE ( "Activities"."Department", REMAP_VALUES ( "Activities"."Activity", [ 'Approval Requested', 'Approval Requested' ], [ 'Approval Granted', 'Approval Granted' ], NULL ) ) = TARGET ( "Activities"."Department" )

Column1

"Activities_CASES"."Case"

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

Department : STRING

1

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-1'

1

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

1

'Approval Granted'

Mon Feb 01 2016

04:00:00.000

'D-2'

2

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-2'

2

'Do some work'

Mon Feb 01 2016 01:00:00.000

'D-1'

2

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

Activities_CASES

Case : INT

1

2

Foreign Keys

Activities.Case

Activities_CASES.Case

Result

Column1 : INT

2

Step-by-step solution

Now let's take a closer look on the operators REMAP_VALUES and SOURCE/TARGET which are used in the solution.

[2] To be able to compare the 'Approval Requested' and 'Approval Granted' activities, we need to set all irrelevant activities to NULL. We can do so by using REMAP_VALUES.

Query

Column1

REMAP_VALUES ( "Activities"."Activity", [ 'Approval Requested', 'Approval Requested' ], [ 'Approval Granted', 'Approval Granted' ], NULL )

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

Department : STRING

1

'Approval Requested'

Mon Feb 01 2016 0:00:00.000

'D-1'

1

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

1

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

2

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-2'

2

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

2

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

Result

Column1 : STRING

'Approval Requested'

null

'Approval Granted'

'Approval Requested'

null

'Approval Granted'

[3] To compare one activity to its successor SOURCE/TARGET is used. The result of the operator is a temporary table in which values of a (source) activity are mapped to the same row as the values of the following (target) activity.

Query

Column1

SOURCE ( "Activities"."Activity" )

Column2

SOURCE ( "Activities"."Department" )

Column3

TARGET ( "Activities"."Activity" )

Column4

TARGET ( "Activities"."Department" )

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

Department : STRING

1

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-1'

1

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

1

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

2

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-2'

2

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

2

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

Result

Column1 : STRING

Column2 : STRING

Column3 : STRING

Column4 : STRING

'Approval Requested'

'D-1'

'D-2'

'D-1'

'Do some work'

'D-1'

'Approval Granted'

'D-2'

'Approval Requested'

'D-2'

'D-2'

'D-1'

'Do some work'

'D-1'

'Approval Granted'

'D-2'

This way we can do calculations and comparisons between consecutive activities. But to solve the problem it is not enough to compare directly consecutive activities. We are only interested in the activities 'Approval Requested' and 'Approval Granted'.

[4] To only consider 'Approval Requested' and 'Approval Granted' activities, a filter column can be added to the  SOURCE/TARGET operator. Each row which is NULL in the filter column is ignored by the operator.

Query

Column1

SOURCE ( "Activities"."Activity", REMAP_VALUES ( "Activities"."Activity", [ 'Approval Requested', 'Approval Requested' ], [ 'Approval Granted', 'Approval Granted' ], NULL ) )

Column2

SOURCE ( "Activities"."Department" )

Column3

TARGET ( "Activities"."Activity" )

Column4

TARGET ( "Activities"."Department" )

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

Department : STRING

1

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-1'

1

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

1

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

2

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-2'

2

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

2

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

Result

Column1 : STRING

Column2 : STRING

Column3 : STRING

Column4 : STRING

'Approval Requested'

'D-1'

'Approval Granted'

'D-2'

'Approval Requested'

'D-2'

'Approval Granted'

'D-2'

[5] The filter identifies violating cases. In the example, Case 2 violates the segregation of duties. The result table contains all rows of the activity table of the violating case.

Query

Filter

FILTER SOURCE ( "Activities"."Department", REMAP_VALUES ( "Activities"."Activity", [ 'Approval Requested', 'Approval Requested' ], [ 'Approval Granted', 'Approval Granted' ], NULL ) ) = TARGET ( "Activities"."Department" )

Column1

"Activities"."Case"

Column2

"Activities"."Activity"

Column3

"Activities"."Department"

Input

Output

Activities

Case : INT

Activity : STRING

Timestamp : DATE

Department : STRING

1

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-1'

1

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

1

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

2

'Approval Requested'

Mon Feb 01 2016 01:00:00.000

'D-2'

2

'Do some work'

Mon Feb 01 2016 02:00:00.000

'D-1'

2

'Approval Granted'

Mon Feb 01 2016 04:00:00.000

'D-2'

Result

Column1 : INT

Column2 : STRING

Column3 : STRING

2

'Approval Requested'

'D-2'

2

'Do some work'

'D-1'

2

'Approval Granted'

'D-2'

To just obtain the violating case-IDs, the above filter expression can be applied to the case column of the case table as shown in the 'Solution' section above.