Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
rudoce
Frequent Visitor

Direct Query unexpected behaviour

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!!

1 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.

View solution in original post

4 REPLIES 4
v-chenwuz-msft
Community Support
Community Support

Hi @rudoce ,

 

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

vchenwuzmsft_0-1656923801825.png

 

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.

 

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

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.

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...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.