Forum Discussion
Partitions of Incremental Refresh Deleted When Adding New Columns Using ALM Toolkit
Hello,
I have a very large fact table that uses incremental refresh, but I'm unable to run the initial refresh after publishing to the Power BI service due to a timeout issue with the data source. Here’s what I’ve done so far:
- Configured incremental refresh in Power BI Desktop (5 years of historical data + incremental refresh for 1 month).
- Published the model to the Power BI service.
- Connected to the published model using XLMA endpoint and SQL Server Management Studio (SSMS), created monthly partitions, and processed them one by one to load 5 years of historical data.
Now, I need to add new calculated columns and a new column from the source. My plan is to:
- Modify the local report in Power BI Desktop.
- Update the published model using the ALM Toolkit.
The challenge I'm facing is that while the ALM Toolkit works effectively for adding new measures—retaining the partitions and historical data—when I add new columns (whether calculated or not), it removes all the partitions I set up in SSMS and deletes all the loaded data.
I’ve tried adjusting the ALM Toolkit options as follows:
- Checked "For table updates, retain partitions."
- Checked "Retain only refresh - policy based partitions."
- Checked "Display warning for measure dependencies."
- Set "Process only affected tables."
- Selected the processing option: "Do not process."
Do you know how I can update tables with new columns while preserving the partitions and the historical data that has been loaded?
Thank you!
Hi VanThuan
When you add new columns to a table with incremental refresh, Power BI sees it as a structural change. That forces the whole table to be rebuilt, so all partitions get dropped and your historical data is lost. ALM Toolkit’s “retain partitions” option only works for things like measures or metadata, not for schema changes.
If you want to keep your old partitions, here are some options:
-
Add the column directly in the Service model using XMLA scripting and then only process the new partition.
-
Back up your partitions with Tabular Editor/SSMS, make the schema change, and then restore them.
-
Or, if that’s too complex, plan for a one-time full refresh after the change.
In short: adding columns always resets partitions, so you need either scripting or a backup/restore approach if you want to keep historical data.
-
5 Replies
- lbendlin
Super User
I don't think you can do that. It's too much of a meta data change and it would introduce inconsistencies across all partitions. Your fastest option may be to create a new table with new partitions, fill these with bootstrapping and then swap the table in for the old table.
rohit1991Hi VanThuan
When you add new columns to a table with incremental refresh, Power BI sees it as a structural change. That forces the whole table to be rebuilt, so all partitions get dropped and your historical data is lost. ALM Toolkit’s “retain partitions” option only works for things like measures or metadata, not for schema changes.
If you want to keep your old partitions, here are some options:
-
Add the column directly in the Service model using XMLA scripting and then only process the new partition.
-
Back up your partitions with Tabular Editor/SSMS, make the schema change, and then restore them.
-
Or, if that’s too complex, plan for a one-time full refresh after the change.
In short: adding columns always resets partitions, so you need either scripting or a backup/restore approach if you want to keep historical data.
-
v-echaithraCommunity Support
Hi VanThuan ,
May I ask if you have gotten this issue resolved?If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Regards,
Chaithra E. - v-echaithra
Hi VanThuan ,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra. - v-echaithra
Hi VanThuan ,
We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.Regards,
Chaithra.
