Live and Legacy Historical Reporting (Data Migration / Blending)

Owned by Joni Girardi
Last updated: Sept 19, 2023
3 min read

The Challenge

Many organizations must seamlessly analyze their live and historical data when moving to a new ERP. For instance, analyze a customer’s purchasing pattern and detailed marketing expenses across the years. The problem is that new ERPs will usually only have the organization’s master lists (e.g., customer and product), open documents (e.g., open AP and AR), and GL balances transferred from the legacy ERP. The legacy transaction history (e.g., invoices and GL transactions) is stuck in the legacy ERP.

Data Warehousing to the Rescue

Option 1: Reporting from Live & Legacy Data

Option 2: Seamless Reporting from Blended Live & Legacy Data

The following example blends a legacy transaction table with its equivalent table from the Live ERP. E.g., GL Transaction Detail.

Option 1 Description: Reporting from Live & Legacy Data

  • The legacy data is extracted into the data warehouse for long-term reporting and auditing purposes.

    • Extract all or a subset of the legacy data into the data warehouse. You might only need important tables for reporting purposes (usually 5% to 25% of all tables). It’s faster to copy all data (less labor now), but it’ll cost higher hosting fees to keep all data in the long run.

    • Once your legacy ERP is no longer operating, its raw data will be frozen in the data warehouse. This frozen data will reduce/eliminate the need to keep your legacy ERP system online in the long run for reporting and auditing purposes.

  • The data warehouse tables from the Live ERP are automatically synced up on schedule.

  • Building and running reports from the Live ERP and frozen data will be easy. For instance, analyzing side-by-side a customer sales report from the legacy ERP and a similar report from the live ERP. Remember that it’s likely that master lists might have changed between the ERPs and IDs, such as COA, customer IDs, and product IDs might not match.

Option 2 Description: Seamless Reporting from Blended Live & Legacy Data

  • The same as Option 1, but with some legacy transaction history blended with the transactions from the Live ERP. Building and running historical reporting will be seamless.

  • A desired legacy transaction table (e.g.: GL Transaction Detail or Invoice Transaction Detail) is duplicated and transformed to match the Live ERP’s equivalent table. The duplicated table will have the same column names and formatting as the one from the Live ERP:

    • Some columns only need simple changes such as column renaming and formatting. For example, date columns, and posted amount columns usually only need to be renamed.

    • Some columns require conversion IDs and/or further transformations. For example, convert old GL Accounts and groupings into new ones; or old Product IDs into new ones. This process often requires conversion tables and the correct logic to transform the legacy data.

    • Some old and new system columns might not have an equivalent match. The client must decide how these columns should be treated when the data is blended.

  • Union’ing the duplicated & transformed table with the equivalent live table.

  • In some cases, this process needs to be done in two transaction tables (e.g.: Invoice Transaction Header and Invoice Transaction Header Detail).

  • Using Quality Assurance reports to validate the union’ed tables meet the conversion requirements.

 

Keywords: Historical data blending, data migration tools, historical data from a legacy system, data archiving, data consolidation, Acumatica, Sage 100, Sage 300, Sage 500, Sage Intacct, Sage Pro, Sage X3, Microsoft Dynamics 365 (AX, GP, NAV, SL), NetSuite, Quickbooks, Quickbooks Online, CSV, MS Access, MS SQL Server, Oracle, DB2, Pervasive, Providex, MS FoxPro, PostgreSQL, MySQL.