Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
If I am using direct-query for SQL database on my server [not Azure SQL DB] configured by on-premises gateway there is no limit for data refresh?
Also, can I get real-time data using the same?
When using direct-query is there still a limit for 1 GB upload? As only the dataset will be updated with new records, not the whole dataset.
Solved! Go to Solution.
Hi @mak82pk,
How clever you are.
As the SQL Server database is on-premise data source, the gateway is required. As you use DirectQuery mode, on-premise data gateway is necessary. See: Live connections and DirectQuery to on-premises data sources.
Data source | Live/DirectQuery | User configured manual or scheduled refresh | Gateway required |
Analysis Services Tabular | Yes | Yes | Yes |
Analysis Services Multidimensional | Yes | Yes | Yes |
SQL Server | Yes | Yes | Yes |
SAP HANA | Yes | Yes | Yes |
Oracle | Yes | Yes | Yes |
Teradata | Yes | Yes | Yes |
Best Regards,
Qiuyun Yu
Hi @mak82pk,
Based on my research, there is a 1 million row limit for returning data when using DirectQuery. See: Limitations of DirectQuery. As the dataset is DirectQuery mode, we can manually adjust the refresh frequency in the Scheduled cache refresh option in the Power BI service. See: Live connections and DirectQuery to on-premises data sources.
Besides, I will confirm it internally whether the 1 G limit apply for the pbix file which use DirectQuery mode.
Best Regards,
Qiuyun Yu
Thanks for you prompt response. I found the answer in the link you provided
The 1 GB dataset limitation does not apply to DirectQuery
Also, is it possible not to use on-premises gateway and get real-time data using SQL Server [Not Azure SQL Server]?
Hi @mak82pk,
How clever you are.
As the SQL Server database is on-premise data source, the gateway is required. As you use DirectQuery mode, on-premise data gateway is necessary. See: Live connections and DirectQuery to on-premises data sources.
Data source | Live/DirectQuery | User configured manual or scheduled refresh | Gateway required |
Analysis Services Tabular | Yes | Yes | Yes |
Analysis Services Multidimensional | Yes | Yes | Yes |
SQL Server | Yes | Yes | Yes |
SAP HANA | Yes | Yes | Yes |
Oracle | Yes | Yes | Yes |
Teradata | Yes | Yes | Yes |
Best Regards,
Qiuyun Yu
Hi @mak82pk,
Our company has a product that allows you to use DirectQuery mode in a hybrid environment without using the Data Gateway. Since you have to poke a hole in the firewall for the Data Gateway, anyway, we just skip the Data Gateway and go straight to our product instance in the cloud.
The product is called Conduit and it was created specifically to allow direct query connections on all connectors, as well as create connectors for some data sources Power BI doesn't support (e.g. ElasticSearch 6).
Ping me if you have more questions.
Thanks,
== Ross ==
Hi All,
I am trying to connect Power BI with IBM DB2 for Direct Query. The required driver has been installed but it still cannot connect to IBM DB2 server. When I tried to connect with Microsoft .Net Framework, it couldn't connect as well. I checked in the command prompt, and it shows .net framework 4.8 is being installed wherein, 4.5 or higher verison is required. Can anyone please help me on this?
Hi Guys,
Any updates on this?