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:
One or more letter prefixes that show what type of table this is.
The namespace for the table's content.
celonis
is for object types, event types, and relationships from the Celonis catalog, andcustom
is for object types, event types, and relationships that you've created. Apps can have their own namespaces for app-specific object types, event types, and relationships, such asDuplicateInvoiceChecker
for the Duplicate Invoice Checker app.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.
For an object type that's embedded in a perspective, a suffix of a double underscore followed by the name of the relationship that the object type was embedded with (for example,
__approver
).
Object-centric process mining table prefixes lists the possible letter prefixes for table names. They can be combined with each other to give a complete description of the table. For example:
c_o
at the start of a table name means it's a table of changes (c
) to objects of the specified type (o
).t_r_o
at the start of a table name means it's a table of object to object relationships (r_o
) , and it's in the development environment (t
).
Note
Tables in the parallel database for the development environment have the 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 |
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 |
Change table | c_ | One change to one row of the table that makes up the rest of the table name (such as an object type table) |
Table in test schema | t_ | The normal contents of the named table, but it's in the development environment, not the production environment |
Extension table | x_ | Only visible in limited contexts |
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).