Working for years on performance issues – deadlocks, locks, etc., I began to ask myself the question – “Is it possible to find a universal solution to performance issues?”.
In this series of articles, I want to present my vision. This is not a panacea for all ills, but it may help somebody to solve issues.
In Business Central, we are trying to combine two kinds of systems – OLTP and OLAP.
Let’s give a short description OLTP and OLAP and how they differ.
Online Transaction Processing (OLTP)
The management of transactional data using computer systems is referred to as online transaction processing (OLTP).
OLTP systems record business interactions as they occur in the day-to-day operation of the organization, and support querying of this data to make inferences.
Another words, OLTP system is transactional system that provides executing a number of transactions concurrently and supports strong consistency for transactions using various locking strategies, such as pessimistic locking, to ensure that all data is strongly consistent within the context of the enterprise, for all users and processes. So, all kinds of posting (Sales Orders, Purchase Orders, Picks, etc.) are part of OLTP system.
Online analytical processing (OLAP) is a technology that organizes large business databases and supports complex analysis.
It can be used to perform complex analytical queries without negatively affecting transactional systems.
Online analytical processing (OLAP)
And here we are talking about reports. Not an invoice or warehouse receipt, but analytical reports that require a significant amount of data to be received.
Thus, we have a paradox.
For the speed of creating or changing records in transactions, we should have as few indexes as possible in the table, including SIFT indexes, which are indexed views physically.
At the same time, for effective reporting, we need indexes like air.
Therefore, it is necessary to find a balance, which is far from a trivial task.
What if we split OLTP and OLAP into two different databases?
The solution architecture can be represented in the following simplified scheme. In this example, we are talking about Business Central Database On-Premises or Azure SQL and Data Warehouse Azure SQL.
1. In Business Central database we configured Change Data Capture or Change Tracking to track data changes.
2. Using Azure Data Factory, we load data from BC database first into the staging tables, and then into dim and fact tables.
3. In Power BI, we configure shared datasets based on dim and fact tables.
4. Use shared datasets in our analytical reports.
Summary
This solution allows us to optimize the performance of our systems.
In the next article, I will take a detailed look at Change Data Capture and Change Tracking.
I’ll make a comparison and we’ll see how and where we can use them.
Next >>