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