This post continues exploring the question of how much storage space a transaction takes in Microsoft Dynamics 365 Business Central (D365BC) database (DB). The methodological foundation has already been laid in the “Microsoft Dynamics 365 Business Central: How much space is used by 1M transactions (Sales Invoice)” post. Therefore, this article omits the methodological details and instead focuses on the information pertinent to this experiment addressing Journal transactions.
A General Journal transaction for the purpose of this study is defined as a General Ledger (G/L) Account to G/L Account entry that is balanced on the same line and contains the applicable Dimensions, but is not set to automatically calculate any sales taxes. Unlike, for example, a Sales Invoice, such a transaction does not have Item or Customer Ledger Entries; neither does it have any shipment-related entries.
Below is a screenshot of the General Journal Batch prepared for this experiment:
Figure 1: The General Journal Batch
The Batch in question consisted of one thousand (1K) Lines such as the ones shown in Figure 1 above. This Batch was posted in the experimental Company (“2022.02.22-Test”) and the results were compared to the control Company (“2022.02.22-Control”). The differences in the data set sizes between the two Companies can be viewed on the “Data Administration” Page:
Figure 2: The “Data Administration” Page
Figure 2 above shows the data size in KB in the Companies under investigation: 31520 (control) and 32552 (experimental) making it possible to calculate the difference: a) the difference between the data size in the control and experimental Companies, based on 1K records: 32552 – 31520 = 1032 (KB); b) the difference in GB: 1032 / 1048576 ≈ 0.001 (GB); c) the storage used by 1M records: 0.001 * 1000 = 1 (GB).
The 1M / year transaction rate is approximately 4K transactions per business day (based on 255 working days per year). Medium-sized businesses may rarely reach this transactional volume. However, those that do reach it are unlikely to run out of the base application storage within next several years as the limit is set to 80 GB by Microsoft (Microsoft, 2022) (based on the Cloud, also known as “SaaS” – software as a service hosted version of D365BC). Nevertheless, there are certain practices that can either make the storage space get used relatively fast or, on the contrary help substantially extend the period of time required to use the base storage space. KMicro specialists help businesses with best practices around D365BC storage space and beyond. If you have any questions related to D365BC, contact us.
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: The “Preview” function used on the “Sales Invoice” Page
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: The Sales Invoices for import
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: Importing the Invoices
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: The imported Sales Invoices
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: The “Table Information” Page
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: The “Data Administration” Page
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.
