Forum Discussion
Incremental Refresh or alternative
Without a full understanding of the ETL/Database etc. it's difficult to say, but the lastUpdate DT will only cause the data to be refreshed if that date is within the defined incremental refresh period. E.g. if your incremental period is set to 1 month, it also needs to have changed from the last cached version of that date.
With the access you have, I would probably look at simplifying the data in the database before Power BI connects. Always "As far upstream as possible..."
If you haven't read it already...
The other thing to consider, what is the performance like now, and how quickly is the data growing?
You may have a working solution for the foreseeable future. You can always make a plan to revisit later.
Thank you for your quick response.
In stage table the rows are hard deleted and also hard deleted later in the etl process, but since I can control that I can make it a soft delete when doing the crud operations, so the final table have a updated column and a deleted datetime column.
But won't this create whole lot of rows for the current month, since there's a lot of changes within the current month?
Ideally would be if say current month and previous two would be truncate and load in PBI but older months would be handled with detect data changes at refresh. Or to do some kind of union in power query where one table is 'not refreshed' on daily basis but on monthly basis, but I guess that's not possible...?
Without a full understanding of the ETL/Database etc. it's difficult to say, but the lastUpdate DT will only cause the data to be refreshed if that date is within the defined incremental refresh period. E.g. if your incremental period is set to 1 month, it also needs to have changed from the last cached version of that date.
With the access you have, I would probably look at simplifying the data in the database before Power BI connects. Always "As far upstream as possible..."
If you haven't read it already...
The other thing to consider, what is the performance like now, and how quickly is the data growing?
You may have a working solution for the foreseeable future. You can always make a plan to revisit later.
Thanks for your clarifications!
As I wrote in the initial question, it's not really a problem today cuz the data isn't growing that fast. But it will be a problem within a year or two, and now I have an idea how to move forward.
Thanks