Incremental Refresh with Direct Query

[toryb]

It is my understanding that Increment Refresh is now supported for tables with Direct Query, although it requires Power BI Premium. (See this document). However, when I access the Incremental refresh and real-time data dialog, I receive this notice:

I cannot set the option to "Incrementally refresh this table." How can I enable this on my table?

Incidentally, I do have a Power BI Premium subscription.

Thanks

[bcdobbs · Accepted solution]

Hi @toryb,

A pure direct query connection won't support incremental refresh because by it's definition you pull fresh data every time.

I think what you might be refering to is Power Bi's new Hybrid tables?

This guide steps you through it: Hybrid tables in Power BI - The ultimate guide! - Data Mozart (data-mozart.com)

You basically switch the table to import mode and then configure a partition (new data) that is accessed via direct query.

Hope that helps.

[toryb]

Thanks for the quick reply @bcdobbs! The document I linked in the question is titled: "Incremental refresh and real-time data for datasets" and it states: 

"Incremental refresh is supported for Power BI Premium, Premium per user, Power BI Pro, and Power BI Embedded datasets.

Getting the latest data in real time with DirectQuery is only supported for Power BI Premium, Premium per user, and Power BI Embedded datasets."

I'm not sure if they are stating something different than "Incremental Refresh" when they say "Getting the latest data in real time", but I understood that to be incremental refresh. Additionally, if you look at the screenshot I added of the Incremental refresh and real-time data dialog, right under the error message it says "Plus get the latest data in real-time with DirectQuery". Do you have any idea what they are refering to here if not the Incremental Refresh? It may be hybrid tables, but there is no mention of that in the article the "Learn More" link takes you to. It takes you to the same artilce I linked in my question.

I'll look into hybrid tables to see if that meets my requirements, but I am really looking to "get the latest data in real time" using a direct query. Any thoughts may be helpful.

Thanks again!

[bcdobbs]

Hi @toryb ,

The article you sent and the guide I sent are talking about the same thing. I'll try and explain.

Power Bi has until recently had two main ways of getting data...

1) Import mode where data is loaded on a schedule into a Vertipak database in the service. This allows for very fast query timings however the data is only as up to date as when it was last loaded in. Also if you have a vast dataset the load time could be long.

2) Direct Query where power bi queries the source system every time you refresh a visual. Aggregations are done on the source system and results are sent back to the service to display. This ensures the data is live and avoids the load time issues. However queries run much slower.

You choose between these two modes when you import data into your model but can switch from Direct Query to Import at any time (just can't go the other way)

From the screen shot you sent your table is using direct query already and so you are getting live data.

We then come to incremental load. This relates to Import mode. It allows the service to spend a long time doing an initial load for all the historic sales data going back 5 years for example but after it's brought that data into it's data store the next time it refreshes it only gets data created recently (last year/month/day depending on your data).

All of the above has been available for a a while.

Finally we get to the article you sent and the blog I linked to. They refer to a new technology from microsoft that gives you the best of import mode (fast queries) and direct query (real time data). With that setup you still have your table set to import mode and use incremental refresh the data on a schedule only bringing in recent data but for any data created "today" or within a set time period it swtiches over to a direct connection.

[toryb]

Thank you @bcdobbs for your detailed explanation. As usual, Microsoft documentation has misdirected me. I will read the blog post you provided and research the use of Hybrid tables. If that proves to solve my problem, I will certainly accept it as a solution. 

There are other requirements I have that drove me to Direct Query. Primarily, I am intending to utilize automatic page refresh to cause an incremental data refresh to get the latest data and then update the screen to display the visualization of the data. Basically, we are attempting to show a "real-time" snapshot of manufacturing data on a large screen at different workcenters on the shop floor. There wlll be no user interaction and we will need to either use a scheduled refresh of about 5 seconds or a change detection refresh to update the data and related visualization when it changes. According to this document "Automatic page refresh is available for DirectQuery sources ..." I hope I can utilize the automatic page refresh with hybrid tables.

I'll be sure to update my findings here for future users to reference.