cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dmistry Regular Visitor
Regular Visitor

Is Gateway needed to be setup for SQL Server DirectQuery

Hi,

 

I have a client who has SQL Server on Premises and that will be the only data source required for building out dashboards. Once the report is developed, it will be published to the service. 

 

Now question is, Do I need to setup gateway to schedule refreshes? I believe directquery does not require refreshes to be scheduled as it shows live data. Not sure, how it would work if report is published to the service, would it show live data without gateway?

 

Can someone please calrify, thanks. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Is Gateway needed to be setup for SQL Server DirectQuery

hi, @dmistry 

Gateway is based on the data source not the way of get data.

And the most direct difference between import and directquery is that there is no data in the dataset when use directquery but the data set is import have the data in the data set.

So after you publish the report into power bi service, the report that is import could be viewed based on the data in data set, and you could setup a gateway then refresh or schedule refresh the data set. but there is no data in the data set when use directquery, so it needs to send a query to data source by gateway for the data. 

All of them need a gateway.

For which way i recommend to use, you could refer to the document:

For further knowledge, please refer to this document:

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

https://docs.microsoft.com/en-us/power-bi/refresh-data#live-connections-and-directquery-to-on-premis...

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team
Community Support Team

Re: Is Gateway needed to be setup for SQL Server DirectQuery

hi, @dmistry 

Unless data source is cloud data sources like Azure SQL Database, others all need a Gateway for refresh.

It has nothing to do with the way of get data.

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-faq

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dmistry Regular Visitor
Regular Visitor

Re: Is Gateway needed to be setup for SQL Server DirectQuery

Thanks Lin, appreciate the answer. However I am still not clear on Live Connection. If I use DirectQuery option for an on prem SQL Server as data source, ideally it should show us live data as that's the purpose of DirectQuery or LiveConnection.

 

Based on your answer, it seems that it won't really act as liveconnection for on prem sql server data source, looks like I need to setup gateway and then schedule refreshes right? @v-lili6-msft 

 

I would like to better understand how liveconnection would behave when data source is on prem sql server. FYI, we don't have azure sql db but I do follow what you are saying in that regards.

Community Support Team
Community Support Team

Re: Is Gateway needed to be setup for SQL Server DirectQuery

hi, @dmistry 

Gateway is based on the data source not the way of get data.

The On-premises data gateway acts as a bridge, providing quick and secure data transfer between on-premises data (data that is not in the cloud) and the Power BI, Microsoft Flow, Logic Apps, and PowerApps services.

(in short, power bi service need to use a gateway to connect to data source )

https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem

https://docs.microsoft.com/en-us/power-bi/service-gateway-getting-started

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dmistry Regular Visitor
Regular Visitor

Re: Is Gateway needed to be setup for SQL Server DirectQuery

Thanks again for the feedback @v-lili6-msft . One question, this means that if SQL server is on prem, DirectQuery will be of no use then as it will require refresh scheduling through gateway, i am clear on how gateway works and the need of it. Is there a specific difference between Import and DirectQuery for sql server on prem, in my understanding both will need refresh scheduling either daily or weekly. Can you recommend what should i use, import or directquery?

Community Support Team
Community Support Team

Re: Is Gateway needed to be setup for SQL Server DirectQuery

hi, @dmistry 

Gateway is based on the data source not the way of get data.

And the most direct difference between import and directquery is that there is no data in the dataset when use directquery but the data set is import have the data in the data set.

So after you publish the report into power bi service, the report that is import could be viewed based on the data in data set, and you could setup a gateway then refresh or schedule refresh the data set. but there is no data in the data set when use directquery, so it needs to send a query to data source by gateway for the data. 

All of them need a gateway.

For which way i recommend to use, you could refer to the document:

For further knowledge, please refer to this document:

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

https://docs.microsoft.com/en-us/power-bi/refresh-data#live-connections-and-directquery-to-on-premis...

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
dmistry Regular Visitor
Regular Visitor

Re: Is Gateway needed to be setup for SQL Server DirectQuery

thanks a lot @v-lili6-msft , appreciate your answers.