ERP Software Logo1

Microsoft Dynamics vendors provide comparisons and opinions to professionals in the ERP/Accounting software selection process

 
 

Briware Solutions Inc.

How do I get historical data into my new system?


Email | Print

"I've had a consultant in here for 2 years now 'converting data'. Does that seem reasonable?"

This is a direct quote from a meeting that I was in today. And I wish that I could tell you that it was the first time I've heard something like this!

Whether you are talking Business Central, GP, CRM, or pretty much any other software implementation the subject of historical data comes up every single time.

What do you mean by "historical data"

During and implementation of software, there are three key types of data we care about:

Master Records are records that help us control transactions, such as customers, items, and vendors.  There are other types of master data, but those are the big three.  Generally there is a bit of a cleaning phase, and then all relevant master data is imported to your new system.  Depending on your software, there are usually a few options for this import, and your partner can likely guide you through them.

Opening balances are generally transactions that have not been settled in the old system.  These often include Accounts Receivable, Accounts Payable, Purchase Orders, and Sales Orders.  These transactions will need to be settled in the new system, so must be imported.  Again, there are several options for this import and your partner can likely cover them with you.  This category also includes the on hand quantities for inventory and the beginning balances for the General Ledger accounts.

Historical Data is generally transactions that have been fully closed in the old system.  For example, all of the quotes, orders, shipments, invoices, and payments - sometimes including the cancelled/voided records -  for every customer.  Ever.  Essentially, most people view this as taking all of the data from their old system and cramming it into their new one.

It is worth noting that the General Ledger has a partial exception here, with respect to history.  In order to provide comparative financial statements, we will usually import a beginning balance far enough back in time, and then import net change transactions per month from that date to the go live date.  We do not import details, only one entry per period.

Makes Sense - and an invoice is an invoice and a PO is a PO, right?

Sort of.

If your project team - in conjunction with your partner - have truly embraced the "implementation of a new system", then many of your business processes should have changed.  Item and customer numbers could be different, the quote to invoice process should be different, and the level of control required will be different.  It is entirely possible, in other words, that an invoice is no longer an invoice.

So, how does that impact my import?

If you want to truly import historical data, you would need to fit it into your new processes and configurations.  Essentially, you would need to recreate history and make it look like it had happened in the new system - but provide the same results you reported from the old system.  In many cases this simply isn't possible from a technological standpoint, but even when it is:

  • The process is incredibly time consuming and technical.
  • Which means that the project timeline and overall cost are dramatically inflated.
  • While the benefit very seldom outweighs the costs.

The last point is more important than it might sound.  As I've said, recreating history is a really big deal.  And it is only worthwhile if the historical data is actually used on a regular basis.  One of my consultants worked with a company who had a consultant come in for a whole year to work on scrubbing their historical data with them.  After that consultant left, however, nobody ever looked at that data again.  What a waste!

Fair enough.  But I can't possibly serve my customer if I don't have their complete history.

OK.

While I don't disagree, I would argue that you're still working in the 90's in your head if you think that's a valid argument for importing history.  The days of only being able to go to one spot for data are well past us.  Most reporting tools today are able to pull data from multiple datasources and either join or merge them as appropriate.  Even tools where this used to be difficult have come way forward (e.g. the addition of Power Query to Excel).

In many cases, end users can use these tools themselves without help from IT or external consultants.

What are you suggesting?

Instead of trying to force history into your new system, think about storing it somewhere else and accessing it only as required.  For example:

Leave it in the old system.  Many times we believe that once the old system is gone, it's gone forever.  In most cases this isn't true and users can go back to it as often as required for reference purposes.  Also, if the data is accessible from the old system and relatively easy to map as far as master records go, you could build reports that merge the old data with the new with fairly little effort.

Massage it and store it.  Instead of recreating the data, simply pull key data only, cleanse it, and store it somewhere else.  For example, if it is sales data you could export the invoice data, override the customer number, item number, and any other master ID required, then store the clean data in a spot where users can consume it through a reporting tool later on.  Some locations might include:

  • A SQL Server table if your ERP is On Premise.
  • An Excel workbook.
  • A SharePoint list.
  • A custom entity in Dynamics 365 for Sales (CRM).  This doesn't have to be CRM data!  At one customer, for example, we stored historical PO's in CRM simply for reporting purposes.

Implement a "Data Warehouse".  Essentially, this is similar to massage it and store it, but the destination is more defined.  In a data warehouse, you have the opportunity to merge both historical and go forward data in one data source, forming one version of the truth.  And a single source of truth is nirvana for CFO's and Data Nerds alike!

 

The truth is, there are a lot of things you can do with your data other than force it into a new system.  Before you convince yourself to follow an expensive and often unfulfilling path, take some time to review your options with your partner or your IT team.

If you would like to talk to one of our consultants about your data conversion choices during an implementation, please contact us at 844-BRIWARE or rod@briwaresolutions.com.

By Briware Solutions, www.briwaresolutions.com

Follow me on Twitter: @Briware_Rod

 

Comments are closed.