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
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"
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' |
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.
Column1
REMAP_VALUES ( "Activities"."Activity", [ 'Approval Requested', 'Approval Requested' ], [ 'Approval Granted', 'Approval Granted' ], NULL )
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 |
---|
'Approval Requested' |
null |
'Approval Granted' |
'Approval Requested' |
null |
'Approval Granted' |
Column1
SOURCE ( "Activities"."Activity" )
Column2
SOURCE ( "Activities"."Department" )
Column3
TARGET ( "Activities"."Activity" )
Column4
TARGET ( "Activities"."Department" )
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' |
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'.
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" )
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' |
Based on the result above it is easy to build a FILTER which identifies rows in which the SOURCE(department) equals TARGET(department). Results of the SOURCE/TARGET operator are joined to the case table, which itself is joined to the activity table. In the solution below Celonis propagates the filter to the activity table.
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"
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' |
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.