Archiving scenarios 

When it comes to configure Delmia Apriso MPI with Apriso MES with archiving process purging data from transactional data store, there are 2 possible scenarios:

• Setting RAP DI packages retention period and purging data from MES system using Archiving Framework
• Using MPI Dara Warehouse as archiving database

We will examine both scenarios closer in the next sections of this guide.

The subject is covered also in RAP DI help "Historical Data Updates and Deletes" in advanced topics section.

Using Archiving Framework with MPI 

In the scenario archiving graphs (processes) are executed along with MPI ETLs. Data from MES DB is purged according to archiving rules or transferred to archiving DB having the same schema and hosted usually in the same DB engine.

In order to properly configure archiving processes with MPI, "retention" period of ETL packages generated by RAP DI should be set.

Retention period is time range, when delete action on source database is considered as performed by user and should be reflected in DW. In other words during retention period, row removed from source system DB will have corresponding action in Data Warehouse of MPI. The rule is defined in the following way (refer to configuration guide in RAP DI help file):

IF (PackageExecutionTime - DW_TABLE.RowOnSourceSystemCreationDate) < RetentionPeriod then DELETE matching rows from DW

 Based on the way how deletes are used in a package, there are some limitations and configuration needed to handle it properly:

• Package (incremental) must be executed more often than the retention period as package execution time is used to detect which rows will be deleted, so if a package will be executed rarely, then deleted records on Source System will not be deleted from Data Warehouse,
• If Archiving is running on Source Systems, then it needs to be configured not to delete records which were created within the period defined in the package, as then they will also be deleted from Data Warehouse (which is invalid in this scenario).

Retention period should be configured in Advanced settings of RAP DI ETL package:

To configure source system delete row action:

1. Go to Delete Options group box and select the Support Deletes check box.
2. Use the Period field to specify the time for which data will be possible to be deleted.
3. Select the column that will be the base for counting the time set in the Period field. To do this, use the Retention column drop-down list.

Using MPI DW as archiving DB

This scenario considers treating MPI data warehouse as archiving DB.

In standard case data is dumped to archiving DB which has the same schema as MES DB with disabled indexing and integrity constraints. When MPI is in use, data warehouse could be used as storage for archived data. Data is transferred and transformed to DW schema, all transformations are done by ETLs created in RAP DI. To purge historical data and free MES DB resources, there is still a need to run archiving process that would remove records according to particular criteria. Also in this case ETL "retention period" has to be set to properly handle situation when data is removed from source system DB.

Reporting considerations 

Many times archiving database is used for reporting. Queries incorporated in reports have to operate on two databases to retrieve all data and give full picture of executed transactions.

First case is usually handled by SQL defined as union of two identical queries using MES and archiving DB.

In second scenario, because of different data warehouse schema, SQL will have bit different format. However in this case MPI cube and cube front-end (cube views, excel) might bring benefits, speeding up creation of reports and allowing ad-hoc analysis on top of historical and recent data.