SQL Database Vs Azure SQL Database Direct Query Power BI

[mak82pk]

Dear All,

I am confused understanding direct query between SQL Database Vs Azure SQL Database Power BI

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-directquery-data-sources/

I am currently using SQL Server

For SQL:

Gateway is required.

Does it mean I cannot have real-time data when using direct query and can only refresh dataset after 15min?

It is mentioned under benefit of using direct query 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/#limitations-of-di...

The 1 GB dataset limitation does not apply to DirectQuery

And limitation is

The resultset of a query to external data source has exceeded
the maximum allowed size of '1000000' rows.

 Is the above same for SQL server?

Your prompt response would be highly appreciated.

[tringuyenminh92 · Accepted solution]

Hi @mak82pk,

There is no different between SQL DB and Azure SQL DB, it's based on your connection mode (Import mode or Direct Query).

• The limitation 1m rows will be applied for both SQL DB and azure db when you use Direct QUery for them.
• The 1GB dataset is applied for Import mode (whatever your sources is cloud or on-premise)
• Gateway is required for SQL DB cause it's on-premise, so it needs gateway to handles data transfering from your local to PBI cloud.
• When using DirectQuery or Live connection, you are in near real-time scenario, and data is up to date whenever users/clients interacts with your charts/reports. If there is no interaction, the data of report will be refreshed every 15 minutes (this refresh period now could be adjusted to 1hour, daily,...)

[mak82pk]

Thanks for your prompt response.

I embedded the URL in my web application and report was updated after 1 hour even when I was interacting with the report.