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
mak82pk
Helper I
Helper I

Power BI Direct query real-time refresh and data upload

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.

 

1 ACCEPTED SOLUTION

Hi @mak82pk,

 

How clever you are. Smiley Very Happy

 

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

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

View solution in original post

6 REPLIES 6
v-qiuyu-msft
Community Support
Community Support

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.

 

q4.PNG

 

Besides, I will confirm it internally whether the 1 G limit apply for the pbix file which use DirectQuery mode.

 

Best Regards,
Qiuyun Yu

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

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. Smiley Very Happy

 

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

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

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 ==

Anonymous
Not applicable

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?

Connection parametersConnection parametersConnection error is Power BI gateway. Same error comes through get data source as wellConnection error is Power BI gateway. Same error comes through get data source as well

Anonymous
Not applicable

Hi Guys, 

Any updates on this?

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