fbpx Skip to content

How to Future-Proof Your Business Systems with a Data Warehouse

insightsoftware -
November 2, 2020

insightsoftware is a global provider of reporting, analytics, and performance management solutions, empowering organizations to unlock business data and transform the way finance and data teams operate.

Data warehouse

Planning for an ERP system implementation can be a daunting undertaking. With the release of Microsoft’s latest ERP product version for small and midsize businesses, many of the company’s legacy GP, NAV, and SL customers are likely beginning to consider a move to the new platform.

Looking at documents together.

Microsoft Dynamics 365 Business Central (D365 BC) is the next evolution of the Microsoft Dynamics NAV code base, extended to a more cloud-friendly platform and more fully integrated with the rest of the Microsoft stack than ever before.

Regardless of which ERP system you are migrating from, the process of getting your data into a new system is never easy. There are some unique challenges associated with data migration. Interestingly, you can address many of them very effectively with a data warehouse.

While data warehouses were developed with a different purpose in mind (that is, for pre-staging data to perform sophisticated analytics), they can provide significant value in an ERP migration, so much so, in fact, that an entire data warehouse solution can pay for itself from the cost savings achieved out of the migration process alone.

Data Migration Challenges

One of the first questions that a project team must answer about data migration is “Which data are we going to bring over from the old system to the new one?” The initial answer, very often, is “everything.”

But “everything” is rarely a viable choice. First of all, many companies have accumulated quite a lot of historical data. The process of exporting the data, filtering them, cleansing them, and reformatting them for the new system is time-consuming and costly. Is it that important to be able to look up an invoice or a payment recorded 10 years ago? Probably not. What about one from last year? Probably yes.

Then there are the particular challenges of matching so-called “many to many” transactions. It’s one thing to bring over a list of customer payments and the list of customer invoices. It’s a much more complicated matter to recreate the history, showing which payments were applied to which invoices in which amounts. That’s an exceedingly ugly data migration task, which is why most companies performing an ERP migration don’t end up doing it.

There are some very important reasons why you might want to bring some of your historical data into your new system, though. For example, it would be useful to retain the capability of reporting historical sales trends. Although you might not care about an invoice detail that is decades old, you might like to see how your sales trends have changed over time. And you’ll need visibility to some of your historical ERP data for tax and compliance purposes.

Many companies take a pragmatic approach to this problem. They bring over master records such as customers, vendors, general ledger accounts, and so on. They bring over summary GL numbers for one or two years, plus the latest year’s general ledger detail. Finally, they bring over open transactions such as unpaid customer receivables, open accounts payable, purchase orders, etc.

Even that is a pretty big project, but it leaves many finance and accounting organizations feeling like they have settled for a compromise. The alternate approach is to keep the old system up and running. In the event you have to look up an old sales order, the old system is always there as a resource for looking at historical information…until it isn’t.

Problems eventually make it difficult to access the old system. The software was installed on an ancient machine, and the hard drive crashed. Or the one person in the company who knew how to maintain it has left. Or an operating system update conflicts with the old ERP software, and it no longer functions properly. If you want to ensure that it keeps working, you can continue paying support and maintenance on the old ERP software, but that can get very expensive.

The Data Warehouse Solution

Now consider an alternative that does not occur to most ERP system managers: A data warehouse with data from your old ERP system that provides all the information you need for historical reference. After all, you don’t need to process transactions on the old system. You just need to be able to look things up.

If you compare the cost of an off-the-shelf data warehouse solution for ERP to the cost of paying continued maintenance on your old ERP system, the data warehouse will win every time. Not only does it solve the problem of historical ERP data, but it also serves an ongoing purpose. It substantially reduces risk.

By normalizing data from your old ERP system with data from the new system, you can report against the entire continuum of history and well into the future as if all that information came from a single system. In other words, even if the data models were different, you can harmonize them within the data warehouse, providing an “apples to apples” view of your ERP data across the years. With the data warehouse, you can get the end result that you want without the effort, expense, and risk associated with a more complicated data migration scenario.

Pre-Staging Migration Data in the Data Warehouse

There is another benefit. By establishing a common data model for your old and new ERP systems within the data warehouse, you can use the data warehouse itself as a migration tool. You can use the process of harmonizing data between the two platforms preemptively to cleanse and normalize data from the old system and prepare them for migration to the new system.

This is a novel approach to the challenge of data migration, one that is not typically proposed because most people think of a data warehouse strictly as a staging area for reports.

Hand holding up a globe.

ERP Migration without Rewriting Reports?

Jet Analytics is a reporting product from insightsoftware that works with the entire family of Microsoft Dynamics products, including Microsoft Dynamics CRM, AX (Axapta), NAV (Navision), GP (Great Plains), SL (Solomon), BC, and Microsoft Dynamics 365 Finance & Supply Chain Management (D365 F&SCM).

The connection between Jet Analytics and the various Microsoft Dynamics products works automatically; you can extract data from the ERP system (or Microsoft CRM), which is mapped to a harmonized data model within the data warehouse. As a result, customer records from Microsoft Dynamics GP will look very much like customer records from Microsoft Dynamics Business Central in the data warehouse. Invoice records from the different systems will also look similar, and so on.

In fact, if you have built reports for your old ERP system using Jet Analytics, there is a good chance that they will continue to work seamlessly with data from your new Microsoft Dynamics ERP system, without any modification. In the case of the migration from Microsoft Dynamics NAV to Microsoft Dynamics 365 BC, the likelihood of all reports running properly without any modification is close to 100 percent.

If you are planning a migration, especially from one of Microsoft’s legacy ERP products to D365 BC or D365 F&SCM, you could save yourself thousands of dollars (or even tens of thousands) in implementation costs by using a combination of these approaches.

In summary, here’s what a Jet Analytics data warehouse can offer:

  • By connecting the Jet Analytics product to your old system, you can automate the extraction and transformation of the data that you plan to migrate, staging them in the data warehouse in preparation for import to the new system.
  • Using Jet Analytics, you can solve the problem of historical data, providing limitless visibility to information that is too difficult and/or expensive to migrate. This reduces risk, saves money, and increases productivity.
  • If you have developed reports for your old Microsoft Dynamics ERP system using Jet Analytics, you can continue to use those reports for data from your new Microsoft Dynamics ERP system with little or no modification. That saves substantial time and money on your implementation.
  • Post migration you end up with the most robust BI and reporting platform on the market, eliminating any future reporting bottlenecks.

A Holistic View across Time

In addition to those advantages, this Jet Analytics data warehouse approach allows you to see both old and new data together, as a unified whole. If you want to run comparative reports that look back over multiple years, Jet Analytics enables you to do that. The data warehouse organizes and presents information as if it came from a single system.

Most ERP system implementations involve some level of compromise. One of those compromises is the necessity of making a “clean break with the past.” The Jet Analytics data warehouse approach effectively does away with that problem.

Why Jet Analytics Makes Sense Now

Jet Analytics can be up and running in mere hours, not days or weeks like many of its competitors.

Even if you are not ready to upgrade to one of the latest versions of Microsoft’s ERP systems, it still makes sense to get started with Jet Analytics now. By establishing the Jet Analytics data warehouse as your system of record today, you can achieve several key advantages:

  • Implementing Jet Analytics in advance of an ERP system upgrade allows you to get a head start, lighten the eventual implementation workload, and reduce risk. Report development on Jet Analytics will continue to pay dividends when you eventually implement a new ERP system.
  • By familiarizing yourself and your team with the data warehouse environment, you’ll gain an early understanding of the potential of using the Jet Analytics data warehouse to achieve the benefits described in this article.

To learn more about how Jet Analytics can help your organization, download our Jet Solutions brochure, built for Microsoft Dynamics today.