Forum Discussion

frtcndkm's avatar
frtcndkm
Frequent Visitor
4 years ago

Unexpected Snowflake Queries Sent From Power BI

I just converted my queries for Snowflake from widget navigation to native queries with Value.NativeQuery() function.

 

Example step I queried data: 

 

Source = Value.NativeQuery(Snowflake.Databases(Server,Warehouse){[Name=Database]}[Data],
"select * from """&Database&""".""PUBLIC"".""Table""",
null, [EnableFolding=true])

 

However Power BI sends multiple weird queries before sending the needed queries for the visuals. (even within the advanced editor) This is an example of the queries sent to Snowflake server before any other query: 

 

select *
from
(
select * from DBNAME."PUBLIC".TABLENAME
) as "_"
LIMIT 1 OFFSET 0

 

And worst thing is this query sent multiple time from the report page when it's initially opened, and it takes minutes for these queries get executed for the fact table w/more than Mio rows.

 

It seems like Power BI trying to get column schema(?) but why? And how to avoid these queries?

Need Help

4 Replies

    • frtcndkm's avatar
      frtcndkm
      Frequent Visitor
      4 years ago

      amitchandak no, there is only one dimension table which is connected with one-to-many and one-directional filtering. And the thing is even if I only load one table in a new file, it still sends those queries first when it's connected with native query.

       

      If I use below query, I don't have those queries however I would like to keep using native queries instead of this widget navigation. 

       

      let
      Source = Snowflake.Databases(Server,WH),
      DB = Source{[Name=DatabaseName,Kind="Database"]}[Data],
      PUBLIC_Schema = DB{[Name="PUBLIC",Kind="Schema"]}[Data],
      Table= PUBLIC_Schema{[Name="TableName",Kind="View"]}[Data]
      in
      Table

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi frtcndkm ,

    Have you resolved the issue now? If not , could you please provide the pbix file without privacy inforamtion and more details with your desired output? 

     

    Best Regards

    Community Support Team _ Polly

     

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

    • frtcndkm's avatar
      frtcndkm
      Frequent Visitor
      4 years ago

      Hi Anonymous , unfortunately no, I couldn't figure out avoiding it to send two queries when connected with native query option.

       

      Below are the details of the issue and here in this link you can find the .pbix which is connected to a Test Snowflake account I just created to solve this issue. I used sample database in the account.

       

      Bascailly, I created two M queries, one is with Snowflake's default widget navigation, second is with native query option. When I create two identical visuals in the report from those two different queries, the one with native query option sends two queries to the Snowflake and the first query sent (with LIMIT 1 OFFSET 0) takes too long for a database with millions of rows. (more than minutes)

       

      You can also see that the one with Native Query takes longer in the Performance analyzer. 

       

       

      Query sent from Widget Nav: 

       

      Queries sent from Native Query option: 

       

      You can use this test credentials to run queries on your end (I'll remove this account anyway in upcoming days)

      user: frtcndkm

      pass: Deneme123

       

      Thanks

      Firat