Numerous articles have been written on how to write or optimize DAX syntax in Power BI to create measures, calculated columns or tables. However, there is limited knowledge about the architecture of DAX and its background processes within Power BI. This blog post sheds light on the backend actions and steps involved every time you execute a DAX expression.
DAX formula and storage engines
DAX operates with two internal engines, the formula engine and storage engine, which work in tandem to compress and encode raw data and evaluate DAX queries. Understanding the inner workings of these engines will enhance your comprehension of how DAX functions and improve your technical skills.
The formula engine is responsible for receiving, interpreting and executing all DAX requests, while the storage engine is used to compress and encode raw data.
When a DAX query is executed, the formula engine processes the query and generates a list of logical steps called a query plan. This plan instructs the storage engine on the required data and how to retrieve it. After the query plan is generated, the storage engine receives and executes it, returning a data cache (a table of data) to the formula engine for evaluating the DAX query and producing a result.
Depending on the type of data connection used in Power BI, storage engines are classified into two categories: VertiPaq and direct query.
VertiPaq is employed for data stored in-memory when the import mode is implemented in Power BI. Direct query is used to read data directly from the source, primarily Azure, SAP or PostgreSQL.
The figure below illustrates a simple DAX query and its evaluation.
DAX data and storage types
Data and storage types are another crucial topic to address when discussing DAX and the behind-the-scenes processes in Power BI. DAX uses six different data types to store values in the storage engine
- Integer
- Decimal
- Currency
- DateTime
- Boolean
- String
For storage, Power BI employs two modes: import and direct query. We will focus on the import mode, which uses the VertiPaq columnar data structure. This structure allows you to store data in memory using individual columns, rather than rows or full tables, thereby enabling quick and efficient evaluation of DAX queries.
VertiPaq employs the following methods to compress and encode the imported data in Power BI, reducing in-memory storage and data model size:
- Value encoding: the VertiPaq engine identifies columns containing integer values and applies mathematical transformations to minimize the number of bits required for storage.
- Hash or dictionary coding: VertiPaq identifies distinct string values and creates a new table with indexes.
- Run length encoding (RLE): the VertiPaq engine uses RLE to reduce column size by replacing duplicate rows with a table that lists each distinct value and its instance count.
VertiPaq relationships
Another important aspect to consider is the existing relationships within the VertiPaq engine. VertiPaq can map relationships between columns in your data model, allowing it to evaluate complex and multi-column queries. This differs from implementing table relationships in your data model; instead, it involves mapping primary and foreign keys across related tables.
Here’s a demonstration of VertiPaq relationships using the same measures as in the previous example:
STEP 1: Search for USA in the Name dictionary to find the line number from the Country lookup table.
STEP 2: Use relationship to find all rows where Country line= 1
STEP 3: After Vertipaq identifies those rows, it returns a datacache containing a filtered Gas Output table and sends it to the formula engine.
STEP 4: After the formula engine receives the datacache, it evaluates the Gas Output (quantity) measure against it and returns the Total Gas Output based on the filter context.
Total Gas Output = 12,000 + 6,000 + 25,000 + 100,000 = 143,000
Understand how DAX works—be a better Power BI practitioner
Knowing how to do something is a skill. But when you understand how something works, you can alter that knowledge and adapt it for your best uses. You can contemplate why it works the way it does and apply your creativity to doing more with it.
Learn how things work and you can expand on them infinitely. Understanding the process behind DAX and its architecture in Power BI is crucial and beneficial for efficient DAX queries. It will also help you design comprehensive yet simple data models that meet your reporting needs. This fundamental understanding can also help advance your career in DAX and Power BI as a whole.
If you could use some help with DAX and data modeling, Senturus can help. We offer Power BI training for every skill level and consulting services along the entire Microsoft BI stack. As always, contact us with any questions.
