Forum Discussion

alexschindler's avatar
alexschindler
Icon for Helper I rankHelper I
1 year ago

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

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
alexschindler
Icon for Helper I rankHelper I
1 year ago

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's avatar
    alexschindler
    Icon for Helper I rankHelper I
    1 year ago

    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'