Object-centric process mining database tables
Here's a reference for the tables that we use in the underlying object-centric process mining database in the OCPM Data Pool. It’s called the OCDM Schema - OCDM stands for object-centric data model. You can manage all these tables using the visual editors we provide, and you don't need to edit them directly.
Each table name is made up of these components:
A prefix giving the table type.
The namespace for the table's content.
celonis
is for object types, event types, and relationships prebuilt by Celonis, andcustom
is for object types and event types that you've created.The name of the object type, event type, or relationship that the table holds data for.
Relationship tables are only used for many to many relationships (m:n). The data for one to many relationships (1:m and m:1) is stored in a column in the table for the object type or event type.
Tables in the parallel database for the development environment have an extra prefix “t_”. When you write transformations, don't include the “t_” prefix - we'll rewrite the transformation to add it if it's for the development environment. Where we do include a table with a “t_” prefix in a data job, don't alter or remove it from the transformation.
Table | Table name prefix | Row contains |
---|---|---|
Object type | o_ | One object and the current values of its attributes |
Event type | e_ | One event and the current values of its attributes |
Change table | c_ | One change to one row of the referenced object type table |
Object to object relationship table | r_o_ | One relationship between two objects |
Event to object relationship table | r_e_ | One relationship between one event and one object |
CELONIS_CALENDAR table
The CELONIS_CALENDAR table replaces the functionality provided by the Vertica-specific operator TIMESERIES, which is not supported for object-centric process mining. The table contains dates in the range 1 January 1980 to 31 December 2030, which you can use for time series analytics. For example, here’s how to generate this date interval starting from 2017-01-01 and ending on 2017-01-05:
Date 2017-01-01 00:00:00 2017-01-02 00:00:00 2017-01-03 00:00:00 2017-01-04 00:00:00 2017-01-05 00:00:00
The SQL statements to do this with TIMESERIES would be:
WITH "TimeSlice" AS ( SELECT CAST('2017-01-01 00:00:00' AS TIMESTAMP) AS "TS" --StartDate UNION SELECT CAST('2017-01-05 00:00:00' AS TIMESTAMP) AS "TS" --EndDate ) SELECT "Date" FROM "TimeSlice" TIMESERIES "Date" AS '1 DAY' OVER (ORDER BY "TS")
With the CELONIS_CALENDAR table, you’d use these statements:
SELECT CAST("Date" AS TIMESTAMP) FROM "CELONIS_CALENDAR" WHERE "Date" >='2017-01-01' AND DATE <='2017-01-05' ORDER BY "Date"
For each date in the range, the CELONIS_CALENDAR table includes the attributes Date (date), Year (integer), Quarter (integer), Month (integer), Week (integer), Day (integer), DayOfWeek (integer), IsWeekend (boolean), FirstDayOfMonth (date), LastDayOfMonth (date), FirstDayOfWeek (date), LastDayOfWeek (date), DayName (varchar), and MonthName (varchar).