In this writing, I like to share how I compare the performance between Import, Direct Query, and Direct Lake modes, in Fabric (and in Power BI).
Before I go further, I must let you know that the sample data that I used here is not big enough, and has a maximum of one million rows. However, after testing this, I want to let you know that I am getting more interested in using Direct Lake mode.
So, I will keep continuing examining and validating when to use Direct Lake mode.
And as a first step, I share below that I have compared the three modes.
Note: I am not 100% sure why Direct Lake Mode cannot connect to DAX Studio. I am using 3.0.7 version. But anyway, let's move on.
I compared two different scenarios.
The first scenario is compared by using one million row tables in three modes.
The second scenario is compared by using fewer-row tables, but writing complicated DAX measures that use more of Formula Engine. The second scenario is performing a Sales-Pareto-analysis.
First scenario
I simply compared how the performance of each mode showed by using simple DAX measures.
Sales = SUM( Sales[Sales] )
Sales Ratio = DIVIDE ( [Sales], CALCULATE ( [Sales], ALL ( Customer[Customer Key] ) ) )
Import mode
Direct Query mode
Direct Lake mode
A finding from the first scenario
performance: Import > Direct Lake > Direct Query
➡️Number of customers, and row count in a fact table were not enough to tell the difference, but it motivates me to keep on investigating in Direct Lake mode.
Second scenario
In order to perform a Pareto analysis, I wrote below DAX measures and used those in each mode.
Sales = SUM( Sales[Sales] )
Sales cumulate WINDOW DAX =
CALCULATE ( [Sales], WINDOW ( 1, ABS, 0, REL, ADDCOLUMNS ( ALL ( Customer[CustomerKey] ), "@sales", [Sales] ), ORDERBY ( [@sales], DESC ) ) )
Cumulate percentage WINDOW DAX = VAR _allsales = CALCULATE ( [Sales], ALL ( Customer[CustomerKey] ) ) RETURN DIVIDE ( [Sales cumulate WINDOW DAX], _allsales )
Import mode
Direct Query mode
Direct Lake mode: DAX measures are written in the Lakehouse-SQL endpoint in the platform.
Direct Lake mode: DAX measures are written in the Power BI Desktop after connecting as Direct Lake mode.
A finding from the second scenario
performance: Import > Direct Lake > Direct Query
➡️ When a DAX measure becomes complicated, Direct Query mode might become out of the option to choose. And if Import mode cannot handle the number of rows in the tables, then the only way to consider is Direct Lake mode.
To summarize,
I start to find out that, once the DAX measures become complicated, and once the number of rows in the tables becomes larger and makes import mode struggle, using Direct Lake mode is a great way to enhance the performance of Power BI reports.
And I will keep continuing to find the benefits of Direct Lake mode.
I hope this helps to increase your focus and interest in Direct Lake mode in Fabric.
Comments