Don't miss your chance to take the Fabric Data Engineer (DP-700) exam on us!
Learn moreNext up in the FabCon + SQLCon recap series: The roadmap for Microsoft SQL and Maximizing Developer experiences in Fabric. All sessions are available on-demand after the live show. Register now
Hi,
Just got acquainted with the incremental refresh functionality - and I understant it´s a way to archive some data and only refresh the data that is changing. What I do not understand is the purpose of the RangeStart and RangeEnd parameters. From the videos I have seen these parameters seem unrelated with the data you are going to archive, or the data you are going to refresh. I chould choose range start 1/1/2022 and range end 31/12/2022. But then in the incremental refresh chose to archive the last 5years and refresh the last 1 year.
Perhaps I misunderstood the videos..
Solved! Go to Solution.
It's a valid question. Here is what is happening:
1. you want to implement a process where you keep a certain amount of data (say, "last12 months") but ony want to update a smaller amount (say, "last seven days"). This is what you specify in the incremental refresh settings for your fact table. Now the Power BI Service takes your instructions (last 12 month/last 7 days) and translates that into layout for partitions - data storage containers that can be refreshed individually. The Power BI service does this automatically, creating daily partitions, monthly, quarterly and yearly partitions as needed. Once a higher level has passed (for example at the end of the month) the lower level partitions may be combined into a higher level partition.
Now you have said "last 7 days" but if you look at the partition layout you will see that the service will keep all daily partitions around until they can be consolidated.
2. To identify what data has to go into each partition you have your RangeStart and RangeEnd parameters. They need to be part of the query against the data source, and they need to be set in a way that partitions don't overlap. Meaning one of the parameters must be inclusive and the other exclusive. Come dataset rrefresh time, the Power BI service will automatically fill these parameters with the required values for each partition, ignoring the default values you have set in Power Query.
3. Now why specify these values if they are ignored in the service? See it as a feature that allows you (the report developer) to restrict the amount of data to use for development without impacting the report data on the service.
That's it in short. There are many more aspects to this. Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn
It's a valid question. Here is what is happening:
1. you want to implement a process where you keep a certain amount of data (say, "last12 months") but ony want to update a smaller amount (say, "last seven days"). This is what you specify in the incremental refresh settings for your fact table. Now the Power BI Service takes your instructions (last 12 month/last 7 days) and translates that into layout for partitions - data storage containers that can be refreshed individually. The Power BI service does this automatically, creating daily partitions, monthly, quarterly and yearly partitions as needed. Once a higher level has passed (for example at the end of the month) the lower level partitions may be combined into a higher level partition.
Now you have said "last 7 days" but if you look at the partition layout you will see that the service will keep all daily partitions around until they can be consolidated.
2. To identify what data has to go into each partition you have your RangeStart and RangeEnd parameters. They need to be part of the query against the data source, and they need to be set in a way that partitions don't overlap. Meaning one of the parameters must be inclusive and the other exclusive. Come dataset rrefresh time, the Power BI service will automatically fill these parameters with the required values for each partition, ignoring the default values you have set in Power Query.
3. Now why specify these values if they are ignored in the service? See it as a feature that allows you (the report developer) to restrict the amount of data to use for development without impacting the report data on the service.
That's it in short. There are many more aspects to this. Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Learn
If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.
A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.
Share feedback directly with Fabric product managers, participate in targeted research studies and influence the Fabric roadmap.