Skip to main content

Celonis Product Documentation

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

  • 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.

Table 12. Vertica SQL functions and replacements

Vertica SQL function

Replacement

ISNULL(), IFNULL()

COALESCE()

The fields must have the same data type.

NULLIFZERO()

NULLIF(xxx, 0)

LISTAGG

Import the column to be aggregated into a dedicated object type connected to the perspective, then use PU_STRING_AGG in PQL to aggregate it to the level of the target object.

‘value’::TYPE for literals

TYPE’value’

Vertica allows you to specify a literal with the TIMESTAMP type using ‘2023-01-01’::timestamp. The ANSI-SQL compatible way is timestamp’2023-01-01’.

DATEDIFF for entities smaller than a day

TIMESTAMPDIFF

TIMESTAMPDIFF supports an argument for the unit of the result, which Vertica SQL supported for DATEDIFF but is not standard for that function.

DATE_PART()

EXTRACT(DAY|/MONTH|/YEAR FROM ...)

Implicit casting

CAST(xxx AS <TYPE>)

Explicitly cast the data types.

Implicit data type conversion for IDs from INT to STRING

CAST(xxx AS VARCHAR)

All IDs or primary keys for objects and events must be a string, and you need to do the conversion explicitly.

TO_NUMBER()

CAST(xxx AS FLOAT)

TO_TIMESTAMP

TIMESTAMPADD(SECOND, 1706692473, CAST('1970-01-01 00:00:00' AS DATE))

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 TO_TIMESTAMP during pre-processing to produce a timestamp in the required format, for example:

TO_TIMESTAMP(''|| "FIELD_XXX", 'YYYYMMDDHHMISS.MS')

CAST(xxx AS DATETIME)

CAST(xxx AS TIMESTAMP)

DATEADD()

CAST(table.some_column AS TIMESTAMP) + INTERVAL '1' SECOND

Or use TIMESTAMPADD()

TIMESERIES

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

table."TIME"

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 (;)