Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Real-time row updates while using incremental refresh

I have a table with the following data:

 

IDCreated TimeModified TimeLast Updated Time
101/01/2020 05:00:00 AM01/01/2020 11:00:00 AM01/01/2020 11:00:00 AM
201/01/2020 06:00:00 AM01/01/2020 12:00:00 PM01/01/2020 12:00:00 PM
301/01/2020 07:00:00 AM01/01/2020 01:00:00 PM01/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:

 

IDCreated TimeModified TimeLast Updated Time
101/01/2020 05:00:00 AM01/01/2020 11:00:00 AM01/01/2020 11:00:00 AM
201/01/2020 06:00:00 AM01/01/2020 12:00:00 PM01/01/2020 12:00:00 PM
301/01/2020 07:00:00 AMnull01/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: 

 

301/01/2020 07:00:00 AM01/01/2020 01:00:00 PM01/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.

Need Help
Tutorial Requests

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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:

       

      IDCreated TimeModified TimeLast Updated Time
      101/01/2020 05:00:00 AM01/01/2020 11:00:00 AM01/01/2020 11:00:00 AM
      201/01/2020 06:00:00 AM01/01/2020 12:00:00 PM01/01/2020 12:00:00 PM
      301/01/2020 07:00:00 AM01/01/2020 01:00:00 PM01/01/2020 01:00:00 PM
      401/01/2020 01:00:00 PMnull01/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:

       

      IDCreated TimeModified TimeLast Updated TimeMODE
      101/01/2020 05:00:00 AM01/01/2020 11:00:00 AM01/01/2020 11:00:00 AMImport
      201/01/2020 06:00:00 AM01/01/2020 12:00:00 PM01/01/2020 12:00:00 PMImport
      301/01/2020 07:00:00 AMnull01/01/2020 07:00:00 AMImport
      401/01/2020 01:00:00 PMnull01/01/2020 01:00:00 PMDirectQuery

       

      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:

       

      IDCreated TimeModified TimeLast Updated TimeMODE
      101/01/2020 05:00:00 AM01/01/2020 11:00:00 AM01/01/2020 11:00:00 AMImport
      201/01/2020 06:00:00 AM01/01/2020 12:00:00 PM01/01/2020 12:00:00 PMImport
      301/01/2020 07:00:00 AM01/01/2020 01:00:00 PM01/01/2020 01:00:00 PMDirectQuery
      401/01/2020 01:00:00 PMnull01/01/2020 01:00:00 PMDirectQuery