Regular Operations

Rebuilding Indices

After a major replication (for example when a tenant first migrates their lines into the service), it will most likely be necessary to rebuild the following indices to maintain performance.

IX_AggCurrentShifts_TenantId
PK_AggCurrentShifts
IX_AggCycletimePercentiles_ShiftUuid
PK_AggCycletimePercentiles
IX_AggData_LocationId_TenantId
IX_AggData_LocationId_TenantId_WorkingDay
IX_AggData_TenantId
PK_AggData
PK_AggInterruptions
IX_AggNiobits_ShiftUuid_TypeNumber_TypeVariant
PK_AggNiobits
IX_AggStationerrors_ShiftUuid
PK_AggStationerrors
PK_AggTypeData
IX_DispatchRepl_ShiftUuid
IX_DispatchRepl_TenantId
PK_DispatchRepl
PK_KPIs
IX_LineMetrics_LineId
IX_LineReplicationConfig_ExternalSystemConfigId
PK_LineReplicationConfig
IX_Lines_PlantId
IX_Lines_ProductId
PK_Links
PK_Plants
PK_Products

User Delete Events

When a user is deleted in MACMA, GPO will remove their entries from the user settings table on the reception of the integration event from RabbitMQ. One current limitation of MACMA is that if a user is deleted in the linked OIDC provider (Microsoft Entra for example), then MACMA does not receive this information and will not send out the user deleted event.

Deleting Data of a Particular Tenant

When a tenant is deleted in MACMA, GPO will remove all the data for that tenant from the user settings table on the reception of the integration event from RabbitMQ.

To delete the rest of the data of a particular tenant, the following script should be executed:

Oracle

tenant_id CONSTANT VARCHAR2(128) := '<tenant ID>';

BEGIN
delete from QRTZ_JOB_DETAILS where job_group = tenant_id;
delete from "Lines" where "TenantId" = tenant_id;

for t in (select table_name from user_tab_columns where column_name = 'TenantId') LOOP
    execute immediate 'delete from "' || t.table_name || '" where "TenantId"=''' || tenant_id || '''';
END LOOP;
END;

MSSQL

DECLARE @TenantId uniqueidentifier = '<tenant ID>';

DELETE FROM QRTZ_JOB_DETAILS WHERE JOB_GROUP = @TenantId;
DELETE FROM "Lines" WHERE "TenantId" = @TenantId;

SET NOCOUNT ON
DROP TABLE IF EXISTS #TEMPTABLENAMES
DECLARE @TableName varchar(250)

SELECT TABLE_NAME INTO #TEMPTABLENAMES FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'TENANTID'

SELECT TOP(1) @TableName = TABLE_NAME FROM #TEMPTABLENAMES

WHILE @@ROWCOUNT <> 0
BEGIN
	EXEC ('DELETE FROM "' + @TableName + '" WHERE "TenantId"=''' + @TenantId + ''';');
	DELETE FROM #TEMPTABLENAMES WHERE TABLE_NAME = @TableName;
	SELECT TOP(1) @TableName = TABLE_NAME FROM #TEMPTABLENAMES;
END

Replication Troubleshooting

While replicating data from a source system, you may encounter various issues. Below are some issues we have come across and solutions for them.

Request to aggregation source timed out.

This indicates GPO cannot reach the target system, most likely due to firewall issues or the source MES Replication Service/IAS SM Essentials ConfigBridge is not running.

Shift XYZ has already been inserted as part of a previous changeset without being deleted first.

This error occurs when the last change GPO has recorded in the DB for this shift is an insert (ChangeType 0 in the AggData table). The usual cause for this is a source system that provides the data in an incorrect order (2 inserts, one after the other). In this case, the source system needs to be cleaned up, please contact our support inbox for assistance in this if you don’t know how to do this.

Occasionally, the cause can also be stale data that exists in GPO from a previous time that the line was replicated. This happened more often in the past when it was still possible to change the location ID of a line after replication had stopped (this was prevented with a patch in GPO around Q2 2025). GPO stores two location IDs for a line, the source location ID (used when fetching the data from a source system) and the target location ID (used when storing data in the GPO DB, to avoid conflicts between plants inside a tenant). Before the restriction was added to prevent the changing of a target location ID once replication had started, a line was replicated and then the target location ID was changed (no changes were made to the data replicated into the DB). Then the line was deleted and in theory all the associated data should be deleted as well. However, when deleting a line, we use the target location ID to find the relevant changelog entries and these still contain the original location ID so they weren’t deleted when the line was deleted. Then when we try to replicate the line again and come to a shift where a changelog entry already exists in the DB (which we check by comparing the ShiftUuids), we assume that this shift has already been inserted and throw the above error.

This may be resolved by cleaning up the orphaned entries in the changelog table. This can be achieved using the following script which deletes all entries from the DispatchRepl table which contain a location ID which can’t be:

Oracle & MSSQL

DELETE FROM "DispatchRepl" WHERE "TenantId" = '<tenant ID>'
                             AND "LocationId" NOT IN (SELECT "LineReplicationConfig"."TargetLocationId" FROM "LineReplicationConfig"
                                                      WHERE "LineReplicationConfig"."TenantId" = '<tenant ID>')
                             AND "ShiftUuid" NOT IN (SELECT "ShiftUuid" FROM "AggData" WHERE "AggData"."TenantId" = '<tenant ID>');

This will solve part of the problem. The second part is where entries already exist from a previous replication, the line was renamed before deletion and now the new replication uses the original location ID. For this we need to clean up on a line by line basis. First we’ll need the UUID of the conflicting shift (shown in the replication logs) and convert it to the Oracle RAW16 format if the DB is Oracle (one tool for this could be https://robobunny.com/cgi-bin/guid) as well as the target location ID of the line (found in the configuration of the line).

Then run the following script to clean up the corresponding entries in the shift data and changelog tables.

Oracle

delete
from "AggData"
where "ShiftUuid" in (select "ShiftUuid"
                      from "DispatchRepl"
                      where "LocationId" = '<target location ID in GPO>'
                        and "ChangeTime" >= (SELECT "ChangeTime"
                                             from "DispatchRepl"
                                             WHERE "TenantId" = '<tenant ID in GPO>'
                                               AND "ShiftUuid" = '<shift UUID>'
                                             ORDER BY "ChangeTime" DESC FETCH FIRST 1 ROW ONLY));
delete
from "DispatchRepl"
where "LocationId" = '<target location ID in GPO>'
  and "ChangeTime" >= (SELECT "ChangeTime"
                       from "DispatchRepl"
                       WHERE "TenantId" = '<tenant ID in GPO>'
                         AND "ShiftUuid" = '<shift UUID>'
                       ORDER BY "ChangeTime" DESC FETCH FIRST 1 ROW ONLY);