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
Anonymous
Not applicable

Taking time to hit the Snowflake data source in Direct Query Mode

Hi Team,

We had published some power bi reports with Snowflake as the data source. We are using direct query mode due to additional securoty constraints which needs to be in the source. However while using the reports we observed some performance lags as given below 

Initial Load of the Report - 25 Secs

Refresh of report data based on the filter change -  22 Secs

We tried to understand this time taken in details. While doing that we observed the following

 

1. Initial Load of the report - Total time taken - 25 Sec

    1.a Time taken for the queries to reach Snowflake - 15Secs

    1.b Time taken in Snowflake for query execution - 7 Secs

    1.c Time taken to render the report after query execuiton - 3 Secs

 

2. Refresh of report data based on the filter change - Total time taken - 22 Sec

    2.a Time taken for the queries to reach Snowflake - 12 Secs

    2.b Time taken in Snowflake for query execution - 6 Secs

    2.c Time taken to render the report after query execuiton - 4 Secs

Can any one help me understand why it is taking 12 Secs or 15 Secs  for the queries to reach Snowflake(Is there any lags that could be happening at the power bi gateway)

What is the best way to analyse these performance issues from the Power BI service.

 

 

 

2 REPLIES 2
v-eachen-msft
Community Support
Community Support

Hi @Anonymous ,

 

Power BI does not import data over connections that operate in DirectQuery mode. Instead, the dataset returns results from the underlying data source whenever a report or dashboard queries the dataset. Power BI transforms and forwards the queries to the data source.

So it will take some time to connect to data source by gateway. If you use a import mode, it wil take less time because data is stored in dataset of service and you can get data directly.

Here are the documents for your reference:
https://docs.microsoft.com/en-us/power-bi/refresh-data#understanding-data-refresh

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#power-bi-connectivity-modes

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Anonymous
Not applicable

Thank You Team for the infomration. Is there any way I can analyze the following for a published report in Power BI Service

1. Network  Latency between the Power BI service and the Data Source while using gateway

2. How much time the Power BI service is taking to create the queries that are being sent to data source

Because for us the out of 24 Seconds 15 secs  is spent for the query to reach the Data Source. Our end goal is to reduce that time if any possibility is there

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.

Top Solution Authors
Top Kudoed Authors