Historical Data migration
First of all many thanks to André Arnaud de Calavon and all other respondents for taking time to respond to this thread. I really appreciate this.
André Arnaud de Calavon wrote that-
I will provide my two cents here as well. Despite you mentioned you don't want to have a reply disputing the "why", some thoughts should be taken in to account about "why not". You haven't mentioned details about the requirements. I do hope you have taken all aspects into account.
Sha12 Response:
Our requirements is that our business activities require looking at the historical transactional data for many of our operations. E.g. We have a customer (master data) brought over into D365. They have an order of $200K (e.g.) and our AR dept need to take a look at their payment history to understand how they have paid to us over the last 5 years based on their order history and payments. Another example – From Payables side – We would like to see a history of existing historical purchase orders in order to determined if we would continue doing business with them or not. Our Purchase orders sometimes remain open for multiple months / years as the contract is a multi-year contract. In short, our business processes and operations need historical data. We would not want to ask our business users to change all of their processes / operations because D365 cannot provide them with the data an ERP system generally provides.
Someone can argue that we should just get all data in BYOD or some datalake. That option would require our users to use reports for any historical data they need. It is really cumbersome to keep looking at reports in a different system from the main (ERP) system. We must try putting ourselves into the shoes of business users and how their day to day is going. I understand that it is “easy” to not import. But just because it is “easy” doesn’t mean it’s right for business users. Reports for reviewing transaction details is really not a good solution. Reports are good for listing and/or analytics, but it cannot produce the details in the manner regular D365 (or any ERP) can show in its native flow.
Since reports is not a viable option, the other option may be to create D365 like screens either on PowerPlatform or somewhere else. This option would create “tremendous” work to re-produce every transaction details screen. Think of how many different types of transactions are – e.g. Sales order headers, sales order lines, discounts/surcharges on each line, details about why a particular discount was applied (or why not), details of packing slips, shipments, sales tax details etc. Then these lines are interfaces into Accounts Receivables (AR). AR has one or more invoice headers for a given order and each invoice headers has its corresponding invoice lines along with tax details, invoices lines have GL distributions (at the subledger level), invoice lines have revenue recognition schedules and details. Coming onto Invoice payments has customer payments, payment applications to invoices (full/partial), it may have adjustments, it may have write-offs. All these details are seamlessly available on native ERP screens. To “re-create” all of these from a different datasource ( datalake or BYOD) is a huge effort. And these are just 1-2 modules I described here. Similarly there can be many more like expense reimbursements data, purchasing data (requisitions, purchase orders, AP invoices, etc. and so on…)
My question is – Why a “so called” modern ERP (D365) would not allow a customer to have the historical data as per their needs? Why it wants to change how customers should operate their business?
André Arnaud de Calavon wrote that-
first of all would require really amazes me as t
Why do I challenge you? There are multiple reasons.
The Dynamics 365 gives you a certain database size. Additional capacity will be additional costs where Data Lake storage is cheaper. However, building the reports requires a certain effort. I don't know your number of transactions. That would determine what would be better in your situation.
Sha12 Response:
We will get the adequate size storage as per needs. This is not an issue.
Data management framework is intended to work with master data, reference data and opening balances via journals. You mentioned about invoices and "their corresponding lines". What are these lines about? Do you need revenue on the level of ledger accounts only or do you need product details? Product details would require importing sales and purchase orders and manually post them or create a customization for it.
Sha12 Response:
These invoice lines are about what the customer is invoiced for. Think of it as a similar to sales order line, but only with quantity that is shipped/served. We would need revenue at the invoice line level. And thus it would come at the ledger level too based on transaction revenue accounting.
André Arnaud de Calavon wrote that-
You can import all historical GL, AP, AR and Fixed assets transactions into journals and post them to have the full history and database consistency. It would require validations if the conversion would be correct, but this option would not include settlements. That should be done manually or with a certain customization.
Sha12 Response:
Any data migration would require validation and conversion. This goes without saying. Why would settlements be not included? As I mentioned in my post, most ERPs would allow importing transactions (for end-to-end data flow with their appropriate data entities) from another ERP. These responses further cement my observation that D365 is a very light ERP system and could not do simple end-to-end data migration activities.
André Arnaud de Calavon wrote that-
Pointed out by Ramit. Revaluations. If you have unrealized AP or AR revaluations, with just moving over via journals, the details are not known on the AP and AR transactions, so settling the transaction might give wrong exchange rate difference calculations.
To have all information correct and consistent, it would be almost like you would need to recreate all transactions as happened in the previous application. Even if you would move opening balances only, you might need to look after correct currency revaluations and status of various transactions. However, the manual work is less with just opening balances compared to all full history.
Sha12 Response:
Why do we need to “recreate”? D365 simply needs to import transactions along with their subledger accounting entries as they happened in the source system.
André Arnaud de Calavon wrote that-
I have been involved in many conversions. Once, the customer insisted to import all details like you mentioned above. They decided to import data into the transaction tables directly without journals or order entry. As I mentioned it would be too risky and error-prone, they put me aside for the data migration and asked developers to do it. As a result (luckily found during UAT before they went live):
Sha12 Response:
Why would this be risky if we import every entity for all of its touch points? We would like to load journal entries (I assume it meant subledger accounting entries and GL accounting entries) referencing to imported transactions. There are no details as to “why” it would be risky if we do complete data load.
Remember that complete dataload is going to be needed one way or the other. Think of a situation where a customer is going live with D365 in Jun-XXXX. If we only load “open” transactions in D365 and nothing for the first half that year, that means the entire finance audit process would be required to look at two systems. Think how much inconvenient it would be for business users. So efforts required to load multiple years of data is not a direct proportion of number of years of historical data. It may be some/little increamental efforts. WE have done several such transactions in Oracle EBS system and we say this based on experience.
I understand that a lot of “consulting” mentality is to treat ERP implementation as “a project” and then quickly move on to some other project. (Not directed at any individual.) But I would like all of us to think about business users and put ourselves into their shoes. ERPs are meant for easier operations of day-to-day activities. We have to do all we can to make lives easy for our business users. And we should be ready to do the “right things” and not just some “easy” stuff because it is “easy” to implement.
1) Audit trail was not working correctly
2) Reconciliation reports did show wrong numbers
3) Inventory costing was not working correct
4) New asset depreciations did show incorrect numbers
5) Registered vendor invoices could not be picked up for further processing
6) Tax transactions had wrong amounts due to misunderstanding of the data model
and more....
After that, they did apologize and went for a new conversion having opening transactions only and kept the old environment for some historical inquiries.
Sha12 Response: -- Any conversion and data migration done in right way with validations applied and importing every necessary entity should not run into above situation. It can be multiple iterations, but migration itself is a iterative process. That's why we have mutltiple test enviroments before we get to the "Final" (Prod) environment where all thorought testing has been done, data tallied to make sure that data / numbers matche between the source and target system.
Moving back to your situation. When you mentioned you need 3-5 years of history, I wonder about the exact business requirement. If it is about reporting, then you can consider moving data to data lake or BYOD and combine data on e.g., Power BI reports.
As I mentioned, reporting is really not a solution to this ask. Reports do not give you all the details about all different transactions to provide a comprehensive view in the similar manner as how an ERP native screen does.
I'm aware, there might be indeed other business requirements to have detailed history. E.g., with maintenance on assets, you might need to inquire the full history of such asset.
Even though you did not ask for this type of answer, I thought it is important to share my experiences and ensure you would not run in to a situation as I mentioned above.
For the data management framework, there is documentation available from Microsoft. You can start on the landing page: Data management overview - Finance & Operations | Dynamics 365 | Microsoft Learn. This will not tell in detail which entities to use. Some entities are obvious about the purpose. Others are known to people with experience.
