As more people are upgrading to the cloud and also for compliance and speed reasons, we are going to mention how you can reduce the size of your database and the volume of transactions via the built-in tools within Dynamics 365 Business Central (which are the same for Dynamics NAV).
This article is the first of a series that will explain how we have achieved this and the difference it has made to customer systems.
There are many reasons why you may want to reduce the data in your NAV/BC database. The three most common are:
Maintaining a large database which just keeps growing and therefore causing more significant backups each time especially when the standard practice is to run test companies and systems alongside Live/Production.
The data in your system goes back 7+ years which are no longer required and therefore just taking up unnecessary room leading to keeping blocked G/L Accounts, Customers, Vendors, etc. which won’t be used again.
Large numbers of records can impact the speed at which pages and reports load. An excellent example of this is the changelog table which, when activated and if not maintained, can grow very fast and therefore can be challenging to extract and filter.
Also, running “Copy Company” to test a posting or a new idea becomes something to schedule due to the length of time tables are locked during this process.
One of the requirements of GDPR is to not keep records longer than necessary / legally required. For example, the restrictions in place for how long you should keep customer contact details in your system
When upgrading, it is an excellent time to clear out the old and reduce the amount of data to be updated or moved. This will also increase the speed of the process, which will reduce the amount of time your system would need to be offline.
For some it makes upgrades, and SaaS moves possible, rather than having to reimplement due to the time taken for upgrade routines to run.
Dynamics 365 Business Central (Dynamics NAV) has provided several tools to assist in the management of your data.
These tools cover routines (reports) to compress data or delete data from G/L to Inventory. Below shows the list of those (which you can access via Object Designer in Dynamics BC/NAV).
| 95 | Date Compress VAT Entries | 5186 | Remove Contacts |
| 97 | Date Compr. G/L Budget Entries | 5188 | Delete Tasks |
| 98 | Date Compress General Ledger | 5189 | Delete Campaign Entries |
| 99 | Delete Empty G/L Registers | 5190 | Delete Interaction Log Entries |
| 198 | Date Compress Customer Ledger | 5191 | Delete Logged Segments |
| 291 | Delete Invd Blnkt Sales Orders | 5196 | Remove Contacts – Refine |
| 299 | Delete Invoiced Sales Orders | 5197 | Remove Contacts – Reduce |
| 398 | Date Compress Vendor Ledger | 5693 | Delete Empty FA Registers |
| 491 | Delete Invd Blnkt Purch Orders | 5695 | Delete Empty Insurance Reg. |
| 499 | Delete Invoiced Purch. Orders | 5696 | Date Compress FA Ledger |
| 510 | Change Log – Delete | 5697 | Date Compress Insurance Ledger |
| 789 | Delete Phys. Inventory Ledger | 5698 | Date Compress Maint. Ledger |
| 799 | Delete Empty Item Registers | 5755 | Delete Registered Whse. Docs. |
| 1130 | Delete Cost Entries | 5914 | Delete Invoiced Service Orders |
| 1139 | Delete Cost Budget Entries | 6002 | Delete Service Document Log |
| 1141 | Delete Old Cost Entries | 6006 | Delete Service Email Queue |
| 1198 | Date Compress Resource Ledger | 6010 | Delete Service Item Log |
| 1199 | Delete Empty Res. Registers | 6034 | Remove Lines from Contract |
| 1495 | Delete Check Ledger Entries | 6651 | Delete Invd Sales Ret. Orders |
| 1498 | Date Compress Bank Acc. Ledger | 6661 | Delete Invd Purch. Ret. Orders |
| 5172 | Delete Expired Sales Quotes | 7139 | Date Comp. Item Budget Entries |
| 5173 | Del. Blanket Purch. Order Ver. | 7398 | Date Compress Whse. Entries |
| 5175 | Del. Blanket Sales Order Ver. | 7399 | Delete Empty Whse. Registers |
| 5177 | Delete Purchase Quote Versions | 99001041 | Delete Expired Components |
| 5178 | Delete Purchase Order Versions | 99001041 | Delete Expired Components |
| 5179 | Delete Sales Quote Versions | ||
| 5180 | Delete Sales Order Versions | ||
| 5182 | Delete Opportunities |
The data compression routines replace the entries with new summary lines instead significantly reducing the number of records held by Dynamics. The number of lines recreated all depends on the options you select and can be run for different date period summaries, day, week, month, or year.
The important note is the data compression keeps the integrity of the reports which use a running balance, e.g. the trial balance. The summary lines will always add up to the value of the lines removed.
If, however, you do not summarise by Dimension, then the integrity of dimension report would be lost on balances. This is the same if you close your income statement without closing by dimension. This isn’t always an issue when working with historical data, as most companies change how they use dimensions as they progress with the system.
You should take a backup of your system before running these reports and also think of the amount of time it may take to run these. Our standard practice is to take a copy of the live system and the reports in this controlled system where we can time how long items run for. That way we know if we change/run multiple routines in 1 evening/weekend or if we even need to break it down into numerous periods for the bigger tables. We also assess the data using PowerBI.