Direct Query unexpected behaviour

[rudoce]

Hello to you all.

We are facing a problem modelling a star with a very big fact table (>13.000 Millions of rows) and 5 dimensions linked using common Kimball approach. Those dimensions feed filters to be applied in the report, and all the tables are direct query accessed.

When we run a simple visualization filtered with values from dimension tables, the database (Snowflake) runs in the first place some queries completely unexpected: 

- select * from (select field1, field2, etc from dim1_table) as "_" LIMIT 1 OFFSET 0

- select * from (select field1, field2, etc from dim2_table) as "_" LIMIT 1 OFFSET 0

...

- select * from (select field1, field2, etc from fact_table) as "_" LIMIT 1 OFFSET 0

This happens the first time we run the report in a given day. Later runs do not show that behaviour. Looks like that in order to load the model, power bi first launch these queries. With the dimensions there is no problem, but the fact table lasts several minutes to perform the select.

The report is designed with a page filter and a simple visualization in another page.

Do you know how to avoid the select * from (select field1, field2, etc from fact_table) as "_" LIMIT 1 OFFSET 0 query?

Thanks in advance!!

[v-chenwuz-msft]

Hi @rudoce ,

Maybe you can try Advanced options SQL statement when you connect the Snowflake.

Best Regards

Community Support Team _ chenwu zhu

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

[frtcndkm]

Hi @rudoce, have you got any glue on this? We currently struggle with the same issue after we converted fact table from widget navigation to native query option

[rudoce · Accepted solution]

Hello frtcndkm.

We solved the issue avoiding the sql overwriting in the native query when accessing the source data.  Instead of using a native query, we pointed to the fact table using the UI and then we selected the required columns and applied the transformations needed.

I know it is anoying and something Power BI must work on, but is the only workaround I found.

To stick with query folding and better performance, I recommend you to create views in your source system with the required queries and then access them from Power BI using the navigation widget.

[rudoce]

Hi, chenwu zhu, and thanks for your reply.

Actually, we are doing so in both fact and dimension tables. We are writing sql commands in advanced options where we just select the columns we need.

We do not understand why Power BI sends to Snowflake queries where it selects the whole table with a limit 1 clause. Looks like it checks the model by doing so... but with massive tables obviously the performance is very poor and most of the times the report fails.

We have designed the same scenario with Microstrategy and it works fine...