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.
This post is about G/L (General Ledger) Budgets in Microsoft Dynamics 365 Business Central (D365BC). In particular, the post explores why G/L Budgets are useful and how exactly they can be set, used, updated and otherwise maintained.
First of all, it is worth mentioning that there are different Budgets in D365BC. For example, it is possible to create Budgets for Jobs (projects), Cost Budgets, etc. This post focuses on G/L Budgets as, perhaps, the most frequently used type of Budgets in the system. Such Budgets can be used, for example, to plan various expenses, revenue, etc. G/L Budgets can be created by department (e.g. expenses by department by month), product line and so on based on the Dimensions set up in the Company. Budgets can be set up for different legal entities (Companies). The budgeting periods can be months, quarters, years, etc. Moreover, it is possible to create multiple G/L Budgets within the same Company for the same period of time. This, for instance can be useful if multiple people work on G/L Budgets based on different departments, if multiple versions of the Budget need to be created (e.g. to be presented to the team) and in many other scenarios.
In order to be able to create and use G/L Budgets in D365BC, the following areas of the system must be already set up:
the Chart of Account (COA) – the G/L Accounts that are going to be used in the budgeting process;
the Dimensions that the G/L Budget is going to rely on.
In order to create a G/L Budget, search for the corresponding option in the system Search Box. Once on this Page, choose “+ New” and fill out the “Name” and “Description” Fields and the Dimensions that are going to be used in this Budget:
To get down to the Budget details, select the “Process” choice in the Ribbon and press the “Edit Budget” button. The next step is to attend to the “General” and “Filters” FastTabs as explained on the screenshot below:
In order to Export a G/L Budget to Excel or to import it from Excel, users need to select the “Budget” choice in the Ribbon and press the “Export to Excel” or “Import from Excel” buttons correspondingly. This feature can streamline the process of creating a G/L Budget as users can rely on Excel for formulas, text editing, etc.
TIP: It is usually a good idea to, at least, start setting up a G/L Budget in the system and only then to export it to Excel for further work. This is going to show how exactly the system structures the data in Excel so that Users can continue following that structure to successfully import it back to the system.
Once a G/L Budget has been created, it can be used for reporting to compare the Budget to the actuals. It is important to note that G/L Budgets can be created at any point of time (e.g. before any actuals are available, while they are being recorded in the system or after they have already been generated) and still be used as intended and without any issues in D365BC. In order to start using a G/L Budget in a report, navigate to the “Account Schedules” Page, select the desired Account Schedule, and press the “Overview” button in the Ribbon. Once on the “Overview” Page, the G/L Budget can be selected on the “Dimension Filter” FastTab as shown on the screenshot below.
TIP: It necessary to ensure that the Account Schedule Report Columns (and Lines) are set up to show the Budget, the actual, the variance and other amounts appropriately.
Here’s a video tutorial of the above:
This article and the related video have explored why G/L Budgets are useful and how to use them in D365BC.
At KMicro, we help companies make informed, efficient, and effective decisions related to information technology, such as enterprise resource planning (ERP) software, to attain desired business objectives. Our consultants analyze business processes to refine, optimize and automate them. We empower organizations and their hard-working teams to save time and money while increasing their positive impact on the world, whatever industry they are in. If you would like to discuss how technology can help attain your business objectives, contact us.
This post is about the key differences between Purchase Orders (POs) and Purchase Invoices (PIs) in Microsoft Dynamics 365 Business Central (D365BC). Although the discussion focuses on POs and PIs, similar principles are applicable to Sales Orders and Sales Invoices in the system. Which one to choose? How easy it is to use one vs the other? Why one is preferable over the other in some cases. These and other questions are answered in this post.
Before diving into the topic details, it is useful to define a PO and a PI in the context of D365BC:
A PO is a financial Document used to inform Vendors of the intended purchase of goods, services, assets, etc. Posting a PO usually results in two things: firstly, receiving goods and services (Posted Purchase Receipts) and secondly, recording the financial implications (Posted PI). These two do not have to happen simultaneously. For example, it is possible to receive goods and services on one date and to record the invoice from the vendor on a different date.
A PI is a financial Document used to record financial implications of accounts payable (AP) transaction OR to simultaneously record a receipt and the related financial implications – the keyword in the latter case is “simultaneously” (unlike a PO).
So, what are the differences between the two and which one to use in any given situation, which one is easier? To begin with, it is worth mentioning that POs and PIs are about equally easy to use. However, there are some differences requiring that one needs to be used over the other:
Partial receipts – as can be seen from the definitions above, if users need to be able to accommodate partial receipts (e.g. 21 chairs are ordered and are then received in increments of seven or so over a period of time), the PO functionality is the way to go as one PI can only be used to record one receipt which has to be invoiced at the same time.
Purchase Order Document – if users need to issue a document (e.g. to print or create a PDF) to request goods or services from vendors, the PO functionality is built for the purpose – multiple PO design formats are available out-of-the-box and can be easily modified even further. PIs can be printed (or exported to PDF) too, but they are going to read “Purchase Invoice” which may bewilder some vendors; moreover, the print format of PIs is likely usually requires a substantial redesign to be suitable for external stakeholders meaning more development and consulting hours if PIs are used.
Prepayments – there are situations when vendors require a prepayment (in full or partially). On the accounting side, the prepayment money should be posted to an asset G/L Account till the vendor’s invoice(s) are fully processed, at which point the money moves from the asset Account to other corresponding G/L Accounts. If this is the case, the PO functionality should be chosen as it fully accommodates and automates the process, unlike the PI functionality that lacks the Prepayment feature.
Converting from a Purchase Quote (PQ) – companies using the PQ functionality will find that it can be only converted to a PO which only then results in a PI which effectively means that even if POs are not necessary, users still have to come through the PO step if they convert from a PQ.
Blanket POs – in some industries, it is common to use Blanket POs which, again, immediately can only be converted to a PO.
The list above does not exhaust all the differences between POs and PIs, but focuses on the key ones to help users make an informed decision that is efficient and effective when it comes to purchasing Documents. It should be noted that both POs and PIs can be used by users within the same company, depending on what they need to process – it does not have to be one or the other. For example, it is possible to choose POs for receiving inventory (where partial receipts are expected) and PIs for recording simple transactions, such as utility bills.
In summary, this post has focused on the key differences between PO and PI functionality in D365BC. It has defined a PO and a PI in the system and has discussed when a PO is usually preferable over a PI. It has also been pointed out that both POs and PIs can be used within the same Company for different purposes.
Here’s a video tutorial of the above:
At KMicro, we help companies make informed, efficient, and effective decisions related to information technology, such as enterprise resource planning (ERP) software, to attain desired business objectives. Our consultants analyze business processes to refine, optimize and automate them. We empower organizations and their hard-working teams to save time and money while increasing their positive impact on the world, whatever industry they are in. If you would like to discuss how technology can help attain your business objectives, contact us.
This post is about Purchase Quotes (PQs) in Microsoft Dynamics 365 Business Central (D365BC). In particular, the post explores the rationale and the benefits that PQs bring about and which are sometimes overlooked by companies using D365BC.
First of all, what is a PQ in D365BC?
A PQ is the financial Document used as an optional initial step in creating a Purchase Order (PO).
As can be seen from the definition above, PQ is not mandatory in the purchasing process, however, deploying them in some contexts is useful. Below are the key reasons why some companies may find it helpful to use PQs.
Draft PO – some organizations have important processes structured around their POs. For example, the purchasing department may require the creation of draft POs that can be created in great quantities, including by junior team members, easily reviewed and modified before converting to actual POs. A function that fits the need best is the PQ.
Approvals – out-of-the-box approval Workflows in D365BC only allow approving a PO before it is posted, but NOT before it is printed (or, by extension, sent to a Vendor, including as a PDF file). Therefore, companies wishing to get an approval process in place BEFORE a PO is printed have two key options: use PQs OR create a custom approval (e.g. via an Extension or Power Automate). The approval Workflow involving PQs is a no-code user-friendly process and is, therefore, usually preferred. Therefore, companies wishing to approve POs before they are sent to Vendors use PQs.
Team Member license – another advantage of using PQs is the ability to save on license costs. What if a company (e.g. in professional services) has field workers who need to a) be able to use Time Sheets and b) initiate/request a purchase (e.g. to order parts)? The first requirement can be satisfied by the affordable Team Member license, but what about the second one? Only Users with a full (Essentials or Premium) license can generate POs. Interestingly, the solution to this can be PQs as the Team Member licensees can create PQs (which can be converted to POs). At the time of writing this article, the monthly price of an Essential license was $70 USD vs $8 USD for a Team Member license. Thus, a company that has 25 field workers can save $18,600.00 USD per year (($70.00 – $8.00) * 12 months * 25 Users) using PQs; if the company relies on Premium licenses ($100.00 USD per month), the savings are going to be $27.600.00 USD per year (($100.00 – $8.00) * 12 months * 25 Users). .
The list above does not exhaust all the different reasons why companies may choose to use PQs, but focuses on the key ones which are, perhaps, most popular. These help optimize the system, enhance the approval process and save on license costs.
Here’s a video tutorial of the above:
At KMicro, we help companies make informed, efficient, and effective decisions related to information technology, such as enterprise resource planning (ERP) software, to attain desired business objectives. Our consultants analyze business processes to refine, optimize and automate them. We empower organizations and their hard-working teams to save time and money while increasing their positive impact on the world, whatever industry they are in. If you would like to discuss how technology can help attain your business objectives, contact us.
