Incremental Refresh isn't dropping old data

[Don-Bot]

I setup a small incremental refresh as a test of incremental refresh and to try and speed up how this semantic model updates.  I set it to archive data after 32 days and to update everything in the last 32 days.  I then also checked the "Only update data that has changed" so that within those 32 days it would only update what has changed.

I was under the impression that after 32 days it would simply drop what expired.  Thus never increasing the size of my model beyond that.  The test seemed to go well and I left at that.

However, I came back to my model recently as it had grown to over 23 gig (32 days of data is only 3 gig).  I looked at the partitions stored and there were only 32 days of partitions stored.  In the model itself I can only see 32 days back.

So I went back and simply uploaded a brand new model and overwrote the one in the service.  Which I am able to do as my incremental was really only there for testing purposes.  When I did that voila.. the size of my model went back down to 3 gig.  

So what did I do wrong?  Why did my incremental refersh go so large?

[eudesmcf]

@Don-Bot
Is the workspace registered as Large Semantic Model Storage Format? Workspace Settings > License Info > Semantic Model Storage Format

Can you show a print of your table partitions?

[eudesmcf]

@Don-Botare you see the partitions of semantic model? Try use some external tool (TE, Dax Studio, SSMS) for you see the partition, this probably help you for understand whats happening.

[Don-Bot]

Thanks @eudesmcf , I used Tabular Editor and could see only the partitions that should be there.  I couldn't see any of the phantom partitions that were taking up all the data.  When I overwrote the model with the fresh version it had the exact same partitions.  Just went from 23 gig to 3 gig.

[Anonymous]

Hi @Don-Bot 

Thank you very much AlexisOlson  for your prompt reply.

I noticed you've already submitted your support ticket. If there is any result, look forward to your sharing!

If the problem has been solved, you can accept it as a solution.

Regards,

Nono Chen

[AlexisOlson]

The whole point of incremental refresh is to store data that doesn't need to get refreshed every time.

Incremental refresh for semantic models and real-time data in Power BI - Power BI | Microsoft Learn

If your archive window is the same as the incremental window, then incremental refresh doesn't gain you anything. Keep it simple and set up a basic refresh that always pulls the last 32 days.

[Don-Bot]

@AlexisOlson  Yes, I know how incremental is "supposed" to work. 

Actually this "incremental" refresh does accomplish what i want.  It doesn't refresh the entire dataset the entire time.  Just the days that have been updated.

My question is, why isn't it dropping data?  The dataset grows when it shouldn't.  

This error makes me think my other incremental updates (that actually are setup to store historical data) aren't dropping data either and growing more than they should as well.

[AlexisOlson]

It does seem like it should be dropping the older data. Maybe it has to do with the way it partitions the data? If the data you want to drop is in the same partition as some of the data you still want, maybe it just keeps the whole partition.

This text from the documentation I linked previously makes me think it's preserving more than strictly the period you specify:

The Archive data starting before refresh date setting determines the historical period in which rows with a date/time in that period are included in the model, plus rows for the current incomplete historical period, plus rows in the refresh period up to the current date and time.

For example, if you specify five years, the table stores the last five whole years of historical data in year partitions. The table will also include rows for the current year in quarter, month, or day partitions, up to and including the refresh period.

[Don-Bot]

I opened a support ticket with Microsoft on this.  I have 2 Semantic Models that I know this has occurred on and then I have 2 models that are actual incremental models that are too big for me to delete and then upload again that I'm wondering if this is occurring on them as well.

The 2 I know this is happening are models of 3 gig and 1.5 gig that have grown to 23 gig and 10 gig.  THey should only have 32 days of data in them.  And I can only see 32 days of data.

Very strange to me.  Maybe support will be able to answer what is going on.  But this makes me rather paranoid.  Since I only discovered this because I was getting out of memory errors on this.  Which made me think I needed to buy a new capacity.