Dual mode can potentially speed up queries by enabling the cache in certain situations. In theory, it can speed up queries in certain situations by utilizing the cache.
Microsoft’s description is here
In the following example, we start with the V_FactOnline sales view. It is a large table with over 12M rows and 19 columns. We could import it into our dataset, but it will increase the refresh time of our dataset and use valuable memory in our model that we may want to conserve.
To get an idea of the size of the table in our model, we can use the Vertipaq analyzer in DAX Studio.
I have imported 3 tables into our data model so far.
1. V_FactOnlineSales in import mode
2. V_DimProduct in import mode
3. FactOnlineSales table in direct query mode (which is the same size as V_FactOnlineSales
The total size of our dataset now is 233MB, and we can see that the imported V_FactOnlineSales table is taking up nearly all that space. When we have added another 50 tables to our model, we might decide to switch the v_fact_online sales table to a direct query model to reduce refresh time and free up memory. You will also notice that the FactSalesOnline table in direct query mode uses no memory.
After deleting the imported V_FactOnlineSales table, we can import it again, but in import mode, so it does not take up any space in our dataset. We have a one-to-many relationship between V_dimProduct and V_FactOnline Sales, with a single cross-filter direction, so only v_DimProduct and filter V_FactOnlineSales and not the other way around. They are joined by the ProductKey.
We’ll start by creating some simple measures from the FactOnlineSales table as follows:
Sales = SUM(V_FactOnlineSales[SalesAmount])
Cost = SUM(V_FactOnlineSales[TotalCost])
Profit = [Sales]-[Cost]
Profit Margin = DIVIDE([Profit], [Sales], 0)
We can organise them in the home table of V_FactOnline Sales and give them a folder to keep them together.
We can then add our measures to a matrix visual and a slicer on product name using the measures and the product name field from the dimproduct table. We can then use the performance analyzer and refresh the visuals. It takes about 5 seconds for the matrix to load. If I clear the query results and refresh the performance analyzer. I have a nice benchmark of about 5 seconds to work with. The performance analyzer is designed not to cache the queries, so this seems reliable to work with.
If I select a single product from the slicer, it takes about 1 second to load the matrix.
Now, if I remove a product from my slicer, it takes longer (about 6 seconds, consistently).
Next, I could move on to testing the DimProduct table in dual mode. Note I had to recreate the table in Direct Query mode and switch it to dual mode, as I wasn’t allowed to switch from import mode to dual mode.
A comparison of the tests between the two modes is in the table below.
I found I had quite consistent results, although when the full table was selected, I did run into performance problems on my laptop, and it was more of an issue in Dual mode, which does use more memory and some additional CPU (it creates a copy of the table import mode into memory to execute the query). Therefore, I will need to do a cloud-based test to get a more reliable picture. In this test, I’m using Power BI locally with SQL Server on an aging laptop.
The most obvious difference I found between the 2 modes was in individual selections in the slicer, with the Dual mode performing over twice the speed vs. import mode, which is consistent with what should be expected by using dual mode.
Matrix loading speed using the performance analyzer
| Product Slicer Selection | DimProduct: Import FactOnelineSales: Direct Query | DimProduct: Dual Mode FactOnlineSales: Direct Query |
| Select All: | 5-6 seconds | 6 -8 seconds (CPU maxing out) |
| One Product selected | 1.5 seconds | 0.5 seconds |
| Exclude one product | 6 seconds | 1.5 seconds |
To summarize, performance improvements were apparent for slicing with a dimension using dual mode, but a cloud-based test is required, in particular, to see the speed of the full unfiltered matrix refresh.