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 environment only supports a subset of functions and operators of ANSI SQL, and 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. At the end of this topic, there's advice on how to replace Vertica-specific functions that we don't support with suitable alternatives that are supported for object-centric process mining.
The list of enabled functions for case-centric transformations is different. You can find it at Supported functions for case-centric transformations.
Using Vertica SQL syntax has general Vertica recommendations for the Celonis Platform, and here's the official documentation: docs.vertica.com.
Aggregate operators
AVG
COUNT
FIRST_VALUE() with RESPECT NULLS | IGNORE NULLS
LAST_VALUE() with RESPECT NULLS | IGNORE NULLS
LAG
LEAD
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
DATEDIFF
DATE_TRUNC
EXTRACT
LEFT
LENGTH
LOWER
LPAD
LTRIM
NOW
NULLIF
POWER
RANDOM
REPLACE
RIGHT
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. |
|
|
| Import the column to be aggregated into a dedicated object type connected to the perspective, then use |
|
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 (;) |