Archiving in M3 Business Engine

Introduction

Keeping an ERP database as small as possible has many benefits in a cloud environment. Relevant and up-to-date data are located immediately, overall performance is increased and, most notably, the cost of storage is reduced. Because of these benefits, it is an important topic in an organization to know how long data should be kept in the ERP database and what should be kept in other storage solutions.

Archiving in M3 Business Engine (M3 BE) supports the movement of archived data from the ERP database, through the archive schema, and to Infor Data Fabric where the data storage is less costly but accessible. You can also export the archived data from Infor Data Fabric to other data storages.
Note: All archiving programs require authority set up, otherwise they are not allowed to run. Some archive programs in M3 BE only delete data instead of moving it to an archive.

If an organization has not worked with archiving before, it is typically a new process that must be implemented within the organization and within the ERP.

Important questions to answer include:

  • How long must or should the data be kept in the ERP? What are the different legal requirements in the countries where our company operates?
  • How much data do we have? What processes produce the most data, and what options do we have to archive this data?
  • What kind of data do we have and how long is it required? If data that has been archived is required in the daily operations, how can our users access it?
  • What is the quality of data that we have? For example, do we have old orders with an open status that cannot be archived?
  • How often should we run the archiving? Can this be solved within our own organization, or do we need external help?

Most of the questions above require some effort to answer. We recommend starting a project within the organization first to answer these questions before starting to run an archive. Also, since archiving is an irreversible process, it is important to perform tests in a test environment before you run archiving in the production environment.

Archiving capabilities in M3 BE

M3 BE Archiving is centered around the Transactional Archiving Management (TAM) module, while the data is archived in specific archiving programs aimed to archive data for that specific process. There is, for example, a separate archiving program for customer orders and another one for purchase orders. Each archive program knows what related tables should be archived including the selection of that data. All archiving programs follow the same archiving framework in M3 BE so that common features, such as logging, date validations, and archive viewers to see archived records in the archive schema, are managed in the same way. Some archiving programs also support other features such as pausing and restarting an archive job and viewing which records were excluded after a run.

All archiving programs can be found in 'Archiving. Open Toolbox' (AMS100) and tables that can be archived are found in 'Table Analyzer. Open' (AMS050). We are continuously adding new archiving programs to help customers reduce the size of their M3 database. The Experience Designer App, 'Archiving Workbench' also combines all these programs to help throughout the archiving process.

Archiving Workbench

The Experience Designer App, Archiving Workbench aims to connect all programs in the archiving process in place. The app is intended for users running the archiving and provides an overview of the data that can be archived or deleted.

These main tabs are available in the app, Overview and Work With Archive Program. In the Overview tab, you can view information about all the available archive programs and the size of the tables connected to these archiving programs. In the Work With Archive Program tab, you can learn about a single archive program. Selecting an archiving program enables us to see archive runs, records excluded from the last run, archive settings, what data can be moved to Data Lake from the archive schema, and which tables are connected to the archiving program.

Initial archive setup

The archiving framework contains metadata that might be generated in M3 BE.

You must follow these steps:

  1. Ensure that number series type 43 E is defined in 'Number Series. Open' (CRS165).
  2. In 'Settings – Filing' (CRS799), ensure that 'Fr library' is set to MVXJDTA and 'To library' is set to MVXARCH.
  3. In (AMS100), use action F14= 'Standard' to ensure that all archive programs are up to date.
  4. In (AMS050), use action F14='Generate' to get the latest table sizes. This depends on the standard generation that has been done in (AMS100).

Environment investigation

To get the most out of the archiving, it is important to perform an initial environment investigation to know about the current data situation. Since archiving is an irreversible process, you must know exactly what data must be archived before doing the archiving. If your organization has never done archiving in M3 before, a thorough environment analysis is recommended. The initial environment investigation is split into four steps:
  1. Database analysis
  2. Review of database analysis
  3. General clean up
  4. Archive data analysis

Database analysis

The aim of this first step is for you to understand what data you have in your ERP database tables. You have to get a deeper understanding of what data the organization has, and which archiving programs should be used.

These types of tables could be subject to archiving:
  • Transaction data tables

    Tables that grow when a transaction is made like an order. These tables typically have the largest size and are subject to archiving.

  • Master data tables

    Tables that contain master data. These tables might not be as large as transaction data tables but could still grow to substantial sizes. Some master data, for example item master, has archiving programs.

  • Work files

    M3 BE sometimes uses certain system technical tables called work files to work with specific jobs. These tables are normally empty if no such job is processing. However, in some circumstances, these tables can contain several inaccurate data that should be removed.

The size of the tables that have a connected archiving program can be found in the Archive app in the overview tab, Table size. In this tab, you can view the tables and archive programs that have the largest size. Also, a graph showing the top 20 archive programs in terms of size exists. To view all tables that can be archived, click the 'View all tables' link to open in (AMS050). Start working on the archiving programs that have the largest size.

Some questions to investigate can be:
  • Are some tables abnormally large? If so, what is the reason behind this?
  • Do some tables, that should be empty, contain data? Review the size of the work files.
  • For the tables that should be archived, from what year is the data coming from? This information can be considered input for how much data should be saved after each run.
Note: For (AMS050) to work properly, first use action F14='Standard' in (AMS100) and then action F14='Generate' in (AMS050).

To analyze the tables further, other tools within M3 can help answer these types of questions. The API EXPORTMI (Data Export) finds records using SQL-like queries and you can also define custom lists to analyze the data in 'Information Browser Category. Open' (CMS010).

Review analysis

With the insights from the database analysis, some decisions must be made before continuing the environment analysis and starting with archiving.
  • How should financial transactions, such as ledgers, be archived?

    Normally, different legal requirements apply on how long ledgers are stored. In audit cases, for example, how can the archived data be extracted in a compliant manner? Normally the archiving program 'General Ledger. File' (GLS800) can be an archiving project of its own due to the complexity and severeness.

  • For every archive program, what should be the archiving policy?

    In M3 BE, almost every archive program has its own archive policy defined in (AMS100) or in the Settings tab in the archive app. This is the number of months that must be kept for that specific archive program, otherwise, the archiving cannot be submitted

    For example, if today is the 5th of February 2023 and the archiving program has 3 months defined as the archiving policy, then archiving cannot be performed on records after 1st of November 2022.

    We recommend keeping the same archiving policy as much as possible within these groups for consistency reasons. However, some data have little or no business value after being fully processed. Normally the archiving programs can be divided into two or more groups, 'frequent' and 'not frequent'. For example, batch order data can be put in a 'frequent' group that has an archiving policy of 3 months, while the actual orders that are grouped as 'not frequent' can have 60 months. For financial archive programs, we recommend keeping full financial years rather than months. In these cases, set the archive policy dates to keep at least as many financial years as required during any month of the year to prevent users from accidentally archiving too much.

  • For each archive program, how often should the archiving be run?
    Depending on the archiving policy and the size of the affected tables, decide if the archiving program should be run yearly, quarterly, or monthly. Typically, the larger the size and lower archiving policy, the more frequent the archiving program should be run. Examples of archiving programs that are run frequently include:
    • 'Ad Hoc Report Run. Delete' (AHS900)
    • 'Batch Customer Order. File Transferred' (OIS080)
    • 'Purchase Order Batch. File' (PPS945)
    • 'Invoice BOD Data. Archive/Delete' (CMS590)

General clean up

After the Database Analyze step, a general cleanup might be necessary for once. You might have to clean up irrelevant records in work tables that might have been created by mistake. These records can be cleared and identified using 'Work Table. Open' (AMS400). Inspect the size of tables related to (AMS400) using (AMS050) or the Archiving Workbench to see if any table is abnormally large.
Note: (AMS400) requires setting the 'Arch policy 1' to at least '1' in (AMS100). This reduces the risk of deleting records that belong to an active job.

Archive data analysis

The final step of environment analysis is to get a more detailed understanding of the selected archiving programs and the data quality of the tables for that archive program. It is important to identify problems early before archiving in production.

Excluded records

In this step, the final analysis is performed for the selected archive programs to be run. Before starting with archiving, you must know which records are excluded if the archiving program were to run.

Records are excluded from archiving due to various reasons. For example, the archiving program for customer orders has a hard selection and the lowest status must be in status '05', '77', '79', '90', or '99'. This means that if an old customer order has the lowest status '66', it will never be archived until the customer order has changed status to one that is accepted by the archiving program.

To understand what hard selections are set for a specific archiving program, see information about M3 BE functions using transaction archiving in the M3 Business Engine User Documentation Library (Cloud).

Some archiving programs also log which records are excluded and why. The excluded records are found in 'Archiving Error Log. Open' (AMS350) or can be accessed in the Excluded records tab in the 'Archiving Workbench'. Custom Lists that are defined in 'Information Browser Category. Open' (CMS010) and EXPORTMI (Data export) can also be used to analyze which records have been or will be excluded for an archiving run.

Run plan

We recommend establishing a plan to serve as a guide for the user running the archiving programs and identify which other tasks might be needed. The plan can include these items for the relevant archiving programs:
  • Run sequence.
  • Archive policy fields and what date selections to use.
  • Archive statistics.
  • Identify the number of excluded records.
  • Use actions for the next archive run. For example, clean excluded records.

After the archive runs, you can communicate the result of the archiving to relevant stakeholders in the organization.

Run sequence

Some archiving programs have dependencies on one another and therefore should run in a specific order. For example, archive program 'Int Deliveries btw Div. Archive/Delete' (MFS290) requires that the originating reference order is archived before the internal deliveries. The run order for the archiving program is found in the archiving app overview tab, Archive Programs.

The run sequence is important because it decreases the frequency at which the archiving is run. If archiving is run regularly, missed records caused by the dependencies to other archive programs are decreased as dependent data is archived faster. For example, 'Int Deliveries btw Div. Archive/Delete' (MFS290) depends on 'Req/Distr Order. File' (MMS185). (MMS185) however, depends on 'CO Invoice. File' (OIS095) to have run before it. If these three archive programs are run in an incorrect order and are only run once per year, (MFS290) can potentially miss two years of records due to dependencies. If these archive programs were to run each month, however, (MFS290) would only miss two months of data instead.

Archive statistics

All archive programs can log how many records were processed for the archiving run. Logging is enabled per archive program in (AMS100) or in the Settings tab when working with archive programs in the Archiving Workbench. The statistics for each run are found in 'Archiving Log. Open' (AMS300) or in the Archive Runs tab in the archiving app.

Also, consider the number of excluded records or, in other words, the records that should have been archived but were not. Some programs log which records were not included in the latest archiving run in (AMS350), but the other programs require a separate analysis.

Archiving in tenants with several companies

Some tenants, normally development and test tenants, can have more than one company. If a tenant has several companies, it is also likely that the tenant contains more data so archiving becomes even more important. Almost all archive programs are run in the company context, so the user must switch companies and run archiving in each company to ensure that the data in all companies are removed.

Related topics