Cbiz Blogs

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

Written by Theresa Masters | Aug 11, 2020 2:22:25 PM

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

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.