Automatic refreshes never pull in changes (UPDATED)

[markmsc]

Hello team -- I've had an issue that I have been curious about for a while, but which is now an actual problem.

We incrementally refresh our semantic model every 30 minutes using the services's scheduler.  As far as I know, scheduled refreshes perform a refresh type Automatic.  This works fine.

Occasionally, a user will ask to have new data brought into a visual ahead of when the refresh is scheduled to run.  In those cases, I'll refresh the needed table manually in TE3.  However, if I run an Automatic refresh that way, it never finds anything.  The dates on the polling criteria and the partition bookmark all are correct relative to each other and the change should be found, but the Automatic never detects it.  I have to run a Full refresh on the partition to get the data to come in.  Again, it would have come in fine via the scheduled refresh, had I waited.

The problem arises in that we are now moving away from the scheduled refresh to a pipeline-based, Enhanced API refresh that can run more frequently than every 30 minutes.  We have it configured to perform Automatic refreshes on the tables in the model, and again Automatic does nothing.  The model tables never get the new data in the database.

There has to be a way to programmatically (or manually) refresh a model table without doing a Full refresh of the data.  Why doesn't Automatic seem to work?  It doesn't work when run manually from TE3, and now it doesn't work in our own code.

UPDATE: Pulling this out of a reply I posted below.  It appears the core issue is that Automatic only works on partitions that are in a state other than Ready.  The partitions I am expecting to see changes in, in these tests, are all Ready and stay as Ready.  I'm not able to find a defintiion of what Ready means, and what might change it.  If anyone can point me to the answers there, it would greatly help solve my original problem.

[lbendlin · Accepted solution]

We incrementally refresh our semantic model every 30 minutes using the services's scheduler.  As far as I know, scheduled refreshes perform a refresh type Automatic.  This works fine.

That makes little sense as the smallest partition is a daily partition. You will want to look into keeping the "today"  partition in direct query mode.

Note: For your API refreshes you should replace "automatic"  with "full"  to force the refresh.

[v-kpoloju-msft · Accepted solution]

Hi @markmsc,

Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to @lbendlin,  for his inputs on this thread.

The behaviour you’re seeing is expected with Automatic refreshes. Power BI only reprocesses partitions that aren’t already marked as Ready. If a partition is in the “Ready” state, the service assumes it’s up to date and skips it even if the source data has changed. That’s why scheduled refreshes work later, but on-demand automatic ones appear to do nothing.

Detect data changes: Make sure your incremental refresh policy uses a proper change-tracking column (e.g., ModifiedDate). This tells Power BI which partitions need refreshing. https://learn.microsoft.com/en-gb/power-bi/connect-data/incremental-refresh-overview#detect-data-cha... 

Query folding: Verify that filters on RangeStart and RangeEnd are folding to the source. If not, incremental logic may skip updates. https://learn.microsoft.com/en-gb/power-bi/connect-data/incremental-refresh-troubleshoot 

Partition states: If partitions stay in “Ready,” Automatic refresh will skip them. You can confirm this via XMLA endpoint or Tabular Editor.

If you need data immediately, you can: Trigger a Full or Data Only refresh via the REST API instead of “Automatic.” Or update the change-tracking column in the source so the partition is re-evaluated. https://learn.microsoft.com/en-gb/rest/api/power-bi/datasets/refresh-dataset 

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[markmsc]

Thank you very much for your detailed reply.  I think our scenario meets all the detailed prerequisites you mention.  As you suggeted, as did another user here, we have changed the API refreshes to Full.  Please see my second reply to @lbendlin in this topic for more context on that.  I marked that person's post as Solution and I wiill do the same here as you also recommended changing the refresh type to Full.

[v-kpoloju-msft]

Hi @markmsc,

Just checking in to see if the issue has been resolved on your end. If the earlier suggestions helped, that’s great to hear! And if you’re still facing challenges, feel free to share more details happy to assist further.

Thank you.

[v-kpoloju-msft]

Hi @markmsc,

Just wanted to follow up. If the shared guidance worked for you, that’s wonderful hopefully it also helps others looking for similar answers. If there’s anything else you'd like to explore or clarify, don’t hesitate to reach out.

Thank you.

[markmsc]

Thanks for your reply!

I'm a bit unclear on what you mean by your first sentence.  What makes "little sense"?

We can't use DQ for the today partition for a couple of reasons.  The incremental period  for most of the tables in our model is five days, not just one ('today").  Also, we make extensive use of DAX functions in the model that are not available in DQ mode.  It would require an extensive refactoring of the model to shift it to a hybrid model from import only.  

I would prefer not to have to use Full, also for a couple of reasons.  One, it is brute force, and refreshes data whether it is needed or not.  Doing a Full refresh of all tables in the model might take longer on each pass than just running a schedueld incremental every 30 minutes, defeating the purpose.  And to Full refresh only the partitions that have changed would be pretty burdensome, as I think, we'd have to essentially code the polling that the refresh already does to know which ones have changed.

From some more reading, I think the crux of the issue is that Automatic only does something if the partition is in a state other than Ready.  All the partitions I am expecting to see changes in are just sitting there in Ready.  I have no idea what makes a partition not Ready.

UPDATE: Perhaps part of my misunderstanding here is with how Full works.  With Full on a specific partition, which is typically what I will do manually, I think it just refreshes the partition regardless of whether there are changes.  Or if it doesn't, I never catch it doing the analysis step. But with Full on a table, I can see it doing the analysis step on each partition in the update window.  So yes, perhaps Full is what I need to be doing in my code.  I will change to that and test it.

[lbendlin]

I think the crux of the issue is that Automatic only does something if the partition is in a state other than Ready. 

Pretty much.  "automatic" means "let the Power BI service decide if it wants to honor your refresh request".

[markmsc]

Thanks for replying.  This made me laugh.  You seem like a properly jaded Power BI developer!  🙂

I agree with your sentiment entirely.

[lbendlin · Accepted solution]

We incrementally refresh our semantic model every 30 minutes using the services's scheduler.  As far as I know, scheduled refreshes perform a refresh type Automatic.  This works fine.

That makes little sense as the smallest partition is a daily partition. You will want to look into keeping the "today"  partition in direct query mode.

Note: For your API refreshes you should replace "automatic"  with "full"  to force the refresh.

[markmsc]

I've delayed following up because I wanted to monitor things for a few days.  And I have never, not once, seen even the dirtiest of partitions be marked as anything other than "Ready".  I really can't imagine a scenario where Automatic would actually do anything.

This is puzzling, because I was told her on the forum some ago that the service's scheduled refreshes performance an Automatic.  That can't be true, seemingly, or our scheduled refreshes would never actually refresh a table -- but they do.  

I have changed everything to Full as you suggest, and which is clearly the correct answer, and our API refreshes are working now.  It's odd though: it feels like the scheduled refreshes aren't doing Full either, simply because a scheduled refresh can update our entire 30+ table model in not too much more total time than one of our programmatic API refreshes can do a more narrowly-targeted ~5 tables.  

Frustrating behavor overall, but at least our targeted refreshes are working now.