Live and Legacy Historical Reporting (Data Migration / Blending)
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. Note that master lists might have changed in the new ERP and IDs and Names/Descriptions such as for Chart of Accounts, Customer, Products, Salespeople, Vendors, etc might not match with the legacy data.
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.
Examples of DataSelf Reports with Blended Data
Grid Report Examples
Option 1: Two sections showing the live and legacy data side by side. The filters on the left control both reports simultaneously.
Option 2: The same report showing the data seamlessly blended.
Trend Report Example
Option 1: Two sections showing the live and legacy data side by side.
Option 2: Seamlessly analysis. Users don’t need to know the data comes from two different systems.
Demo Dashboard
Click here to visit our demo dashboard which shows the examples above.
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.