Data Analysis Expressions, or DAX, is the foundation of Power BI. It is a robust language that lets us create interactive reports and carry out intricate computations. Have you ever wondered what goes on behind the scenes when a DAX query is done, even though we frequently concentrate on creating effective DAX expressions? Gaining an understanding of how a DAX query is processed backend is essential for troubleshooting, speed optimization, and maximizing Power BI. Now, let’s explore in detail the intriguing path of a DAX query!
Two Engines at Work: The Foundation of DAX
In Power BI, two crucial elements work together to process and return results from DAX queries effectively. They are:
1. The Formula Engine (FE)
The Formula Engine is in charge of processing query logic, organizing computations, and evaluating expressions. In order to optimize a query for execution, it is essential to break it down into a logical plan. Parsing and interpreting DAX queries is one of the Formula Engine’s primary duties.
Managing iterators and row-by-row processes, streamlining query logic to increase efficiency, controlling intermediate computations & expression evaluations are also some of the duties of Formula Engine. However, there is no direct interaction between data storage and the Formula Engine. It asks the Storage Engine to retrieve the required data before finishing its calculations if it is not available in memory.
2. The Storage Engine (SE)
Data is retrieved from the underlying data source by the Storage Engine. It has two modes of operation:
- Vertipaq Engine: The VertiPaq Engine is utilized for imported data and offers extremely quick query performance by utilizing highly compressed in-memory columnar storage. The highly efficient format used for data storage makes retrieval and aggregation faster.
- DirectQuery/Composite Model Engine: The DirectQuery/Composite Model Engine is used to retrieve data in real time by creating SQL queries (or other query languages) that retrieve data straight from the database. Because DirectQuery receives new data on-demand rather than using cached data like VertiPaq does, it is helpful in situations where up-to-date information is needed.
The Storage Engine is an essential part of DAX execution since its effectiveness in data retrieval has a direct impact on query performance.
How Does a DAX Query Get Processed?
A DAX query is executed in a methodical manner. Here is a detailed breakdown:
- Query Submission: The Formula Engine takes over when a DAX query is run, either through DAX Studio or in a report visual.
- Parsing and Optimization: To generate a query plan, the Formula Engine parses the query, divides it into more manageable, smaller steps, and simplifies the logic.
- Data Request to Storage Engine: The Formula Engine notifies the Storage Engine with the desired data if it is not already in memory.
- In DirectQuery mode, the Storage Engine generates a SQL query to retrieve real-time data, or it can retrieve the necessary data from the VertiPaq cache for imported data.
- Computation and Execution: The Formula Engine provides the final result by applying the required DAX expressions and computations once the Storage Engine provides the data.
- Aggregation and Output: After being combined and structured, the calculated results are sent back to the query output or report visual.
Example: Calculating Total Sales by Gender
Let’s understand this process with a classic DAX query example:
Total Sales =
CALCULATE(
SUM(‘Sales’[Total Sales]),
‘Customer’[Gender] = “M”
)
This is how the internal execution of this query looks:
- The Formula Engine receives and analyzes the query logic.
- It makes the expression more efficient and asks the Storage Engine for the information it needs.
- If VertiPaq is being used, the Storage Engine fetches sales data from memory; if DirectQuery is being used, it queries the database.
- The Formula Engine receives the data that has been retrieved, applies the gender filter, and does the SUM operation.
- The report aggregates and presents the ultimate outcome.
This example highlights how the Storage Engine ensures effective data retrieval, while the Formula Engine focuses on calculations.
Conclusion
Our capacity to debug, optimize, and create better-performing queries is improved when we comprehend how a DAX query is executed backend. Power BI strikes a mix between speed and functionality by using the Formula Engine to handle computations and the Storage Engine to ensure effective data retrieval. By following best practices and improving query patterns, you can fully utilize Power BI and DAX for data analysis.
Addend Analytics is a leading Power BI consulting services provider and Microsoft Power BI partner based in Mumbai, India. In addition to Power BI implementations, we specialize in providing end-to-end solutions like Business Central with Power BI to unlock actionable insights. Our expertise also extends to Microsoft Fabric consulting, offering competitive Microsoft Fabric pricing to meet your business needs.
We have successfully delivered Power BI for Manufacturing industry, with real-time Power BI manufacturing dashboards. Having successfully completed over 100 projects across industries such as financial services, banking, insurance, retail, sales, real estate, logistics, and healthcare. Whether you’re exploring Business Central implementation cost or seeking advanced data analytics, Addend Analytics is here to help. Get a free consultation now by emailing us at kamal.sharma@addendanalytics.com.