Microsoft Dynamics 365 Business Central: How much space is used by 1M transactions (Sales Invoices)

Introduction

With Microsoft Dynamics 365 Business Central (D365BC) hosted on the Cloud (also known as “SaaS” – software as a service), the base storage space is limited to 80 GB (Microsoft, 2022). Different types of data count towards the limit, including the combinations of all the Environments (Production and Sandbox), file attachments* therein and transactional records. This means that companies that have a substantial number of records in their enterprise resource planning (ERP) system database (DB) potentially may run out of the base storage space fairly quickly. In order to determine how quickly this may happen depends, among other factors, on how much space a transaction takes in the DB. Therefore, this post seeks to answer this question.

Research question

How much space is used by 1M (one million) transactions in D365BC?

Although, data in D365BC can be compressed, this study is not interested in this function (such a feature, however, can be applied in a real-life scenario to help reduce the DB size). Moreover, it is possible to rely on certain programming tools to help answer the question; the design of this experiment, however, demands to rely exclusively on the tools readily available to end users who are thus empowered to reproduce this experiment or modify it to make it even more relevant to their business context.

Methodology

In order to conduct the experiment, the following steps have been taken:
1) copy a Company from any existing functioning Company – this is the control Company which is going to be used to compare to the one containing the transactions;
2) make a copy of the Company copied in step one – this is the experimental Company in which the transactions are going to be posted;
3) create a Sales Invoice in the Company established in step 2 – for the sake of this experiment, a transaction is defined as a Posted Sales Invoice containing one Line of Type “Item” (of Item Type “Inventory”) and with the applicable sales taxes and related Dimensions (if you are interested in how much DB space is used by Journal transactions, see our “Microsoft Dynamics 365 Business Central: How much space is used by 1M transactions (General Journals)” post);
4) export (via a Configuration Package) the Sales Invoice created in step 3;
5) copy the Sales Invoice in the Excel file and import it back into the system;
6) post the resulting 1K Sales Invoices and compare the results with the control Company;
7) calculate the storage space required for 1M records.

Results

Figure 1 above shows the Sales Invoice created in the experimental Company. Note the usage of the applicable Sales Taxes and Dimensions.

Figure 2 shows the tentative result of posting the Sales Invoice that has been created above. It is interesting to note that two entries are created for the applicable Sales Taxes, namely the applicable GST and PST; moreover, since the transaction involves an Item, the corresponding Entries are also created for the General Ledger (G/L) and Item Ledger; finally, Customer Ledger Entries are created because this is an accounts receivable (AR) transaction.

Before the transaction is posted, a Configuration Package is used to export the Sales Invoice. Tables, namely “Sales Header” and “Sales Invoice”. Furthermore, validation of certain Fields in both these Tables has been disabled to ensure successful import of the data back into the system via Excel.

Figure 3 above shows additional Sales Invoices created in the exported Excel file to bring the total number of Sales Invoices to one thousand (1K).

Figure 4 above shows the successful import of 1K Sales Invoices. Note that the screenshot shows 2K records as two Tables mentioned above (1K in each one) are used.

Figure 5 above shows posting of the imported 1K Sales Invoices. The “Post Batch” function is used.

It is now possible to compare the changes to the data set in the two Companies under investigations, “2022.02.02-Control” (control) and “2022.02.02-Test” (experimental). There are several Tables that are impacted by the posted transactions, including the “G/L Entry” and “Cust. Ledger Entry” Tables:

Figure 6 above shows the difference between the data sets in the control and experimental Companies. There are other Tables impacted by this Batch, including “Item Ledger Entry”, “G/L Register”, “Item Register”, “Sales Shipment Header”, “Sales Shipment Line”, “Sales Invoice Header”, “Sales Invoice Line” and “G/L Entry – VAT Entry Link”.

The total difference between the data sets in the control and experimental Companies can be viewed in the “Data Administration” Table:

Figure 7 above shows the data size in KB in the Companies under investigation: 31520 (control) and 42568 (experimental). It is now possible to calculate the difference:
a) the difference between the data size in the control and experimental Companies, based on 1K records: 42568 – 31520 = 11048 (KB);
b) the difference in GB: 11048 / 1048576 ≈ 0.0105 (GB);
c) the storage used by 1M records: 0.0105 * 1000 = 10.5 (GB).

Conclusions

The experiment conducted in this study shows that 1M Posted Sales Invoices in D365BC use 10.5 GB of storage space. This means that if a business posts 1M transactions yearly, several years are going to pass before this business should need additional storage. Medium-sized businesses posting only about 25500 transactions yearly (100 transactions every business day (255)) are going to consume approximately 0.27 GB (11048 / 1000 / 1048576 * 2500 ≈ 0.27 GB) of their storage space. This means that it is going to take about 296 years (80 / 0.27) before they need additional storage space as far as their transactional volume goes.

In order to calculate the exact time when additional storage may be required depends on a number of additional, business-specific factors, including base application DB size, number of Environments (Production and Sandbox) and Companies created, the quantity of licenses purchased, average number of Sales Invoice Lines and Sales Credit Memos typically required per 1K Sales Invoices, etc. The objective of this research to identify how much space is taken by 1M Posted Sales Invoices has been successfully achieved. By analogy and to a certain extent, the results can be applied to similar Posted Purchase (Vendors’) Invoices. Thus, this experiment can be of use to businesses processing AR and AP transactions to help estimate how much time it is going to take them to attain a point at which additional storage space may be required.

Based on the results discussed above, an average business processing around 100 transactions daily, is unlikely to require additional space in the nearest future. There are, however, additional factors, features and best practices in connection with data storage that KMicro specialists are knowledgeable about experienced with. Therefore, our team looks forward to helping businesses with their specific situation in connection with data storage and other D365BC-related questions.

• It is worth mentioning that exceeding the storage space does not prevent users from accessing the system or transacting in it; it does, however, prevent certain operations, such as creation of new Environments. Moreover, adding more licenses increases the base data storage limit. In addition to this, companies that still need more space can purchase it from Microsoft (via a Partner). Finally, there are also different best practices that prevent from reaching the storage limit, including integrating with SharePoint.

References

Microsoft Documents (Microsoft) (2022). Retrieved August 22, 2022, from https://docs.microsoft.com/en-us/dynamics365/business-central/dev-itpro/administration/tenant-admin-center-capacity