We will transform your business

Reducing the amount of data in Dynamics NAV / Dynamics 365 Business Central

Introduction

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.

Why this is important?

There are many reasons why you may want to reduce the data in your NAV/BC database. The three most common are:

  • Wanting to reduce the size of your database
  • Having too much history and ‘old’ accounts, etc.
  • Wanting to improve performance
  • Keeping data complaint with GDPR requirements
  • Upgrading to Cloud (SaaS or Hosted) versions

Size

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.

History

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.

Performance

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.

GDPR

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

Upgrading or moving to SaaS

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.

The tools you can use

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).

Object ID – Name

95Date Compress VAT Entries5186Remove Contacts
97Date Compr. G/L Budget Entries5188Delete Tasks
98Date Compress General Ledger5189Delete Campaign Entries
99Delete Empty G/L Registers5190Delete Interaction Log Entries
198Date Compress Customer Ledger5191Delete Logged Segments
291Delete Invd Blnkt Sales Orders5196Remove Contacts – Refine
299Delete Invoiced Sales Orders5197Remove Contacts – Reduce
398Date Compress Vendor Ledger5693Delete Empty FA Registers
491Delete Invd Blnkt Purch Orders5695Delete Empty Insurance Reg.
499Delete Invoiced Purch. Orders5696Date Compress FA Ledger
510Change Log – Delete5697Date Compress Insurance Ledger
789Delete Phys. Inventory Ledger5698Date Compress Maint. Ledger
799Delete Empty Item Registers5755Delete Registered Whse. Docs.
1130Delete Cost Entries5914Delete Invoiced Service Orders
1139Delete Cost Budget Entries6002Delete Service Document Log
1141Delete Old Cost Entries6006Delete Service Email Queue
1198Date Compress Resource Ledger6010Delete Service Item Log
1199Delete Empty Res. Registers6034Remove Lines from Contract
1495Delete Check Ledger Entries6651Delete Invd Sales Ret. Orders
1498Date Compress Bank Acc. Ledger6661Delete Invd Purch. Ret. Orders
5172Delete Expired Sales Quotes7139Date Comp. Item Budget Entries
5173Del. Blanket Purch. Order Ver.7398Date Compress Whse. Entries
5175Del. Blanket Sales Order Ver.7399Delete Empty Whse. Registers
5177Delete Purchase Quote Versions99001041Delete Expired Components
5178Delete Purchase Order Versions99001041Delete Expired Components
5179Delete Sales Quote Versions
5180Delete Sales Order Versions
5182Delete 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.

You may also like