Real-time row updates while using incremental refresh

[Anonymous]

I have a table with the following data:

ID	Created Time	Modified Time	Last Updated Time
1	01/01/2020 05:00:00 AM	01/01/2020 11:00:00 AM	01/01/2020 11:00:00 AM
2	01/01/2020 06:00:00 AM	01/01/2020 12:00:00 PM	01/01/2020 12:00:00 PM
3	01/01/2020 07:00:00 AM	01/01/2020 01:00:00 PM	01/01/2020 01:00:00 PM

This table is configured to refresh 4 times a day at 12:00:00 AM, 06:00:00 AM, 12:00:00 PM, 06:00:00 PM. In the incremental refresh settings, it is configured to refresh the last 1 day of data at each refresh interval using the Created Time column for the RangeStart and RangeEnd parameters. Detect Data Changes is turned on using the Last Updated Time column.

Suppose the data is refreshed at 01/01/2020 at 12:00:00 PM. The stored data would look like this:

ID	Created Time	Modified Time	Last Updated Time
1	01/01/2020 05:00:00 AM	01/01/2020 11:00:00 AM	01/01/2020 11:00:00 AM
2	01/01/2020 06:00:00 AM	01/01/2020 12:00:00 PM	01/01/2020 12:00:00 PM
3	01/01/2020 07:00:00 AM	null	01/01/2020 07:00:00 AM

Now suppose I wanted to see real-time data at 02:00:00 PM. Row 3 would look like this: 

3

01/01/2020 07:00:00 AM

01/01/2020 01:00:00 PM

01/01/2020 01:00:00 PM

But this would not be updated until the next refresh occurs at 06:00:00 PM. Is there a way for me to get real-time row updates while still using incremental refresh to store archived data? One option I thought of was creating a separate DirectQuery dataset of the last 6 hours of data based on the Last Refreshed Time, and have that "update" the archived data until it gets incrementally refreshed. I'm not sure how I would do that though.

[parry2k]

@Anonymous most recently (In dec, 2021) hyrbrid tables were introduced and that will handle your ask. Read more about hybrid tables here . Keep in mind hybrid tables are in preview. FYI

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

[Anonymous]

By my understanding, the DirectQuery partition in hybrid tables only adds new data as it is created in real time. I need something that will update previously created rows. For example, if the database looked like this at 02:00:00 PM:

ID	Created Time	Modified Time	Last Updated Time
1	01/01/2020 05:00:00 AM	01/01/2020 11:00:00 AM	01/01/2020 11:00:00 AM
2	01/01/2020 06:00:00 AM	01/01/2020 12:00:00 PM	01/01/2020 12:00:00 PM
3	01/01/2020 07:00:00 AM	01/01/2020 01:00:00 PM	01/01/2020 01:00:00 PM
4	01/01/2020 01:00:00 PM	null	01/01/2020 01:00:00 PM

The last incremental refresh happened at 12:00:00 PM. So from my understanding, the data in a hybrid table would be stored like this:

ID	Created Time	Modified Time	Last Updated Time	MODE
1	01/01/2020 05:00:00 AM	01/01/2020 11:00:00 AM	01/01/2020 11:00:00 AM	Import
2	01/01/2020 06:00:00 AM	01/01/2020 12:00:00 PM	01/01/2020 12:00:00 PM	Import
3	01/01/2020 07:00:00 AM	null	01/01/2020 07:00:00 AM	Import
4	01/01/2020 01:00:00 PM	null	01/01/2020 01:00:00 PM	DirectQuery

I'm looking for a solution that would keep the updated rows (based on the max Last Updated Time) in DirectQuery and have that update the rows in my archived data. So it would look like this:

ID	Created Time	Modified Time	Last Updated Time	MODE
1	01/01/2020 05:00:00 AM	01/01/2020 11:00:00 AM	01/01/2020 11:00:00 AM	Import
2	01/01/2020 06:00:00 AM	01/01/2020 12:00:00 PM	01/01/2020 12:00:00 PM	Import
3	01/01/2020 07:00:00 AM	01/01/2020 01:00:00 PM	01/01/2020 01:00:00 PM	DirectQuery
4	01/01/2020 01:00:00 PM	null	01/01/2020 01:00:00 PM	DirectQuery