A.1 Process Query Language (PQL)
Process Query Language (PQL) is a extension to the normal SQL used to query databases. PQL has been especially designed to query and filter process flows and process patterns.
Consequently, PQL offers many additional commands to improve the analysis of processes using Celonis Process Mining 3. Besides, PQL supports all standard SQL commands of the used database server (e.g. MS SQL, SAP HANA, and Oracle). Therefore all standard commands and functions of the database server can be used in CPM 3, too. E.g. one can use the SAP HANA function "WORKDAYS_BETWEEN" to determine the number of workdays between to dates in CPM 3 or the MS SQL Function "GETDATE()" to query the current time.
A.2 PQL Syntax
| Command | Description |
|---|---|
| count(X) | Counts the frequency of X. Empty rows will not be counted. |
| sum(X) | Calculates the sum of X (Premise: X is a number) |
| avg(X) | Calculates the average of X (Premise: X is a number) |
| max(X) | Calculates the maximum of X (Premise: X is a number or a date) |
| min(X) | Calculates the maximum of X (Premise: X is a number or a date) |
| median(X) | Calculates the median of X (Premise: X is a number) Note: The median calculation will only be available if it has been configured for your system. |
| A (not) like ‚B‘ | Compares the content of Field A with the Value B. Wildcards: _= one arbitrary character, % = one or many arbitrary characters. |
| A not in (‚E1‘,‘E2‘,‘E3‘) | Compares the content of Field A with the Values E1,E2 and E3 and chooses all values that match E1, E2 or E3. |
| datepart(PART,TS) | Chooses the PART from a date TS, e.g. PART = yy → year, mm → month, dd → day, hh → hour, mi → minute, wk → week of year, dw → day of week (1=Sunday,…,7=Saturday) |
| dategroup(GROUP,TS) | Rounds a date TS to the next GROUP, e.g. GROUP = yy → year, mm → month, dd → day, hh → hour, mi → minute, wk → week of year, dw → day of week (1=Sunday,…,7=Saturday) |
| left(XYZ,int) | Selects the first „int“ characters from XYZ |
| right(XYZ,int) | Selects the last „int“ characters from XYZ |
| case when ABC then XYZ when QWE then RTY else … end | If ABC is true then do XYZ when QWE is true then do RTY else… Note: Don’t forget the END! |
| count(distinct „COLUMN“) | Counts the distinct frequency of the expression „COLUMN“, i.e. every entry is counted that is different from the entries before. Empty rows will not be counted. |
An example for a process relevant KPI is the average idle period that can be calculated as follows:
avg(1.0*datediff (hh,source.assignment\_transmissiondate,target.assignment\_transmissiondate))
Instead of calculating the average with avg one could also use e.g. min or max in order to calculate the minimal/maximal idle time. In this case we use hh in order to get the results in hours but we could also use for instance dd to get the result in days.
Process Filters with PQL
Beside the commands to calculate KPIs and Dimensions, PQL offers process specific commands and filters. Usings these one can easily query certain process patterns, independently of their complexity.
Furthermore, these process related commands can be combined with the other PQL commands. I.e. one can easily calculate the average runtimes for certain process patterns etc.
| Command | Description |
|---|---|
| process#'Activity XYZ' | Filters to all cases that are passing once or several times by "Activity XYZ" |
| process NOT#'Activity XYZ' | Filters to all cases that are NOT passing by "Activity XYZ" |
| process#START'Activity XYZ' | Filters to all cases thar are starting with "Activity XYZ" |
| process#'Activity XYZ'END | Filters to all cases that are ending with "Activity XYZ" |
| process#'Activity XYZ' TO 'Activity ABC' | Filters to all cases thare are passing once or several times by the edge between "Activity XYZ" and "Activity ABC" |
| process#START'Activity XYZ' TO 'Activity ABC' | Filters to all cases thare are starting with "Activity XYZ" and that are passing once or several times by the edge between "Activity XYZ" and "Activity ABC" |
| process#'Activity XYZ' TO 'Activity ABC' END | Filters to all cases thare are starting with "Activity XYZ" and thare are passing once or several times by the edge between "Activity XYZ" and "Activity ABC" |
Please note: It is crucial that you brace the activity name with a ' (not")!
A.2 Root Cause Analysis
The Root Cause Analysis offers a statistical view on the data attributes. It will compare the data selected through the current filter setting (variant) with the complete data set (reference). For each chosen attribute a comparison of the absolute and relative frequency of every attribute value within the filtered and the whole data set is made.