Create Object-centric Model from Relational Schema

This page instructs how to create an object-centric model from a any relational data schema. The relational data schema contains tables for each object and relations between the tables are described using foreign keys. Other datatables needed by the object-centric model are automatically created by a script from the object tables. The object-centric model is built with following principles:

• Each table is an object type.
• Each table row is an object.
• Each timestamp in the object table is an event.
  • The event type name is the column name.
  • The event is linked to the object where the timestamp is as an object attribute (only one object is linked to each event).
• Foreign keys define the object-to-object relations.

To create an object-centric model using this method, proceed as follows:

1. Create the object tables. Each table needs to have the column named OcelObjectTypeObjectId containing unique ID's for objects.
2. Define relations between the object tables using foreign keys.
3. Create an object-centric model and define datatables for the model. For the Object types tables, define the tables created previously. In addition, define names for the Objects, Events, ObjectToObject, and EventToObject datatables (these datatables are created by the script later).
4. Run the script to update the object-centric model datatables.

Define foreign keys

Foreign keys define how the object tables (and thus individual objects) are linked together. The object-to-object relations in the object-centric model will be based on them. Foreign keys can be defined using the expression language. Here is an example script that sets some foreign keys:

// Set foreign key from datatable "Sales Order Item" to datatable "Sales Order Header". Source and target datatable column name is "VBELN".
Project.DatatableByName("Sales Order Item").SetForeignKeys([
  #{
    "Columns": [
      #{ "Source": "VBELN", "Target": "VBELN" }
    ],
    "TargetDatatable": "Sales Order Header"
  }
]);

// Set foreign key from datatable "Delivery Item" to datatable "Delivery Header". Source and target datatable column name is "VBELN".
// Set foreign key from datatable "Delivery Item" to datatable "Sales Order Item". Source datatable columns are "VGBEL" and "VGPOS", and corresponding columns in the target datatable are "VBELN" and "POSNR".
Project.DatatableByName("Delivery Item").SetForeignKeys([
  #{
    "Columns": [
      #{ "Source": "VBELN", "Target": "VBELN" }
    ],
    "TargetDatatable": "Delivery Header"
  },
  #{
    "Columns": [
      #{ "Source": "VGBEL", "Target": "VBELN" },
      #{ "Source": "VGPOS", "Target": "POSNR" }
    ],
    "TargetDatatable": "Sales Order Item"
  }
]);

Project.DatatableByName("Sales Order Changed").SetForeignKeys([
  #{
    "Columns": [
      #{ "Source": "Sales Order ID", "Target": "OcelObjectTypeObjectId" }
    ],
    "TargetDatatable": "Sales Order Header"
  }
]);

Update object-centric model datatables

Following script uses the object types datatables defined for the object-centric model as the source data to create the events, objects, event-to-object, and object-to-object tables (if they don't exist). The script can also be used to update existing datatables when the data in the object types datatables changes. Note that the event types tables are not needed and thus they are not created by the script. The script assumes that there is only one object-centric model in the project where the script is run.

let modelObject = Project.Models.Where(IsOcelModel);
if (Count(modelObject) == 0) {
  throw "There is no object-centric model in the project - there should be one model.";
} else if (Count(modelObject) > 1) {
  throw "There are several object-centric models in the project - there should be one model.";
}
modelObject = modelObject[0];

function getDatatableAndCreateIfNeeded(datatableName) {
  let dt = Project.DatatableByName(datatableName);
  if (dt == null) {
    dt = Project.CreateDatatable(#{
      "Name": datatableName,
      "Type": "Snowflake"
    });
  }
  return dt;
}

// Add data to Objects table from ObjectTypes tables.
let objectTypeNames = modelObject.Configuration.OcelDataSource.ObjectTypes.Keys;
let allObjectsDataFrame;
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.Configuration.OcelDataSource.ObjectTypes[objectTypeNames[i]];
  let objectsDataFrame = getDatatableAndCreateIfNeeded(objectsDatatableName)
    .SqlDataFrame
    .Select(["OcelObjectTypeObjectId"])
    .RenameColumns(["OcelObjectId": "OcelObjectTypeObjectId"])
    .WithColumn("OcelObjectType", #expr{ objectTypeNames[i]})
    .Select(["OcelObjectId", "OcelObjectType"]);
  if (allObjectsDataFrame == _EMPTY) {
    allObjectsDataFrame = objectsDataFrame;
  } else {
    allObjectsDataFrame = allObjectsDataFrame.Append(objectsDataFrame);
  }
}
getDatatableAndCreateIfNeeded(modelObject.Configuration.OcelDataSource.Objects)
  .Truncate()
  .Import(allObjectsDataFrame, #{ "ImportExistingColumnOnly": false });

// Add data to Events table from ObjectTypes tables.
let allEventsDataFrame;
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.Configuration.OcelDataSource.ObjectTypes[objectTypeNames[i]];
  let objectsDatatable = getDatatableAndCreateIfNeeded(objectsDatatableName);
  let dateColumns = objectsDatatable.Columns.Where(DataType == "DateTime").Name;
  for (let j = 0; j < Count(dateColumns); j++) {
    let dateColumn = dateColumns[j];
    let eventsDataFrame = objectsDatatable
      .SqlDataFrame
      .Where(Column(#expr{ dateColumn }) != null)
      .Select(["OcelObjectTypeObjectId", #expr{ dateColumn }])
      .WithColumn("OcelEventId", Concat(Column("OcelObjectTypeObjectId"), "_", #expr{ dateColumn }))
      .WithColumn("OcelEventType", #expr{ dateColumn })
      .RenameColumns(["OcelEventTime": #expr{ dateColumn }])
      .WithColumn("Object Type", #expr{ objectTypeNames[i]})
      .WithColumn("Object ID", Column("OcelObjectTypeObjectId"))
      .Select(["OcelEventId", "OcelEventType", "OcelEventTime", "Object Type", "Object ID"]);
    if (allEventsDataFrame == _EMPTY) {
      allEventsDataFrame = eventsDataFrame;
    } else {
      allEventsDataFrame = allEventsDataFrame.Append(eventsDataFrame);
    }
  }
}

// Add data to Events table from EventTypes tables.
let eventTypeNames = modelObject.Configuration.OcelDataSource.EventTypes.Keys;
for (let i = 0; i < Count(eventTypeNames); i++) {
  let eventsDatatableName = modelObject.Configuration.OcelDataSource.EventTypes[eventTypeNames[i]];
  let eventsDatatable = getDatatableAndCreateIfNeeded(eventsDatatableName);
  let dateColumns = eventsDatatable.Columns.Where(DataType == "DateTime").Name;
  if (Count(dateColumns) == 0) {
    throw "EventTypes datatable \"" + eventsDatatableName + "\" should have a date type of column for timestamps.";
  }

  let foreignKeys = eventsDatatable.ForeignKeys;
  if (Count(foreignKeys) == 0) {
    throw "Foreign key to the ObjectType table is missing from EventTypes datatable \"" + eventsDatatableName + "\".";
  }
  let objectsTable = foreignKeys[0].TargetDatatable;
  let objectIdColumn = foreignKeys[0].Columns[0].Source;

  let eventsDataFrame = eventsDatatable
    .SqlDataFrame
    .RenameColumns(["OcelEventId": "OcelEventTypeEventId"])
    .WithColumn("OcelEventType", #expr{ eventsDatatableName })
    .WithColumn("OcelEventTime", Column(dateColumns[0]))
    .WithColumn("Object Type", #expr{ objectsTable })
    .WithColumn("Object ID", Column(objectIdColumn))
    .Select(["OcelEventId", "OcelEventType", "OcelEventTime", "Object Type", "Object ID"]);
  if (allEventsDataFrame == _EMPTY) {
    allEventsDataFrame = eventsDataFrame;
  } else {
    allEventsDataFrame = allEventsDataFrame.Append(eventsDataFrame);
  }
}
getDatatableAndCreateIfNeeded(modelObject.Configuration.OcelDataSource.Events)
  .Truncate()
  .Import(allEventsDataFrame, #{ "ImportExistingColumnOnly": false });

// Add data to Event-to-Object table from ObjectTypes tables.
let allEventToObjectDataFrame;
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.Configuration.OcelDataSource.ObjectTypes[objectTypeNames[i]];
  let objectsDatatable = getDatatableAndCreateIfNeeded(objectsDatatableName);
  let dateColumns = objectsDatatable.Columns.Where(DataType == "DateTime").Name;
  for (let j = 0; j < Count(dateColumns); j++) {
    let dateColumn = dateColumns[j];
    let eventToObjectDataFrame = objectsDatatable
      .SqlDataFrame
      .Where(Column(#expr{ dateColumn }) != null)
      .Select(["OcelObjectTypeObjectId"])
      .WithColumn("OcelEventToObjectSourceId", Concat(Column("OcelObjectTypeObjectId"), "_", #expr{ dateColumn }))
      .RenameColumns(["OcelEventToObjectTargetId": "OcelObjectTypeObjectId"])
      .WithColumn("OcelEventToObjectQualifier", "")
      .Select(["OcelEventToObjectSourceId", "OcelEventToObjectTargetId", "OcelEventToObjectQualifier"]);
    if (allEventToObjectDataFrame == _EMPTY) {
      allEventToObjectDataFrame = eventToObjectDataFrame;
    } else {
      allEventToObjectDataFrame = allEventToObjectDataFrame.Append(eventToObjectDataFrame);
    }
  }
}

// Add data to Event-to-Object table from EventTypes tables.
for (let i = 0; i < Count(eventTypeNames); i++) {
  let eventsDatatableName = modelObject.Configuration.OcelDataSource.EventTypes[eventTypeNames[i]];
  let eventsDatatable = getDatatableAndCreateIfNeeded(eventsDatatableName);
  let objectIdColumn = eventsDatatable.ForeignKeys[0].Columns[0].Source;
  let eventToObjectDataFrame = eventsDatatable
    .SqlDataFrame
    .RenameColumns(["OcelEventToObjectSourceId": "OcelEventTypeEventId"])
    .WithColumn("OcelEventToObjectTargetId", Column(objectIdColumn))
    .WithColumn("OcelEventToObjectQualifier", "")
    .Select(["OcelEventToObjectSourceId", "OcelEventToObjectTargetId", "OcelEventToObjectQualifier"]);
  if (allEventToObjectDataFrame == _EMPTY) {
    allEventToObjectDataFrame = eventToObjectDataFrame;
  } else {
    allEventToObjectDataFrame = allEventToObjectDataFrame.Append(eventToObjectDataFrame);
  }
}
getDatatableAndCreateIfNeeded(modelObject.Configuration.OcelDataSource.EventToObject)
  .Truncate()
  .Import(allEventToObjectDataFrame, #{ "ImportExistingColumnOnly": false });

// Add data to Object-to-object table from ObjectTypes tables.
let allObjectToObjectDataFrame;
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.Configuration.OcelDataSource.ObjectTypes[objectTypeNames[i]];
  let objectsDatatable = getDatatableAndCreateIfNeeded(objectsDatatableName);
  let foreignKeys = objectsDatatable.ForeignKeys;
  for (let j = 0; j < Count(foreignKeys); j++) {
    let joinedColumns = foreignKeys[j].Columns;
    let targetDataFrame = Project.DatatableByName(foreignKeys[j].TargetDatatable).SqlDataFrame
      .Select(Flatten(["OcelObjectTypeObjectId", joinedColumns.Target]))
      .RenameColumns(Flatten(["OcelObjectTypeObjectId", joinedColumns.Target]).{ let col = _;[("Target" + col): col] }[0]);
    let objectToObjectDataFrame = objectsDatatable.SqlDataFrame
      .Select(Flatten(["OcelObjectTypeObjectId", joinedColumns.Source]))
      .Join(targetDataFrame, joinedColumns.{ let col = _;[col.Source: ("Target" + col.Target)] }[0], "inner")
      .WithColumn("OcelObjectToObjectSourceId", Column("OcelObjectTypeObjectId"))
      .WithColumn("OcelObjectToObjectTargetId", Column("TargetOcelObjectTypeObjectId"))
      .WithColumn("OcelObjectToObjectQualifier", "")
      .Select(["OcelObjectToObjectSourceId", "OcelObjectToObjectTargetId", "OcelObjectToObjectQualifier"]);
    if (allObjectToObjectDataFrame == _EMPTY) {
      allObjectToObjectDataFrame = objectToObjectDataFrame;
    } else {
      allObjectToObjectDataFrame = allObjectToObjectDataFrame.Append(objectToObjectDataFrame);
    }
  }
}
getDatatableAndCreateIfNeeded(modelObject.Configuration.OcelDataSource.ObjectToObject)
  .Truncate()
  .Import(allObjectToObjectDataFrame, #{ "ImportExistingColumnOnly": false });