User-Defined Aggregations slow even though aggregation is hit; is there still some direct query?

[alexschindler]

Hi all

I am having troubles getting the user-defined aggregations to work as expected. The aggregations are hit, but the queries are still very slow.

I have a minimal example with

- a sales table in direct query mode (_FACT_SALES)

- an aggregated sales table in import mode (FACT_SALES_AGG_DATE_POS_ARTICLE)

- 3 dimension tables in dual mode (_DIM_ARTICLES, _DIM_DATE, _DIM_POS)

The tables are connected with relationships. I set up a user-defined aggregation on the aggregated table.

I have a few measures that are based on _FACT_SALES. All of them should only use the aggregated table and the dimension tables. Yet they take a long time to evaluate (in the current example > 16 seconds), while I expect that they should hit the aggregation and be very fast (< 1 second).

DAX studio shows that the aggregation is hit (<match found>), but I have the impression that the engine still does some stuff with direct query - and that this is why it takes so long.

Did anyone have similar experiences? I'm grateful for any hints. Thanks

I will attach a screenshot of the query on DAX studio and another one of the model.

All best and thanks

Alex

model 

query on DAX studio

[Anonymous]

Hi @alexschindler,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[alexschindler]

Hi Pavan

Thanks for your answer and sorry for the late reply!

I did more extensive testing considering all of your points. At the end I found the following that has to do with the connections on the Power BI Service:

- if I have the VNET data gateway turned on (as shown in 1. on the left) there is always some direct query connection - even though at the end a match is found.

- if I turn off the VNET data gateway (as shown in 2. on the right), the user-defined aggregation works as expected.

In case 1 (gateway on) the query takes a long time. A match is found, but there is still a direct query.

 

If I run the query a second time, I no longer get this (due to some caching I assume):

I'm unclear why the VNET data gatway interferes with the user-defined aggregation and apparently forces some useless direct query connection at the beginning. Also the DAX query does not rely on the data that comes from the SharePoint (and was connected through the gateway).

Does anyone have any ideas? I'm glad it seems to work when I turn off the VNET data gateway, but I would still like to know how to do this in a scenario where the gateway is necessary. Thanks

Update: I tried the same again some hours later with the setting from case 2 (without VNET data gateway) and it did not work anymore, that is, I was back to the situation with the phantom direct query: 

[alexschindler]

Update: by now I believe that this whole problem may have to do with the VNET data gateway and/or the Fabric Warehouse going to sleep.

• If the dataset is connected to a source via a VNET Data Gateway (even if the query does not have anything to do with the gateway), the first query takes a really long time (> 60 seconds).
• If the dataset is connected to the Fabric Warehouse it takes > 10 seconds for the first query.

I am under the impression that this may be a bug. I suspect that upon the request the dataset tries to get all the potential sources ready (and subsequently the Fabric Warehouse and the VNET Data Gateway). Even though these sources are not necessary for the query (since everything goes to the table in import mode), it still waits for the sources to be ready before finishing the request.

I also observe 2 small queries on the Fabric Warehouse (that take 0 seconds). I am not sure why they are necessary, since the query can be solved through the import mode (match found). It is striking that these queries are shown at 12:34:09, so at the tail end of the query processing time (as if something else – potentially the waking up – is taking place before this).

On the Fabric Warehouse side:

The 2 queries on the warehouse:

EXEC sp_set_session_context 'root_activity_id', '1cfd6333-91e6-477b-910f-e2df8cf90152', 1

SELECT 'EngineEdition' AS 'EngineEdition', SERVERPROPERTY('EngineEdition') AS 'EngineEditionServerProperty'

[Anonymous]

Hi @alexschindler,

I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.

Please continue using Microsoft community forum.

Thank you,
Pavan.

[Anonymous]

Hi @alexschindler,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[alexschindler]

Hi Pavan

Thanks so much for taking the time to look into it.

As for your points:

1. this should be fine – and you can also see on the DAX studio screenshot that it always finds a match.

2. the 3 relevant dimension tables are all in dual mode.

3. I don't think my DAX measures are very complex. What kind of measure is it that may bypass the aggregation? Generally it's mostly a sum of the net sales.

4. I'm hoping that no direct query should be involved for my query. However, it seems that maybe there is something going on under the hood.

So all in all the aggregation is working theoretically; however, it takes a big amount of time such that I can't help wondering if some invisible direct query is going on under the hood. I'm not sure how to avoid this though.

Thanks for any further hints.

Alex

[Anonymous]

Hi @alexschindler,

Thank you for your detailed response. Please follow below steps to solve this error;

1. Make sure relationships between aggregation and dimension tables are optimized, and test switching dimension tables to Import mode to rule out DirectQuery issues.

2. Run Server Timings in DAX Studio to check if DirectQuery calls are still occurring, even for simple measures.

3.Ensure your DAX measures only reference columns present in the aggregation table to prevent unintended DirectQuery triggers.

4Enable Query Reduction Settings;

     In Power BI Desktop, go to File > Options and Settings > Options > Query Reduction and enable options that minimize unnecessary queries, which might help in reducing background DirectQuery operations.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,

Pavan.

[Anonymous]

Hi @alexschindler,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[Anonymous]

Hi @alexschindler,

Thank you for reaching out in Microsoft Community Forum.

Please follow below steps to acheive this error;

1. Make sure the aggregation table has all necessary columns and granularity for your measures, and verify it's properly related to the dimension tables.

2. Confirm that relationships are set up correctly for dual-mode tables (_DIM) to ensure the aggregation is used instead of DirectQuery when possible.

3. Review your DAX measures to ensure they are designed to leverage the aggregation table directly. Avoid complex measures that might bypass the aggregation.

4. If DirectQuery is involved, Make sure the data source is optimized and use the Performance Analyzer to pinpoint any bottlenecks. and Refresh your model or clear the cache to make sure Power BI uses the updated aggregation settings.

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.