Hybrid Mode in Incremental Load - How to tackle calculated datetime column scenario please

[RKM]

I am having some hard Time to grasp the idea of Hybrid (Dec 21 Release) Mode of Incremental refresh and wondering if practically I can fit this in my scenario. (Please Note: I am aware what is incremental load, how to set up etc and have done that several times, so my question is in Particular about the Real Time /Hybrid mode enhancement of Power BI per December 2021 update)

So, here I am showing a representative set of data. Materials are scheduled to arrive at different calendar days (Includes Future Dates), then once it arrives, We get the Arrived_At and Processed By - columns updated.

Sometime some of the Materials are picked for sample test and then SampleTest_At column gets updated and in general once item is processed then Processed_At column is populated.

[Sampling, Processing happens with in a day of arrival usually and arrival is usually either same calendarDay of Schedule or sometime 1 or 2 hours before midnight last day.]

Although, Sampling and Processing are not sequential. Either can happen before other.

What I did in past was : Creating a derived column in View, with logic as.

ISNULL ( Max(Arrived_At_UTC, SampleTest_At_UTC, Processed_At_UTC) , IIF(CalendarDay_UTC > getdate, getdate, CalendarDay) 

~ Again above line is just for illustration. i.e. Basically pick the max among Arrived, SampleTest, Processed - Datetime if any of these exists, otherwise pick CalendarDay as long as it is less than today, else, pick Getdate.

With That Derived Column : [ChangedData_DateTime]

I was happily using Incremenal Refresh with Config as [2Years For Archive], [7 Days for Incremental], Refresh Schedule Daily 24 times each hour. 

Now I want to enable DirectQuery to go into Hybrid mode under IR Policy. But the challenge is Above Type of Calculated View Columns are not allowed under Hybrid Mode in Incremental Refresh Scenario.

I really/badly want to get the some workaround so to not force users wait an hour to see latest data. Any idea please?

Material	Processed By	CalendarDay_UTC	Arrived_At_UTC	SampleTest_At_UTC	Processed_At_UTC
Tea	Jim	04-Jan-2022 00:00:00	03-Jan-2022 23:11:34		04-Jan-2022 01:48:34
Coffee	Jim	04-Jan-2022 00:00:00	04-Jan-2022 00:12:30	04-Jan-2022 02:20:00	04-Jan-2022  01:48:34
Tea	Jim	30-Jan-2022 00:00:00	30-Jan-2022 00:05:04	30-Jan-2022 02:00:00	30-Jan-2022 03:20:21
Coffee	Jim	30-Jan-2022 00:00:00	30-Jan-2022 00:12:30
Tea		02-Feb-2022 00:00:00
Coffee		02-Feb-2022 00:00:00

[Anonymous]

Hi @RKM,

If you are working with the simple if statement logic to replace values, have you tried to add a custom on the query edit side?
These column values will be calculated every time the source table update and been transformed and fixed as static values into data model tables.
Regards,

Xiaoxin Sheng

[bcdobbs]

Can you elaborate a little on the data source? Is it SQL Server? Are you creating the calculated column in a sql view?

[RKM]

Yes SQL Server. Calculated column in SQL View

[bcdobbs]

What error does it give?

I'm guessing that because the live aspect runs in direct query mode and is assuming you have a large amount of data you really are going to need an index on the datetime column. Without an index performance would be aweful.

My first thought was a persisted computed column on the base table itself but you're using todays date in the calculation which will block that route. 

Will ponder on it.

[bcdobbs]

What range of dates do

you actually want in the direct query partition? Wondering if you can make it work by creating a persisted computed column in the base sql table but rather than use getdate

just substitute a static date a way in the future. May be over simplification depending on your specific requirements.

[RKM]

I think its less to do with volume of data or even index etc... Power BI simply doesn't allow calculated column in Hybrid Mode on it's IR Policy.  Upto simple incremental Load, its fine. I just tried producing the error for you in case you are interested....

~ Now coming to other point about "Persisted Computed Column" - Yes you are totally right. Since everything is current day based (which should be because end of day we want incremental refresh too for limited scope) that's why Persisted column /Indexed View etc. not option.

[bcdobbs]

Think our messages crossed! Ignore my last post.