Forum Discussion
How does Incremental refresh behave (in PRO) with multiple schedules in relation to subqueries
Hi C4YNelis ,
Since each table's incremental refresh policy is independent, the subqueries will extract data according to the specific `RangeStart` and `RangeEnd` parameters set for each table. Therefore:
- The stock data subquery will extract data for the last 2 years.
- The sales history subquery will extract data for the last 7 years.In summary, the data extracted from your SQL database will be 2 years for stock data and 7 years for sales history, based on the respective incremental refresh policies.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi C4YNelis ,
Since each table's incremental refresh policy is independent, the subqueries will extract data according to the specific `RangeStart` and `RangeEnd` parameters set for each table. Therefore:
- The stock data subquery will extract data for the last 2 years.
- The sales history subquery will extract data for the last 7 years.
In summary, the data extracted from your SQL database will be 2 years for stock data and 7 years for sales history, based on the respective incremental refresh policies.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
thank you for your quick answer. That is very nice indeed.
If you don't mind, what I'm still wondering about is: how does Power BI decide which subquery should utilise which incremental schema, when things are less straight forward. Does it use data lineage to track which data it would need to satisfy the needs of the reportqueries?
For instance, suppose:
- subquery 1 (for stockhistory) does nothing other than extract the data, which it is supposed to do for only 2 years of data (as little as possible), and
- the sales history subquery (2) will only extract data for sales, just 7 years worth of data, yet
- somewhere during the ETL process part of the stockdata subquery would be merged into a sales transformation subquery and
- the stock history has it's own straightforward transformation subquery. (see picture below).
Would Power BI now start extracting seven years worth of stock history data, instead of just two, or would this result possibly in blank rows on the sales history tabled loaded to report?
How many years of data would the stock history subquery load?
Thank you again for your time!
Cheers,
Niels
Hi C4YNelis ,
The data is pre-processed in Power query and then loaded into PBI Desktop according to your choice, which is an irreversible process. You can then right-click incremental refresh to set incremental refresh on the table, and the retention and refresh of the data depends on your settings. You can see it in the diagram.
Regardless of whether table c merges tables A and table B, if table c is loaded into PBI Desktop, then it exists as a separate table. His refresh depends on your settings. For example, if you select Table C in the image, the incremental refresh policy of Table C depends on your input.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
thank you for your clarification. My question is not so much about the report tables (the ones loaded to report), that much is clear to me.
I was specifically wondering how Power Query treats the subqueries. Since they all share the same parameters (at least, in name), yet there are multiple different refresh schedules, how does Power Query choose which schedule to apply to which subquery?
Like in your example, merging A and B into C, but A is also a source for table D (loaded to report). Suppose C refreshes incrementally retaining 7 years of data, with an incremental partition of one week, yet D only retains 1 year of data, but with an incremental partition of one month.
I'm assuming that in case of such a merge, Power BI will refresh as much data as needed. So subquery A will now also (initially) extract 7 years of data (basically, conform the maximum of any refresh schedule of their respective tables it would contribute data to) and one month incrementally?
Thank you again!
Cheers,
Niels
