WIXLIB

Considerations for Data Migrationin Dynamics 365 Finance & Operations

Table of Contentsi01Data Migration for Companies Migrating from a Legacy ERP02Data Defined03Data Migration for Your D365 Implementation04Historical Transactional Data04Why You Shouldn’t Migrate It to D36505Addressing Reporting Goals05Migrate balance and net change activity into D365.05Simple mashup within Power BI.06Report directly from BYOD.06BYOD as a source for a data warehouse.07The New World Orderalithya.com

Data Migration for Companies Migrating from aLegacy ERPHarvesting enterprise data is central to how organizations compete, and even survive,as industries transform digitally. Yet, as companies merge and technologies shift,managing data has become an extremely complex and critical task, especiallyhandled alongside of an enterprise ERP implementation.For companies moving from an on-premise legacy ERP system to Microsoft’s cloudbased Dynamics 365 for Finance and Operations (“D365”), there are some uniquechallenges and new tools to leverage when considering the data migration activity.Microsoft delivers the Data Management Framework (“DMF”) tool to assist customerswith data migration for D365. Data migration itself consists of three distinct activities,as illustrated below: Data extraction (from legacy systems), data transformation anddata import into D365. DMF assists with the import of data into the newD365 application. But what is the best way to extract and transform the data from alegacy environment to prepare it for input to the DMF tool?THIS WHITE PAPER ADDRESSES APPROACHES FOR EXTRACTION ANDTRANSFORMATION OF DATA FROM LEGACY SYSTEMS, AS WELL ASSTRATEGIES FOR ADDRESSING SEAMLESS REPORTING OF TRANSACTIONALDATA (I.E., SALES) COMBINING THE NEW D365 DATA WITH LEGACY DATA AFTERSYSTEM GO-LIVE.1alithya.com

Data DefinedFor the purpose of this paper, we will classify data into these three categories: masterdata, transactional data or opening balances.Master data spans the dimensionality of an organization’s business functions andconsists of information about a person, entity or object. For example, in the sales,marketing and customer service functions, master data can consist of customernumbers, contact info, service codes, warranty information and distribution details. Inthe finance function, master data might include GL accounts, cost centers,department codes and company hierarchies.Master data can be very detailed. For example, a master vendor record contains notonly general information such as a vendor’s name and address, but also specificinformation, such as payment terms and delivery instructions. Master data remainssomewhat constant over time and is really the core data about your company, whichforms the basis of an enterprise-wide “system of record” for the business.Transactional data are all the unique business events that occur in the day-to-dayoperations of a business, such as a shipped order or a movement of inventory.Transactional data will consist of “facts” like quantities shipped, amounts invoiced andhours worked, and be associated with the master data dimensions. The volume oftransactions grows exponentially each year and can easily add up to millions ofrecords. Transactional data can be either closed historical transactions or opentransactions.Opening balances are essentially a rollup of transactions to a point in time. Forexample, inventory starts at zero and all the inventory transactions ( /-) sum to anending balance, which becomes the opening balance for a new period. If historicaltransactions are left behind, one will need to import an opening balance in the newERP system for assets and liabilities (i.e., inventory).Master Data CustomersVendorsItemsBill of materialsTransactional DataHistorical Sales Orders (invoiced) Sales Invoices (paid) Customer Payments (AR) Vendor Payments (AP) Purchase Orders (received)Open Open Sales Orders Open Sales Invoices Open Purchase Orders Open Vendor InvoicesOpening Balances Inventory G/L BalancesMaster data, “open” transactional data and open balances need to find their way intoD365, either programmatically, or manually, depending on the total number ofrecords and the need for automation. Migrating historical transactional data into D365is generally discouraged.2alithya.com

Data Migration for Your D365 ImplementationData living in a legacy ERP system will be structured very differently than datarequired for D365. It is not a simple mapping from one system to another. Microsoftsimplifies the import process by providing the Data Management Framework (“DMF”)tool.The migration of legacy data into D365 will primarily focus on the master data, suchas customers, vendors, items, etc. The DMF tool helps to easily import data into D365that will then proliferate to all the underlying D365 tables associated with thatparticular data set. That said, it does require a customer provide the export, cleansingand transformation of its own legacy data into a standard Excel-based format,provided by Microsoft, as defined by the D365 entity structure. Once in that format,DMF picks it up and properly loads it into D365.The process of legacy data extract and transform (the “ET” of “ETL”) typically falls tothe customer, who is more familiar with their own legacy data. This process can bepainstaking and tedious, and prone to error if not completed in a systematic andrepeatable way. Customers will likely use Microsoft SSIS to accomplish this task, orconsider a SQL-based automation tool like Alithya’s Accelerator to streamline thislegacy data extract and transform activity.Alithya’s Accelerator provides SQL-based ETL automation fully leveraging theMicrosoft stack. With Alithya’s tool, this data extract and transformation process is50-75% more efficient than writing SSIS the traditional way, and offers additionallong-term benefits (see “addressing reporting goals” below).“ALITHYA’S ACCELERATOR PROVIDES ETL AUTOMATION FULLY LEVERAGINGTHE MICROSOFT STACK.”3alithya.comalithya.com

Historical Transactional DataWHY YOU SHOULDN’T MIGRATE IT TO D365So what about the transactional data? Migrating historical transactional data canpresent a major problem when considering a move from a legacy ERP system to amore modern ERP system with its new data structures, posting methodologies andworkflows.The reality is that all those facts, events and transactions, living in their legacy format,requires harmonization and transformation before they can be plugged back into anew ERP system. In addition, many transactions will require further processing(i.e., posting), and the very process of posting transforms the data further, making iteven more challenging to get the various data sets to synchronize into a functionalwhole. Next, consider that many companies have millions of historical transactions, sothe chance for error is significant. It is unlikely all the errors would be identified andcorrected during the ERP data validation phase, which means that companies willcontinue to “pay the price” for data anomalies long after their ERP go-live.For this reason, most experienced ERP implementation partners advise customers tonot migrate transaction history from legacy applications to a modern ERP system.With the D365 cloud pricing model, Microsoft further discourages this migration bycharging customers for the extra storage required, making this an even moreexpensive proposition.For customers who are upgrading from Dynamics AX 2009 to D365, it should benoted that the data schema is dramatically different. Table structures werere-architected and have ballooned in number—from 1,800 tables to over10,000 tables. That, along with the many functional changes delivered by Microsoft inthe Dynamics product, results in having to execute a “re-implementation” vs an“upgrade” from AX 2009 to D365. Migrating AX 2009 historical transactional datashould be avoided.For customers who are upgrading from Dynamics AX 2012 R3, the data schema isclosely aligned, and a data upgrade is certainly possible. A review on the overarchingreporting and BI strategy would be warranted to determine if the increased cost andeffort are worth it.Migrating historical transactional data will result in increased project complexity, costand risk. Most commonly, the reason for migrating the data tends to be for reportingpurposes and there are certainly better ways to address this critical business need.We will explore this further next.4alithya.comalithya.com

Addressing Reporting GoalsEvery business understandably wants to keep legacy data and preserve its integrity.After all, a company may have spent decades compiling it, as it’s the only way toshow trends and reveal patterns to help guide the way forward. Legacy data isvaluable.For businesses moving to D365, there are a couple options--besides migratingtransactional data into D365--to achieve post implementation reporting thatcombines legacy and new D365 data. Each of these options is described andillustrated on the next page:1MIGRATE BALANCE AND NET CHANGE ACTIVITY INTO D365.This approach is most commonly used to address financial reporting requirements. Itinvolves moving general ledger account opening balances and periodic net changesinto D365. This approach provides a valuable capability to drive year-over-yearfinancial reporting. Since we are not migrating volumes of historical transactionaldata, it is limited in that drill down to the transactions themselves would not bepossible.2SIMPLE MASHUP WITHIN POWER BI.Microsoft delivers embedded Power BI with its D365 application. If the organizationupgrades to Power BI Pro, it is possible to do some limited data mashup within PowerBI itself. The challenge with this approach lies in the budget for additional Power BIPro licenses, plus the challenge with transforming and harmonizing data across theaggregate D365 entities and detailed legacy data. Further, this method implies thatPower BI would be the only option for reporting. So although possible, it is not alwayspractical or desired.5alithya.com

3 REPORT DIRECTLY FROM BYOD.The BYOD is an export of the standard data entities into an Azure SQL data store.Think of the BYOD as an operational data store that provides full SQL access toD365 data. Although it is possible to pull legacy data into the BYOD for reporting, theBYOD behaves more like a staging database than a true modeled data warehousethat will allow for meaningful reporting and analytics.4 BYOD AS A SOURCE FOR A DATA WAREHOUSE.In this case, the BYOD is used specifically for D365 data, in order to leverage the fullpower of SQL for data transformation and harmonization. SQL SSIS ETL is used to pulldata from the external data source (i.e., legacy environment), and then transformedand harmonized into a governed data set specifically designed to support theorganization’s reporting and analytics needs. If this approach is taken, Alithya’sAccelerator can greatly help expedite this capability and provide a tool that can beleveraged for long-term enterprise reporting and analytics. If the tool is used formigration of master data, as described above, much of that work can be leveraged onthe back side to ensure harmonization of data between old and new ERP systems.6alithya.comalithya.com