Direct Query with Merge - When is it executed?

[tctrout]

Hello,
I know Direct Query pushes the work back to the server, but when does this occur when 2 direct query tables are merged together?

Is there work being performed when the front end model is loaded OR after a DAX Query is created?

Additionally, I am performing an Inner Join equivelant on the Merge, I confirmed that Query Folding is indeed taking place.  Table A has 10 rows and Table B has 1,000,000 rows.  I expect only 10 rows, however when I go to load my model to the front end I can see it looping thru 1,000,000 rows which surprised me.  Why is the work being performed on model load (instead at time of DAX execution) and why is it considering all 1,000,000 rows to eventually get to the 10 rows?

Update:  After I complete the Direct Query merge and load it to the model, my computers CPU and Memory are very high due to the PowerBI Desktop, all allocated in the Mashup Evaluation Container.  I am surprised to see the pbix to idle with high usage when no DAX Queries have been executed, can anyone shed insights?

[v-henryk-mstf]

Hi @tctrout ,

You are right. This is also the difference between different connection modes.

Power BI: Import Mode Vs DirectQuery Mode

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-henryk-mstf · Accepted solution]

Hi @tctrout ,

Based on your description, I don't really recommend you to use this compound model with double direct query. It may have some security risks: the query sent to one data source can contain data values already retrieved from another data source.

Use composite models in Power BI Desktop - Power BI | Microsoft Docs

Also for cpu usage, part of the reason depends on the complexity of the created measure (the measure takes up cpu computing time, not memory). Since it is a dq mode, powerbi connects directly to the data source and sends queries to the back-end data source every time the measure is used.

In addition you can use the performance analyzer to find out the performance of visual objects or dax publics and how much they consume resources.

If the problem is still not resolved, please point it out. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tctrout]

@v-henryk-mstf 

Thank you for the response, I had not gotten much traction.

I agree with everything you stated, though in this case there are no measures, no visuals no DAX Queries at all.  I had not gotten that far in my development, I noticed that merging tables in Direct Query mode made my PC's Memory and CPU idle at 90% while not performing any DAX / Visual work.

Ive since moved away from Direct Query, performed the same actions in Import Mode and high usage of CPU and Memory has gone away.