Incremental refresh Setup with week start date column in fact table

[Anonymous]

Hi All,

I have a fact table with Sales_week_start_date with date/time data type. I have the below sample data

Sales_week  Sales_week_start_date

 202449       12\01\2024

202450        12\08\2024

I have setup incremental policy as below as my backend source gets loaded with data on every monday(weekly)

I have run the full load on 15th Dec. At that time I have data upto 202449. I scheduled my refresh on every tuesday. So next incremental refresh would be on 17th dec. By that time I should have data at my backend (as data is there by 16th).
What period should it consider in my 1st incremental refresh?
How it calculated the dates. Will it check the calendar dates or my sales_week_start_date?
if it is calendar dates then how the relation applies to my fact date column and calendar ?
as refresh period is 7days will it checks the data from 11th to 17th? or how exactly
Can anyone explain it?

@v-xinc-msft  

[pallavi_r]

Hi @Anonymous 

So as per the below records, you did a full load on 15th, so the first record is present in dataset.

Now when your 2nd record has arrived in the source system and you do a incremental refresh on 17th, suppose this sales_week_start is the data column you have used in range filters/parameter, it will take today's date (17-12-24) as end date, today()-7 (11-12-24) as start date. So its going to pick up the record which has sales week start date begining from 11-dec to 17th dec.

Then there's a condition if you select detect data changes, which needs to be a separate column, say - 'modified date' other than Sales Week Start Date. It will capture only those records with different modified date not already present in dataset.

Sales_week  Sales_week_start_date

 202449       12\01\2024

202450        12\08\2024

Please let me know if you need more clarification or still have any doubt as I have myself done these thorough check and testing for my project earlier. I will answer.

If this post helps, please accept this as a solution. Appreciate your kudos.

Thanks,

Pallavi

[Anonymous]

Hi Pallavi,

I scheduled the refresh in my Test and Prod workspaces. I can see the refresh history as completed without any errors in both test and prod. But I can see the 202450 data (sales weeks start date :dec 8th) in my test environemnt but I didn't see any new records in my prod. But when I refreshed it manually from SSMS then I could see 202450 data in prod also. I haven't given any detech changes option also. If it checks the sales week start date it is not falling under last 7days from 17th Dec. That is the reason I have asked the question above. Something is missing here.

[pallavi_r]

Hi @Anonymous ,

As per the logic of 7 days, if you refreshed it on 17th of Dec, then it will pick only those records where sales_week_date is between 11-dec and 17-dec. 

Refreshing from SSMS is okay, it will refresh current, historic all the data residing in a partition. 

As per your incremental setup logic, it should not pick up for 12/08.

If this post helps, please accept this as a solution. Appreciate your kudos.

Thanks,

Pallavi

[Anonymous]

Hi Pallavi,

I am trying to see the dates partitions in SSMS after my scheduled refresh but I am seeing differnt dates. Sometimes it is taking 8 or 10 or 13 days but not same all the time. Is there any possibility to connect with you? I even showed the same to Microsoft platform team. They are also verfying the same from their end how the dates are being calculated.

[lbendlin]

7 days is only a guidance. The Power BI Service will decide by itself how many partitions to keep.  You will see up to 37 daily partitions if you choose "7 days" - after the end of the month plus the "hot period" they will be consolidated.  As I suggested, use monthly partitions  for less mess.  

[lbendlin]

Please read through the documentation.  It's quite a bit more complex than that.

Incremental refresh for semantic models in Power BI - Power BI | Microsoft Learn

[Anonymous]

Thank you for your response. I have already gone through it. But there they have given example based on a date field which is continuous, we can consider calendar and that dates are in sync.So we can understand it easily. But in my case it is weekly data that too with 1 week delay dates. So I am little bit worrying on this.

[lbendlin]

Incremental refresh does not support weekly partitions. Your choices are Day, Month, Quarter and Year.

Use the first day of the week to drive the daily partitions, or use monthly partitions.

[Anonymous]

Yes. We don't have week option there. But my data gets loaded weekly once at the source, I need to refresh my report weekly.So I have given 7 days. Then what period should I give in the incremental refresh data.