Create Object-centric Model from Relational Schema

This page contains instructions how to create an object-centric model from a typical relational data schema. The idea is that it's enough to define tables for each object and the relations between the object tables using foreign keys. All other tables needed by the object-centric model are created by a script from the object tables. The tables are created with following principles:

• 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.
2. Define relations between the object tables using foreign keys.
3. Create an object-centric model and define datatables for the model. For the ObjectTypes tables, define the existing tables created previously. In addition, define the Objects, Events, ObjectToObject, and EventToObject tables (the datatables won't exist at this point but they are created automatically by the script).
4. Run the script to update the object-centric model datatables.

Define foreign keys

Foreign keys define how the objects are linked together, and the object-to-object relations in the object-centric model will be based on them. Foreign keys are defined using the expression language in a script or in the Expression Designer.

Example of defining foreign keys:

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

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"
  }
]);

Update object-centric model tables

Following script uses the object types tables and creates the events, objects, event-to-object, and object-to-object tables. Note that the event types tables are not needed at all in this approach. The scripts uses the datatables defined in the object-centric model. 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.";
} else if (Count(modelObject) > 1) {
  throw "There are several object-centric models in the project.";
} 
modelObject = modelObject[0];
let objectTypeNames = modelObject.Configuration.OcelDataSource.ObjectTypes.Keys;

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

// Create Objects table
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);
  }
}
Project.DatatableByName(modelObject.Configuration.OcelDataSource.Objects)
  .Truncate()
  .Import(allObjectsDataFrame, #{ "ImportExistingColumnOnly": false });

// Create Events table
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);
    }
  }
}
Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events)
  .Truncate()
  .Import(allEventsDataFrame, #{ "ImportExistingColumnOnly": false });

// Create Event-to-Object table
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);
    }
  }
}
Project.DatatableByName(modelObject.Configuration.OcelDataSource.EventToObject)
  .Truncate()
  .Import(allEventToObjectDataFrame, #{ "ImportExistingColumnOnly": false });

// Create Object-to-object table
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);
   }
  }
}
Project.DatatableByName(modelObject.Configuration.OcelDataSource.ObjectToObject)
  .Truncate()
  .Import(allObjectToObjectDataFrame, #{ "ImportExistingColumnOnly": false });