Supported functions for object-centric transformations
The SQL dialect you need to use in transformations for the Celonis Platform is Vertica SQL syntax. To keep the SQL transformations as generic as possible, the SQL editor in the Objects and Events module only supports a subset of ANSI SQL functions and operators, so not all Vertica functions are enabled.
The functions and operators on this list are enabled for use in custom transformations for object-centric process mining. Refer to the table below for advice on how to replace Vertica-specific functions that is not supported with suitable alternatives that are supported for object-centric process mining.
The list of enabled functions for case-centric transformations is different and can be found here - Supported functions for case-centric transformations.
Using Vertica SQL syntax has general Vertica recommendations for the Celonis Platform, and the official documentation can be found at docs.vertica.com.
Aggregate operators
AVG
COUNT
FIRST_VALUE() with RESPECT NULLS | IGNORE NULLS
LAST_VALUE() with RESPECT NULLS | IGNORE NULLS
LAG
LEAD
LISTAGG
MIN
MAX
SUM
Date part functions
YEAR
QUARTER
MONTH
WEEK
DAYOFYEAR
DAYOFMONTH
DAYOFWEEK
HOUR
MINUTE
SECOND
Window rank functions
DENSE_RANK
RANK
ROW_NUMBER
Scalar functions
ABS
CAST
COALESCE (fields must have the same data type)
CONCAT
CONVERT_TIMEZONE
DATEDIFF
DATE_TRUNC
EXTRACT
LEFT
LENGTH
LOWER
LPAD
LTRIM
NOW
NULLIF
POWER
RANDOM
REPLACE
RIGHT
RTRIM
SPLIT_PART
SUBSTRING
TIMESTAMPADD
TIMESTAMPDIFF
TRIM
UPPER
Replacing functions no longer supported for object-centric data models
Here's how to replace functions that were supported with Vertica SQL and case-centric transformations, with suitable alternatives.
Vertica SQL function | Replacement |
---|---|
|
The fields must have the same data type. |
|
|
|
Vertica allows you to specify a literal with the TIMESTAMP type using |
|
|
|
|
Implicit casting |
Explicitly cast the data types. |
Implicit data type conversion for IDs from INT to STRING |
All IDs or primary keys for objects and events must be a string, and you need to do the conversion explicitly. |
|
|
|
Your epoch timestamp (here it's a ten-digit timestamp 1706692473) must be an integer and have the right number of digits to convert to your selected datetime format. Alternatively, you may use
|
|
|
|
Or use |
| Use the CELONIS_CALENDAR table, for example: SELECT CAST("Date" AS TIMESTAMP) FROM "CELONIS_CALENDAR" WHERE "Date" >='2017-01-01' AND DATE <='2017-01-05' ORDER BY "Date" |
Using table and column names such as TIME, CAST, SELECT without quotation marks |
All table and column names that match an SQL keyword need to be enclosed with proper quotation marks. |
End a script with a semi-colon (;) | Do not end a script with a semi-colon (;) |