In March 2021, Microsoft released a preview for the archival of inventory transactions. The feature is useful for customers having a lot of inventory transactions. To prevent having performance issues when querying inventory transactions or using them in calculations for summary, Microsoft now made the option to archive inventory transactions. I spent some time reading the documentation and trying out the solution. In this post, I will share my findings related to testing the feature and what exactly is happening under the hood.
Archive inventory transactions feature
Let’s start with sharing the resources I have read; some before and some after I tried to use the feature in my demo environment. The feature is announced by Microsoft in the Dynamics 365 2021 release wave 1 plan: Enterprise-scale inventory performance improvements and archiving – Dynamics 365 Release Plan | Microsoft Docs.
This topic gives a brief description which caught my attention to look out for the preview and I added a test on my bucket list.
The second source of information was coming from the Microsoft Documentation site. Archive inventory transactions – Supply Chain Management | Dynamics 365 | Microsoft Docs
Actually, I just tried to enable the feature and did not read this documentation first. Of course, I got blocked as there are important topics to consider and there are some pre-requisites. I do encourage you to read the documentation if you haven’t done yet. It is mentioning pre-requisites like closing ledger periods and the inventory, but also side-effects when you archive the transactions. When archived, you can’t perform certain tasks, unless you reverse the archival. Before you start using the feature, check if there are reports in use which might be impacted when the original inventory transactions are not available anymore. Possibly some of the existing reports can be moved to Power BI where the data will be sourced from a data warehouse which will then still have the details after you archive the transactions.
When you want to try the Inventory transactions archive feature yourself, be aware that after enabling the feature, you can’t disable it anymore in your environment. So choose carefully in which environment you want to test it. If you have a database backup of your test environment, you can restore the backup to get back to the state of before enabling this feature.
After enabling the feature, I was impatient and wanted to run the archival directly. The Contoso demo environment has transactions in 2016 (USMF company) which might be a perfect candidate to archive. Actually, I got errors on all pre-validations. The first check was about not having an inventory close performed. After closing the inventory, I had to close the ledger periods. Here I used the Excel add-in to quickly close the periods for 2016 in the USMF company. During the publishing, I got additional errors that not all accounting distributions were posted. Also, there were open ledger journals.
I will elaborate a bit more on the accounting distributions. It might be the case that you can’t quickly find the not-posted documents. In my case, I have been blunt and deleted records from the table AccountingEvent directly. Of course, this might have an impact and I don’t want to encourage you to do this in a customer environment.
For not posted journals, I used the table browser to find out which journals were not posted and blocked me from closing the periods. You can do this yourself by altering the URL. In the parameter setting, you can use ?cmp=USMF&mi=SysTableBrowser&tablename=LedgerJournalTrans. You can replace USMF with another company ID. To filter on only the not posted lines, you can use the Advanced search. Then add the table LedgerJournalTable and add a range on the Posted field. Note that also periodic journals are included in the validation before you can close a ledger period. You might have missed posting the periodic journals, so the validation is helpful here.
It took some time, but finally, I managed to meet the pre-validation checks.
Inventory transactions archive
To start the archive process, you first have to decide for which period you want to run the archival. Do you want to run it for example for a full year or per month? This would depend on the number of transactions you have in the environment. It is recommended to run the inventory transactions archive job outside of your business hours. When the load exceeds the available time, you can split the archiving into smaller chunks or pause a currently running job.
To start the inventory transactions archive job, go to Inventory Management > Periodic Tasks > Clean up > Inventory transactions archive. You can specify a date range and via the recurrence, you can schedule when exactly the batch job should run. When the job is running, the status is In Progress. When required, you can use the option Pause archiving to give system resources to other tasks.
After the completion of the job, you can review the archived transactions.
Inquiring inventory transactions
We were quite used to check detailed inventory transactions in the past. After archiving the inventory transaction, I checked the details for a purchase order posted in 2016. The transactions were not directly available, but there is an informational message that you might find detailed in the archived transactions.
Now I had seen the result, I didn’t stop my research. Is the on-hand information still accurate? What will happen when you run the consistency check and start repairing the on-hand from inventory transactions? This all is working correctly. The reason is that all deleted inventory transactions will be replaced with summary records for issues and receipts separately containing a total quantity and physical and financial amounts.
Another question would be: Are all inventory transactions within the selected period archived? The answer here is: “No!”. After running the archival, there might be transactions in the period which are not closed. When you for example purchased goods that were not sold yet, the purchase inventory transaction is not closed and will remain to support the settlement part of the inventory close feature.
There is more…
I did some additional checks and have some technical details to share with you. All deleted inventory transactions are copied to a new table called InventTransArchive. So actually, the InventTrans table will shrink in database size when doing the archival, but there is not a real database size gain as the records are moved to another table. A copy is required to ensure you can reverse the inventory transaction archival in case you need this.
Next to the InventTrans table, there are other related tables in the database, like: InventTransOrigin, InventSettlement, InventTransPosting. The fact is that records in these tables are not archived at all. Some of them might be required to keep for reconciliation or the impact of archiving these tables are not clear yet.
One last thought to share. Per item and inventory dimension summarized records are created as a replacement in the InventTrans table. For this reason, a company would gain the most if there are a lot of transactions per item to be archived. When you have a huge number of products and/or variants and only a few records per combination, the performance effect is minimal. This is also the case for serialized items.
This feature will be very useful for us in future. Thanks for sharing your thoughts.
Appreciate you exploring and documenting this for us. Keep the content coming!