BigQuery (BIEngine) and DirectQuery time

[mhar]

Hi,

I'm trying the new BIEngine in BigQuery together with Direct Query in PBI. BiEngine works fine and I can see that a query is returned after 500 ms. Before running the query I started performance analyzer in PBI desktop. For the same query I get 7317 ms stated as DirectQuery. This of course makes the experience much slower than imported mode. 

Is the conclusion that DQ is not an option against BigQuery regardless of how fast we can get the BigQuery queries to run. Or can I reduce the overhead in some way.

For information I have tried both having all tables (3) as DQ or have the two dimension tables as import (dual).

Thanks

[vanessafvg]

as far as  I know imported is going to be  faster most of the time than direct query unless you have huge amounts of data then you will need to use direct query  - it will be need to be optimised use a MPP type engine which I am assuming Big Query does?

this video is quite informative.

Import vs DirectQuery in Power BI

[mhar]

Thanks for your answer.

Yes, it's going to be faster, that is ok and expected. But BigQuery and especially now with the in-memory solution called BI Engine the query time is below 500 ms. 

What I'm asking is if there is a way to reduce the 7 second (in this example) overhead? 

Note that this question would still be relevant without BI Engine in BigQuery.  

[vanessafvg]

well one would need to understand what that overhead is.  Have you tried using DAX studio to understand more details of whats happening than what performance analyser can provide?

https://www.sqlbi.com/tools/dax-studio/

[yan]

DAX studio won't give much more details for DQ than the built-in Performance Analyzer. Many - including me in prior issues - have complained about DQ performance for BigQuery, using BI engine or not. BQ query and retrieval times are not the issue, the culprit I believe is the embedded ODBC driver (made by Simba). Issue would not exist if MS who stayed silent on it were to switch to direct API calls, bypassing the ODBC limitations. For instance, it could natively support nested fields without unnecessary and extensive (read, slow) transformation.

Until then - am still hopeful -, BI engine is best used in other tools and for PBI, use import (e.g. scheduled).