Composite model issue

[_power_bi]

I am having an issue with a composite model.  Here is the setup:  a customers table, a customer_orders table, and an order details table. The first two are imported, the order details table is massive so it uses direct query as I cannot import this table.  The end user will drillthrough from a customer to see all orders for that customer.  Then, to see an specific order details, they will drillthrough on an order and see those details.  

The issue I am having is that the first time (and only first time) that I drillthrough to the order details view (direct query) it takes a couple minutes to load the results.  After that, if I go back out and select a new order and drillthrough, the results are instant. 

The query itself is optimized and returns results (with the order number) parameter within a few seconds. 

I am just wondering what I am doing wrong. Perhaps it is a modeling issue, but what I thought was happening is the order number was being passed to the direct query as a parameter.  But since it is taking so long, perhaps the direct query is running by itself, then from that result it is being filtered. I don't know.  The performance analyzer doesn't show me anything other than it takes a few minutes to load the visual (on the first occurrence). 

I saw a video on dynamic m query parameters and will try that if this is the scenario for it.  There are over a million orders. Also, the user doesn't type in the number, it will have to be drillthrough from a table to the order details. 

Any help is appreciated as I would like to use this technique on other reports that are fairly large. 

[Anonymous]

Hi @_power_bi ,

Is the problem occurring on the PBI desktop or the service? What version are you using?

Could you please show a screenshot of the performance analyzer? I would like to confirm if the performance issue is happening in the query preparation stage or in the visual rendering stage.

DirectQuery in Power BI - Performance diagnostics

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

Also try increasing the maximum number of active connections, which can improve performance in some scenarios.

DirectQuery in Power BI - Power BI | Microsoft Learn

Evaluation configuration settings for Desktop - Power BI | Microsoft Learn

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[_power_bi]

My main issue is that this delay happens the first time and only the first time when drilling through. If I pick a different order number or simply repeat the drillthrough, it responds immediately.  The rest of the report is snappy and no delays. 

1st time:

2nd time:

[rajendraongole1]

Hi @_power_bi -Especially when dealing with large datasets and DirectQuery. The key points to consider are how the query is being executed, the caching behavior of Power BI, and the modeling setup. With DirectQuery, it’s essential to ensure that the query is being folded correctly. Query folding refers to the process of translating Power BI actions into SQL queries that can be executed by the data source. If the query isn’t fully folded, Power BI might retrieve more data than necessary and then apply filters locally, which can be very slow.

Understanding query evaluation and query folding in Power Query - Power Query | Microsoft Learn

Composite Model - Relationships: Ensure that the relationships between the customers, customer_orders, and order details tables are correctly set up. The order details table should have a direct relationship with customer_orders, typically using the OrderID. Misconfigured relationships can lead to inefficient queries.

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

If your current approach isn't optimizing the DirectQuery as expected, using Dynamic M Query Parameters could help. This technique allows parameters to be passed directly to the source query, which can significantly improve performance.

Hope the above resources and information helps.