Forum Discussion
table refresh doesn't find changes but partition refresh does
Hello colleagues. I have a Power BI premium model. Some of the tables are quite large, but have the advantage that once a new day starts, much of the prior day's data can be removed. We have a daily script that runs early each morning to mark the rows that can be removed on the first model refresh of the day.
The IR filter criteria include the "delete me" flag in addition to the usual RangeStart and RangeEnd parameters, resulting in the proper dataset for that date. The mark-for-delete script also tickles the datatime column we use for Detect Data Changes, so the IR process will see that the row has changed.
We partition based on business day and since the data to be removed each day is from "yesterday", this process should update the prior day's data partition. And it has been working fine for a couple of months now. Each day, our update script runs, and a short while later the first IR of the day kicks off and drops the marked data from the model.
But this has recently stopped working. The IR process is ignoring the marked rows, and they are remaining in the model, making these tables too large. Manially refreshing the tables in question does not help. I can even see the message "no data changes detected' in the partition with the rows marked to be deleted. However, if I manually refresh just the partition itself, and not the whole table, then it works fine, and the marked rows drop out, just as expected.
Any thoughts on what is going on here? Why did it stop working with no changes on our part, and why does it still work as expected if I refresh the partition but not the table (all partitions)?
Hi markmsc ,
Can you please share an update to your issue and let us know if you were able to resolve it by using azure pipeline-orchestrated XMLA refresh of the model, as this seems to be the best possible workaround currently available.
Sharing an update and accepting the reply which may have helped you in the direction will surely help other community memebers who may experience the same error in the future.
Thank you
19 Replies
Are you doing a "full" refresh on the table. If not then only active partitions will be touched. Make sure the partition in question is definitely in the active scope, also check the bookmark on the partition and make sure your more recent data has a different value, to ensure it is not being skipped
Hello Deku and thanks for replying.
The scheduled IR's are run by the service and I assume that is doing full, but I don't know since I don't think that is configurable. But if I refresh a table myself (using TE3), then yes, I am running a full. The partition in question is definitely in the active scope. Our active period is five days and the archive period is five days before that. The partition in question is "yesterday", so well within the active scope.
I'll double check the bookmarks on the partitions in question.
Hi markmsc ,
You can try this -
Schedule a partition refresh (instead of the full table) via the XMLA endpoint or Power Automate.
If feasible, consider switching to a delete + append strategy instead of flagging rows for deletion.
I hope it helps.
Thanks,
Sai Teja
Hello SaiTejaTalasila and thank you for replying.
In the next couple of months, we will be moving to an Azure pipeline-orchestrated XMLA refresh of the model so will have the opportunity to do more granular things, in the spirit if your Power Automate suggestion, pretty soon.
As for delete + append, I assume you are referring to deleting the rows from the physical table? If so, we do not want to delete the rows from the database, only from the model after the business day to which they apply has passed. Hence the mark-for-delete strategy we have used. And again, that was working fine for the past couple of months after we put it in; it only stopped working this week for some reason. 🤔
Hi markmsc,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi markmsc,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.Hello. No, the problem is not solved. I will mark helpful posts with a thumb but I can't mark any as the solution.
Hi markmsc,
We really appreciate your efforts and for letting us know the update on the issue.
I’ll continue exploring alternative solutions in the meantime, feel free to keep using the Fabric Community Forum for additional support and guidance.
Thank you
Hi markmsc ,
Can you please share an update to your issue and let us know if you were able to resolve it by using azure pipeline-orchestrated XMLA refresh of the model, as this seems to be the best possible workaround currently available.
Sharing an update and accepting the reply which may have helped you in the direction will surely help other community memebers who may experience the same error in the future.
Thank youHello -- No, this issue is not resolved. I expect that switching to a pipeline-orchestrated XMLA refresh will take care of it. However, this is an enterprise application and IT budgets and blah blah, and I cannot simply implement this on a whim, unfortunately. The change is scheduled to be done later this year. Until then, I cannot mark that suggestion as the solution, although I fully expect it to work.
Hi markmsc ,
Thank you for providing an update on the issue.For now we are closing this thread should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread or reopen this thread. We’ll be happy to help.
Thank you for your understanding and participation.
