Create Object-centric Model from Relational Schema

This page describes how to create an object-centric model from a relational data schema. Tables in the relational data contain either objects or events in the object-centric model, and relations between the tables are described using foreign keys which will become the object-to-object and event-to-object relations. The rest of the datatables needed by the object-centric model are created automatically by a script from the object type and event type tables.

Model creation principle

The object-centric model is built with following principles:

Each datatable is either an object type or event type datatable.
For object type tables:
- Each table row is an object.
- Each timestamp in the 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 between the object type tables define the object-to-object relations.
For event type tables:
- Each row is an event
- The left-most date type of column contains the event timestamp.

Model creation steps

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

Add a project to QPR ProcessAnalyzer for the source datatables.
1. Add the source data as datatables.
2. Define relations between the object type and event type tables using the Data Schema Dialog.
3. Also define primary keys for each table.
Add another project for the object-centric model.
1. Add an object-centric model to the project.
2. Define the previously created tables as the Object types and Event types tables to the object-centric model settings .
3. Define names for the Objects, Events, ObjectToObject, and EventToObject datatables.
4. Add and run script to create ObjectsTypes and EventTypes datatables from the source datatables .
5. Add and run another script to create Objects, Events, ObjectToObject, and EventToObject datatables.

Validate schema

The relational schema should be validated which checks that foreign keys are consistent and there are no duplicates for the primary keys. When the object-centric model has been created from a valid relational schema, the created object-centric model is also valid. QPR ProcessAnalyzer has the model validation for object-centric models, but it only checks the model configuration and not the actual data in the Snowflake tables.

Create ObjectsTypes and EventTypes datatables

This script will create the ObjectsTypes and EventTypes tables to the project containing the object-centric model from the configured source project (in the first line of the script).

let sourceProject = ProjectByName("SAP data - dates converted");

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];
let objectTypeNames = modelObject.Configuration.OcelDataSource.ObjectTypes.Keys;
let eventTypeNames = modelObject.Configuration.OcelDataSource.EventTypes.Keys;

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

function importToDatatable(datatableName, df) {
  getDatatableAndCreateIfNeeded(datatableName)
    .Truncate()
    .Import(df, #{ "ImportExistingColumnOnly": false });
}

function createDatatableWithIdColumn(datatableName, idColumnName) {
  let sourceDatatable = sourceProject.DatatableByName(datatableName);
  let sourceDatatableName = sourceDatatable.Name;
  let sourcePrimaryKey = sourceDatatable.PrimaryKey;
  let sourceDataframe = sourceDatatable.SqlDataFrame;
  if (Count(sourcePrimaryKey) == 1) {
    sourceDataframe = sourceDataframe
      .WithColumn(idColumnName, Concat(#expr{sourceDatatableName}, "-", Column(sourcePrimaryKey[0])));
  } else if (Count(sourcePrimaryKey) == 2) {
    sourceDataframe = sourceDataframe
      .WithColumn(idColumnName, Concat(#expr{sourceDatatableName}, "-", Column(sourcePrimaryKey[0]), "-", Column(sourcePrimaryKey[1])));
  } else if (Count(sourcePrimaryKey) == 3) {
    sourceDataframe = sourceDataframe
      .WithColumn(idColumnName, Concat(#expr{sourceDatatableName}, "-", Column(sourcePrimaryKey[0]), "-", Column(sourcePrimaryKey[1]), "-", Column(sourcePrimaryKey[2])));
  }
  let newDatatable = importToDatatable(sourceDatatableName, sourceDataframe);
  newDatatable.SetPrimaryKey(sourcePrimaryKey);
}

let COLUMN_OBJECT_TYPES_OBJECT_ID = "OcelObjectTypeObjectId";
let COLUMN_EVENT_TYPES_EVENT_ID = "OcelEventTypeEventId";

for (let i = 0; i < Count(objectTypeNames); i++) {
  let datatableName = modelObject.OcelObjectTypeConfiguration(objectTypeNames[i]).Datatable;
  createDatatableWithIdColumn(datatableName, COLUMN_OBJECT_TYPES_OBJECT_ID);
}
for (let i = 0; i < Count(eventTypeNames); i++) {
  let datatableName = modelObject.Configuration.OcelDataSource.EventTypes[eventTypeNames[i]];
  createDatatableWithIdColumn(datatableName, COLUMN_EVENT_TYPES_EVENT_ID);
}

for (let i = 0; i < Count(objectTypeNames); i++) {
  let datatableName = modelObject.OcelObjectTypeConfiguration(objectTypeNames[i]).Datatable;
  let sourceDatatable = sourceProject.DatatableByName(datatableName);
  Project.DatatableByName(datatableName).SetForeignKeys(sourceDatatable.ForeignKeys);
}
for (let i = 0; i < Count(eventTypeNames); i++) {
  let datatableName = modelObject.Configuration.OcelDataSource.EventTypes[eventTypeNames[i]];
  let sourceDatatable = sourceProject.DatatableByName(datatableName);
  Project.DatatableByName(datatableName).SetForeignKeys(sourceDatatable.ForeignKeys);
}

Create Objects, Events, ObjectToObject, and EventToObject datatables

This script uses the object types and event 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. 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];
let COLUMN_EVENTS_EVENT_ID = "OcelEventId";
let COLUMN_EVENTS_TYPE = "OcelEventType";
let COLUMN_EVENTS_TIME = "OcelEventTime";
let COLUMN_OBJECTS_OBJECT_ID = "OcelObjectId";
let COLUMN_OBJECTS_TYPE = "OcelObjectType";
let COLUMN_EVENT_TYPES_EVENT_ID = "OcelEventTypeEventId";
let COLUMN_OBJECT_TYPES_OBJECT_ID = "OcelObjectTypeObjectId";
let COLUMN_EVENT_OBJECT_EVENT_ID = "OcelEventToObjectSourceId";
let COLUMN_EVENT_OBJECT_OBJECT_ID = "OcelEventToObjectTargetId";
let COLUMN_EVENT_OBJECT_QUALIFIER = "OcelEventToObjectQualifier";
let COLUMN_OBJECT_OBJECT_SOURCE_ID = "OcelObjectToObjectSourceId";
let COLUMN_OBJECT_OBJECT_TARGET_ID = "OcelObjectToObjectTargetId";
let COLUMN_OBJECT_OBJECT_QUALIFIER = "OcelObjectToObjectQualifier";
let COLUMN_OBJECT_TYPE = "Object Type";
let COLUMN_OBJECT_ID = "Object ID";
let TEMP_TABLE_SUFFIX = "_temp";
let SUB_LOG_ENTRY_SUFFIX = "-- ";

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

function importDataframeToDatatable(datatableName, df) {
  createOrGetDatatable(datatableName)
    .Import(df, #{ "ImportExistingColumnOnly": false });
}

createOrGetDatatable(modelObject.Configuration.OcelDataSource.Objects).Truncate();
createOrGetDatatable(modelObject.Configuration.OcelDataSource.Events).Truncate();
createOrGetDatatable(modelObject.Configuration.OcelDataSource.EventToObject).Truncate();
createOrGetDatatable(modelObject.Configuration.OcelDataSource.ObjectToObject).Truncate();

WriteLog("Add data to Objects table from ObjectTypes tables");
let objectTypeNames = modelObject.Configuration.OcelDataSource.ObjectTypes.Keys;
let objectsDatatableNames = objectTypeNames.{
  let objectTypeName = _;
  modelObject.OcelObjectTypeConfiguration(objectTypeName).Datatable;
};
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.OcelObjectTypeConfiguration(objectTypeNames[i]).Datatable;
  let objectsDatatable = Project.DatatableByName(objectsDatatableName);
  if (Count(objectsDatatable.Columns.Where(Name == COLUMN_OBJECT_TYPES_OBJECT_ID)) == 0) {
    throw "Objects datatable \"" + objectsDatatableName + "\" should have a column named " + COLUMN_OBJECT_TYPES_OBJECT_ID + ".";
  }
  let objectsDataFrame = objectsDatatable
    .SqlDataFrame
    .Select([COLUMN_OBJECT_TYPES_OBJECT_ID])
    .RenameColumns([COLUMN_OBJECTS_OBJECT_ID: COLUMN_OBJECT_TYPES_OBJECT_ID])
    .WithColumn(COLUMN_OBJECTS_TYPE, #expr{ objectTypeNames[i] })
    .Select([COLUMN_OBJECTS_OBJECT_ID, COLUMN_OBJECTS_TYPE]);
  WriteLog(SUB_LOG_ENTRY_SUFFIX + "Object type table: " + objectsDatatableName);
  importDataframeToDatatable(modelObject.Configuration.OcelDataSource.Objects, objectsDataFrame);
}

WriteLog("Add data to Events table from ObjectTypes tables");
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectTypeName = objectTypeNames[i];
  let objectsDatatableName = modelObject.OcelObjectTypeConfiguration(objectTypeName).Datatable;
  let objectsDatatable = Project.DatatableByName(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([COLUMN_OBJECT_TYPES_OBJECT_ID, #expr{ dateColumn }])
      .WithColumn(COLUMN_EVENTS_EVENT_ID, Concat(Column(COLUMN_OBJECT_TYPES_OBJECT_ID), "_", #expr{ dateColumn }))
      .WithColumn(COLUMN_EVENTS_TYPE, #expr{ dateColumn })
      .RenameColumns([COLUMN_EVENTS_TIME: #expr{ dateColumn }])
      .WithColumn(COLUMN_OBJECT_TYPE, #expr{ objectTypeName })
      .WithColumn(COLUMN_OBJECT_ID, Column(COLUMN_OBJECT_TYPES_OBJECT_ID))
      .Select([COLUMN_EVENTS_EVENT_ID, COLUMN_EVENTS_TYPE, COLUMN_EVENTS_TIME, COLUMN_OBJECT_TYPE, COLUMN_OBJECT_ID]);
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Object type table: " + objectTypeName + ", column: " + dateColumn);
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.Events, eventsDataFrame);
  }
}

WriteLog("Add data to Events table from EventTypes tables");
let eventTypeNames = modelObject.Configuration.OcelDataSource.TryGetValue("EventTypes").Keys;
for (let i = 0; i < Count(eventTypeNames); i++) {
  let eventTypeName = eventTypeNames[i];
  let eventsDatatableName = modelObject.Configuration.OcelDataSource.EventTypes[eventTypeName];
  let eventsDatatable = Project.DatatableByName(eventsDatatableName);
  if (Count(eventsDatatable.Columns.Where(Name == COLUMN_EVENT_TYPES_EVENT_ID)) == 0) {
    throw "Events datatable \"" + eventsDatatableName + "\" should have a column named " + COLUMN_EVENT_TYPES_EVENT_ID + ".";
  }
  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 + "\".";
  }
  for (let j = 0; j < Count(foreignKeys); j++) {
    let objectsTable = foreignKeys[j].TargetDatatable;

    // Define expression to generate the event type name. Examples (add between #sql{ ... }):
    // #expr{eventTypeName}
    // #expr{dateColumns[0]}
    // Column("OtherColumn")
    // Concat( Column("OtherColumn"), ": ", #expr{eventTypeName} )
    let eventTypeNameExpression = ToSqlExpression(#sql{ #expr{ eventTypeName } });

    let objectTypeName;
    for (let i = 0; i < Count(objectTypeNames); i++) {
      if (modelObject.OcelObjectTypeConfiguration(objectTypeNames[i]).Datatable == objectsTable) {
        objectTypeName = objectTypeNames[i];
        break;
      }
    }

    let objectsDataframe = Project.DatatableByName(objectsTable).DataFrame;

    let eventsDataFrame = eventsDatatable.SqlDataFrame;
    for (let i = 0; i < Count(foreignKeys[j].Columns); i++) {
      eventsDataFrame = eventsDataFrame.Where(Column(#expr{foreignKeys[j].Columns[i].Source}) != null);
    }
    eventsDataFrame
      .Where(Column(COLUMN_EVENT_TYPES_EVENT_ID) != null)
      .Join(objectsDataframe, foreignKeys[j].Columns.{ let col = _;[col.Source: col.Target] }[0], "inner") 
      .RenameColumns([COLUMN_EVENTS_EVENT_ID: COLUMN_EVENT_TYPES_EVENT_ID])
      .WithColumn(COLUMN_EVENTS_TYPE, #expr{ eventTypeNameExpression })
      .WithColumn(COLUMN_EVENTS_TIME, Column(dateColumns[0]))
      .WithColumn(COLUMN_OBJECT_TYPE, #expr{ objectTypeName })
      .WithColumn(COLUMN_OBJECT_ID, Column(COLUMN_OBJECT_TYPES_OBJECT_ID))
      .Select([COLUMN_EVENTS_EVENT_ID, COLUMN_EVENTS_TYPE, COLUMN_EVENTS_TIME, COLUMN_OBJECT_TYPE, COLUMN_OBJECT_ID]);
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Event type table: " + eventTypeName + ", foreign key: " + ToJson(foreignKeys[j]));
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.Events, eventsDataFrame);
  }
}

WriteLog("Add event type tables columns as common event attributes");
let commonEventColumns = Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events).Columns.Name;
for (let i = 0; i < Count(eventTypeNames); i++) {
  let eventsDatatableName = modelObject.Configuration.OcelDataSource.EventTypes[eventTypeNames[i]];
  let eventsDatatable = Project.DatatableByName(eventsDatatableName);

  let foreignKeys = eventsDatatable.ForeignKeys;
  let eventAttributeColumns = eventsDatatable.Columns.Name;
  for (let j = 0; j < Count(foreignKeys); j++) {
    eventAttributeColumns = Except(eventAttributeColumns, foreignKeys[j].Columns.Source);
  }
  eventAttributeColumns = Except(eventAttributeColumns, [(eventsDatatable.Columns.Where(DataType == "DateTime").Name)[0]]);
  eventAttributeColumns = Except(eventAttributeColumns, [COLUMN_EVENT_TYPES_EVENT_ID]);

  if (Count(eventAttributeColumns) > 0) {
    let mergedColumns = [];
    let columnRenames = #{ };
    eventAttributeColumns.{
      let eventAttribute = _;
      if (eventAttribute.In(commonEventColumns)) {
        mergedColumns = Concat(mergedColumns, [eventAttribute]);
        columnRenames.Add("_" + eventAttribute, eventAttribute);
      } else {
        commonEventColumns = Concat(commonEventColumns, [eventAttribute]);
        columnRenames.Add(eventAttribute, eventAttribute);
      }
    };
    columnRenames.Add(COLUMN_EVENT_TYPES_EVENT_ID, COLUMN_EVENT_TYPES_EVENT_ID);
    let eventTypeDataFrame = eventsDatatable.SqlDataFrame
      .Select(columnRenames);
    let eventsDatatableDf = Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events).SqlDataFrame;
    let allEventsDataFrame = eventsDatatableDf
      .Join(eventTypeDataFrame, [COLUMN_EVENTS_EVENT_ID: COLUMN_EVENT_TYPES_EVENT_ID], "leftouter")
      .RemoveColumns([COLUMN_EVENT_TYPES_EVENT_ID]);

    mergedColumns.{
      let mergedColumn = _;
      allEventsDataFrame = allEventsDataFrame
        .WithColumn(mergedColumn, Coalesce(Column(mergedColumn), Column("_" + mergedColumn)));
    };
    if (Count(mergedColumns) > 0) {
      allEventsDataFrame = allEventsDataFrame.RemoveColumns(mergedColumns.("_" + _));
    }
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Event type table: " + eventsDatatableName);
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.Events + TEMP_TABLE_SUFFIX, allEventsDataFrame);
    Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events).DeletePermanently();
    Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events + TEMP_TABLE_SUFFIX)
      .Modify(#{ "Name": modelObject.Configuration.OcelDataSource.Events });
  }
}

WriteLog("Add object attributes as common event attributes");
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.OcelObjectTypeConfiguration(objectTypeNames[i]).Datatable;
  let objectsDatatable = Project.DatatableByName(objectsDatatableName);
  let dateColumns = objectsDatatable.Columns.Where(DataType == "DateTime").Name;
  for (let j = 0; j < Count(dateColumns); j++) {
    let dateColumn = dateColumns[j];
    let objectAttributes = objectsDatatable.Columns.Name;
    objectAttributes = Except(objectAttributes, dateColumns);
    objectAttributes = Except(objectAttributes, [COLUMN_OBJECT_TYPES_OBJECT_ID]);

    let mergedColumns = [];
    let columnRenames = #{};
    objectAttributes.{
      let objectAttribute = _;
      if (objectAttribute.In(commonEventColumns)) {
        mergedColumns = Concat(mergedColumns, [objectAttribute]);
        columnRenames.Add("_" + objectAttribute, objectAttribute);
      } else {
        commonEventColumns = Concat(commonEventColumns, [objectAttribute]);
        columnRenames.Add(objectAttribute, objectAttribute);
      }
    };
    columnRenames.Add("_" + COLUMN_EVENTS_EVENT_ID, COLUMN_EVENTS_EVENT_ID);
    let objectAttributesDf = objectsDatatable.SqlDataFrame
      .Where(Column(#expr{ dateColumn }) != null)
      .WithColumn(COLUMN_EVENTS_EVENT_ID, Concat(Column(COLUMN_OBJECT_TYPES_OBJECT_ID), "_", #expr{ dateColumn }))
      .Select(columnRenames);
    let eventsDatatableDf = Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events).SqlDataFrame;
    let allEventsDataFrame = eventsDatatableDf
      .Join(objectAttributesDf, [COLUMN_EVENTS_EVENT_ID: ("_" + COLUMN_EVENTS_EVENT_ID)], "leftouter")
      .RemoveColumns(["_" + COLUMN_EVENTS_EVENT_ID]);
    mergedColumns.{
      let mergedColumn = _;
      allEventsDataFrame = allEventsDataFrame
        .WithColumn(mergedColumn, Coalesce(Column(mergedColumn), Column("_" + mergedColumn)));
    };
    allEventsDataFrame = allEventsDataFrame.RemoveColumns(mergedColumns.("_" + _));
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Object type table: " + objectsDatatableName + ", column: " + dateColumn);
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.Events + TEMP_TABLE_SUFFIX, allEventsDataFrame);
    Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events).DeletePermanently();
    Project.DatatableByName(modelObject.Configuration.OcelDataSource.Events + TEMP_TABLE_SUFFIX)
      .Modify(#{ "Name": modelObject.Configuration.OcelDataSource.Events });
  }
}

WriteLog("Add data to Event-to-Object table from ObjectTypes tables");
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.OcelObjectTypeConfiguration(objectTypeNames[i]).Datatable;
  let objectsDatatable = Project.DatatableByName(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([COLUMN_OBJECT_TYPES_OBJECT_ID])
      .WithColumn(COLUMN_EVENT_OBJECT_EVENT_ID, Concat(Column(COLUMN_OBJECT_TYPES_OBJECT_ID), "_", #expr{ dateColumn }))
      .RenameColumns([COLUMN_EVENT_OBJECT_OBJECT_ID: COLUMN_OBJECT_TYPES_OBJECT_ID])
      .WithColumn(COLUMN_EVENT_OBJECT_QUALIFIER, "")
      .Select([COLUMN_EVENT_OBJECT_EVENT_ID, COLUMN_EVENT_OBJECT_OBJECT_ID, COLUMN_EVENT_OBJECT_QUALIFIER]);
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Object type table: " + objectsDatatableName + ", column: " + dateColumn);
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.EventToObject, eventToObjectDataFrame);
  }
}

WriteLog("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 = Project.DatatableByName(eventsDatatableName);
  let foreignKeys = eventsDatatable.ForeignKeys;
  for (let j = 0; j < Count(foreignKeys); j++) {
    let objectsTable = foreignKeys[j].TargetDatatable;
    let objectsDataframe = Project.DatatableByName(objectsTable).DataFrame;

    let eventToObjectDataFrame = eventsDatatable.SqlDataFrame;
    for (let i = 0; i < Count(foreignKeys[j].Columns); i++) {
      eventToObjectDataFrame = eventToObjectDataFrame.Where(Column(#expr{foreignKeys[j].Columns[i].Source}) != null);
    }
    eventToObjectDataFrame
      .Where(Column(COLUMN_EVENT_TYPES_EVENT_ID) != null)
      .RenameColumns([COLUMN_EVENT_OBJECT_EVENT_ID: COLUMN_EVENT_TYPES_EVENT_ID])
      .Join(objectsDataframe, foreignKeys[j].Columns.{ let col = _;[col.Source: col.Target] }[0], "inner") 
      .WithColumn(COLUMN_EVENT_OBJECT_OBJECT_ID, Column(COLUMN_OBJECT_TYPES_OBJECT_ID))
      .WithColumn(COLUMN_EVENT_OBJECT_QUALIFIER, "")
      .Select([COLUMN_EVENT_OBJECT_EVENT_ID, COLUMN_EVENT_OBJECT_OBJECT_ID, COLUMN_EVENT_OBJECT_QUALIFIER]);
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Event type table: " + eventsDatatableName + ", foreign key: " + ToJson(foreignKeys));
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.EventToObject, eventToObjectDataFrame);
  }
}

WriteLog("Add data to Object-to-object table from ObjectTypes tables");
let TARGET_COLUMN_PREFIX = "Target";
for (let i = 0; i < Count(objectTypeNames); i++) {
  let objectsDatatableName = modelObject.OcelObjectTypeConfiguration(objectTypeNames[i]).Datatable;
  let objectsDatatable = Project.DatatableByName(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(Distinct(Flatten([COLUMN_OBJECT_TYPES_OBJECT_ID, joinedColumns.Target])))
      .RenameColumns(Flatten([COLUMN_OBJECT_TYPES_OBJECT_ID, joinedColumns.Target]).{ let col = _;[(TARGET_COLUMN_PREFIX + col): col] }[0]);
    let objectToObjectDataFrame = objectsDatatable.SqlDataFrame
      .Select(Distinct(Flatten([COLUMN_OBJECT_TYPES_OBJECT_ID, joinedColumns.Source])))
      .Join(targetDataFrame, joinedColumns.{ let col = _;[col.Source: (TARGET_COLUMN_PREFIX + col.Target)] }[0], "inner")
      .WithColumn(COLUMN_OBJECT_OBJECT_SOURCE_ID, Column(COLUMN_OBJECT_TYPES_OBJECT_ID))
      .WithColumn(COLUMN_OBJECT_OBJECT_TARGET_ID, Column(TARGET_COLUMN_PREFIX + COLUMN_OBJECT_TYPES_OBJECT_ID))
      .WithColumn(COLUMN_OBJECT_OBJECT_QUALIFIER, #expr{StringJoin(" - ", joinedColumns.Source)})
      .Select([COLUMN_OBJECT_OBJECT_SOURCE_ID, COLUMN_OBJECT_OBJECT_TARGET_ID, COLUMN_OBJECT_OBJECT_QUALIFIER]);
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Object type table: " + objectsDatatable + ", foreign key: " + ToJson(foreignKeys[j]));
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.ObjectToObject, objectToObjectDataFrame);
  }
}

WriteLog("Add data to Object-to-object table from datatables that are not defined as ObjectTypes tables but contains N-N relation between objects");
let eventsDatatableNames = modelObject.Configuration.OcelDataSource.EventTypes.Values;
let relationDatatableNames = Except(Except(Project.Datatables.Name, objectsDatatableNames), eventsDatatableNames);
for (let i = 0; i < Count(relationDatatableNames); i++) {
  let relationDatatable = Project.DatatableByName(relationDatatableNames[i]);
  let foreignKeys = relationDatatable.ForeignKeys;
  if (Count(foreignKeys) == 2) {
    let sourceDatatable = Project.DatatableByName(foreignKeys[0].TargetDatatable);
    let targetDatatable = Project.DatatableByName(foreignKeys[1].TargetDatatable);
    let sourceDatatableDataframe = sourceDatatable.DataFrame
      .RenameColumns([COLUMN_OBJECT_OBJECT_SOURCE_ID: COLUMN_OBJECT_TYPES_OBJECT_ID]);
    let targetDatatableDataframe = targetDatatable.DataFrame
      .RenameColumns([COLUMN_OBJECT_OBJECT_TARGET_ID: COLUMN_OBJECT_TYPES_OBJECT_ID]);
    let objectToObjectDataFrame = relationDatatable.SqlDataFrame
      .Join(sourceDatatableDataframe, foreignKeys[0].Columns.{ let col = _;[col.Source: col.Target] }[0], "inner")
      .Join(targetDatatableDataframe, foreignKeys[1].Columns.{ let col = _;[col.Source: col.Target] }[0], "inner")
      .WithColumn(COLUMN_OBJECT_OBJECT_QUALIFIER, #expr{relationDatatableNames[i]})
      .Select([COLUMN_OBJECT_OBJECT_SOURCE_ID, COLUMN_OBJECT_OBJECT_TARGET_ID, COLUMN_OBJECT_OBJECT_QUALIFIER]);
    WriteLog(SUB_LOG_ENTRY_SUFFIX + "Object type table: " + relationDatatableNames[i]);
    importDataframeToDatatable(modelObject.Configuration.OcelDataSource.ObjectToObject, objectToObjectDataFrame);
  }
}

Create Object-centric Model from Relational Schema

Contents

Model creation principle

Model creation steps

Validate schema

Create ObjectsTypes and EventTypes datatables

Create Objects, Events, ObjectToObject, and EventToObject datatables

Navigation menu

Create Object-centric Model from Relational Schema

Model creation principle

Model creation steps

Validate schema

Create ObjectsTypes and EventTypes datatables

Create Objects, Events, ObjectToObject, and EventToObject datatables

Navigation menu

Search