Forum Discussion
Incremental refresh Setup with week start date column in fact table
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
9 Replies
- lbendlin
Super User
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
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.
- 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
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
