Finance | Project Operations, Human Resources, ...

Import all historical transactions

(0) Share

Report

Posted on by Chris Milikouris

Our data migration will need all historical transactions to be imported on a line by line basis (we want the ability to interrogate for transactions in prior years). Is this possible? If so how can this be done?

Thanks

Chris

I have the same question (0)

All responses (12)

Answers (0)

Shyam Mani 331 on at

Like (0)

Report

RE: Import all historical transactions

Hi Guys,

Thanks for the informative posts.

We have a similar requirement, we have got some 20 years of Purchase history of Customers from non-Microsoft POS, with details like which products they bought, how many quantities and all other details typical of a Customer purchase transaction.

We are moving to D365FO, and have communicated to User that it's a bad idea to migrate these transactions to D365FO.

We have decided to build a centralized place for reports, analytics etc., basically migrating all historical customer transactions to Azure Data Lake as well as recent transactions (D365->BYOD(Azure SQL DB)->Azure Data Lake).

From Azure Data Lake, PowerBI or Analytics team can fetch data and create whatever reports, feed algorithms they want.

So far its looks fine, but our Users insist on a single place for Store staffs to enquire and have a look at Customer's past purchases (atleast 2 years in past).

We are thinking of creating a button at POS screen that will pick the CustomerAccount as parameter and open up PowerBI URL to that Customer's report.

Can someone let me know if it's feasible or not? or had seen similar requirement.

Any advice or new ideas are more than welcome! Thanks in advance!

Was this reply helpful? Yes No
Chris Milikouris 35 on at

Like (1)

Report

RE: Import all historical transactions

Thanks Junaid

There is a lot of explanation here and it is all appreciated. I think I am persuaded that a different solution is required!

Chris

Was this reply helpful? Yes No
Chris Milikouris 35 on at

Like (0)

Report

RE: Import all historical transactions

Thanks for responding Nikolaos

This wasn't something we had considered and may well be worth investigating.

We are going to be transferring in excess of 50 companies. Unfortunately the chart of accounts layout is not consistent for each company so this may end up being a realistic cost effective solution. From testing it looks like export to Excel will be our best option as the history is maintained there.

Was this reply helpful? Yes No
Chris Milikouris 35 on at

Like (0)

Report

RE: Import all historical transactions

Thanks André

It looks like we will need to keep almost all of the historical transactions separate.

Now intending on using Excel read-only files.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

RE: Import all historical transactions

In addition to what others have already said, you could host your old database somewhere and build some integrations for querying the important data from D365.

Was this reply helpful? Yes No
Suggested answer

Junaid Idrees on at

Like (1)

Report

RE: Import all historical transactions

Hi Chris Milikouris,

Thanks for sharing the details and scope of your historial transaction.

I learned many lessons in past while migrating transactions from legacy systems but Let me share with you my recent experience. We migrated last 4 years GL transactions(Expenses and cash entries) along with only OPEN customers/vendor document/invoices from AR and AP modules. And honeslty speaking, it's become our nightmare to reconcile the balances. Actually on real ground, it's not only about migrating data from any data management tool but the most critical element is financial reconciliation of this data which is time consuming task and at the same time to ensure and verify many check points. Who will make sure that that provided data is correct.

If i tell you on high level, you have to put following check points:

1. Verify Customer/Vendor ID

2. Verify invoice amount

3. Verify currency

4. Verify Exchange rates

5. Verify Account string (Main account + All required financial dimension)

6. Verify discount

7. Verify payment references (Like check numbers/Wire/ACH references)

8. Verify tax amount/percentages

9. Total count of invoices/payment in each month to match with legacy system

If you only missed one financial dimension or used wrong GL account mistakenly, an entire reconciliation will be out and you will have to spend number of hours to find such GAPs. I'm giving your very simple example.

And even After migrating historical transactions, you have to make sure your Vendor/Customer aging reports closing balance is matcing with GL ledger. In addition to that you have to ensure all vendors and customers Invoices are properly settled with payment transaction otheriwse you will find discrepancy for individual customer and vendor balances. You have to run year end closing for all years one by one to bring the correct opening balance. You have to make sure your Asset rollforward report is showing correct values or not. There is a bunch amount of work to be done so eventually my recommendation also would be not to go with this route as Andre and Ludwig mentioned.

Was this reply helpful? Yes No
André Arnaud de Cal... 298,570 Super User 2025 Season 2 on at

Like (1)

Report

RE: Import all historical transactions

Hi Chris,

It would also be against my recommendation to convert all historical details. The data import export framework is designed to import setup, master data and open orders and transactions. It is not capable of having a full history with traceability from a ledger transaction to the original order.

I can understand the need for history on construction assets. I had a similar requirement in the past with heavy vehicles. Then we imported only the maintenance history and details which were important for maintenance and warranties.

Was this reply helpful? Yes No
Chris Milikouris 35 on at

Like (1)

Report

RE: Import all historical transactions

Thanks Ludwig.

This is helpful. I am mindful I want to have all data in one place but as you point out there is a large time cost.

Chris

Was this reply helpful? Yes No
Chris Milikouris 35 on at

Like (0)

Report

RE: Import all historical transactions

Hi Juanid

Thanks for responding.

1. We do not make use of orders in our businesses so do not need this functionality. However, we would like invoices and payments (both sales and purchases) to be transferred. Additionally, is it possible to import the history on the general ledger?

2. We are going back in some cases 20 years. This is a long time, but because we manage construction assets, the transactions are sometimes required to be investigated some years later.

3. We are able to create full transaction listings within Excel - we currently use Sage Line 50. I don't believe there is an option for integration here.

I know we have the option of keeping the historical transactions in Excel on a read-only database so that these are not lost. However there are two concerns I have with this.

1. There is a higher risk of duplicate payments because an invoice will be raised pre- and post-migration, and this is not picked up.

2. We may want to report against the data pre-migration and this will be easier if all data is in one place.

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (1)

Report

RE: Import all historical transactions

Hello Chris,

Based on previous experience I would say that this is possible but a bad idea.

That is because transferring all historical transactions from a former / different ERP system requires a lot of work; not only to bring over the old transactions but also recording transactions exactly the same way as they have been recorded in your old system.

In other words, you have to think how to bring over 'mistakes' / corrections and things for which you possibly used workarounds in your old system.

Those workarounds might not be available in D365FO and you have to find a solution for that.

What will be difficult and time consuming as well is bringing over historical settlements of old invoices that have already been paid.

Also here a lot of work can be involved in this step.

Unless you are forced to by law bringing over old and historical data, I would recommend not doing that.

Note: Transferring actual and open balances is different. That's what you have to do.

Best regards,

Ludwig

Was this reply helpful? Yes No