
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: