The dataset size of Incremental refreshed report is increasing

[JosieGuo]

I have an incremental refreshed report and only keep 3 months data in the report. but after the daily incremental refresh ,the dataset size is increasing even though there are always at most 3 month + current month data here. 

at the begining, the dataset size only 15GB for 3 month + current month data. but now the dataset is more than 50 GB.

[v-karpurapud]

Hi @JosieGuo 

Thanks for reaching out to the Microsoft Fabric Community Forum.

The increasing size of your incremental refreshed report dataset, despite retaining only 3 months of data, may involve several factors. Here are the steps below to resolve the issue:

1. Ensure that  data retention policies are correctly configured. Sometimes, old data might not be deleted as expected.  If the configuration allows for more historical data than intended, it could lead to an increase in dataset size.
Here is the reference link to set up a retention policy:

Set a data retention policy for a table - Power Apps | Microsoft Learn
Manage data retention policies in Microsoft Dataverse - Power Apps | Microsoft Learn
View long term retained data in Microsoft Dataverse - Power Apps | Microsoft Learn

2. If the data source contains duplicates or historical changes are being reloaded unnecessarily, this can increase the size. Validate data source query to ensure it returns only the expected incremental data.
3.Incremental refresh relies on a date or datetime column for filtering. If the underlying database table does not have proper indexing, the query may pull unnecessary data. Ensure data source has an efficient index on the datetime column used for incremental filtering.
4.Use tools like Tabular Editor or DAX Studio to inspect your dataset partitions. Connect to the XMLA endpoint of your Power BI Service dataset and verify that only the partitions for the current three months plus the current month exist. If any partitions are found for older data, remove them manually or investigate why they aren’t being removed automatically.

Following these steps will help keep your dataset within the desired size while ensuring it remains accurate and up to date.


If you have any further questions or need additional help with this, feel free to reach out     to us for further assistance!

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[v-karpurapud]

Hi @JosieGuo 

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 my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[JosieGuo]

Hi @v-karpurapud ,

Thank you for your reply.

I have three questions now.

1. The dataset size keep increasing everyday. The size of every incremental report is very big.

2. My report is an incremental refreshed report, but the time of incremental refresh seems the same with full refresh. 

for example, when I set the following setting to keep 3 months data, my incremental refresh will cost more than 1 hour. if I set to keep 1 month data, my incremtal refresh will cost about 20 mins.

How can I check why the incremental refresh cost the same time with full refresh?

3. when I refresh this incremental report, sometimes it will be refreshed failed. what does this error mean?

[v-karpurapud · Accepted solution]

Hi @JosieGuo 

1. Yes, the data size of incremental refresh will be increased due to every day newly updated data will be added to the report. Because of this reason the size will be increased.
2. Incremental refresh is designed to save time and resources by refreshing only the partitions containing updated or new data, rather than refreshing the entire dataset. However, there are scenarios where incremental refresh might take as much time as a full refresh.

To check why the incremental refresh cost the same time with full refresh, follow these steps to investigate:

•  Ensure incremental refresh is properly configured with correct RangeStart and RangeEnd parameters.
• Use the Premium Metrics App to see which partitions were refreshed and identify resource usage.
• Reduce heavy transformations and push calculations to the data source if possible.
• Ensure the refresh policy doesn’t overlap large data ranges unnecessarily.
• Check if the source query retrieves only incremental data and is optimized with proper filters.
  
  3.The error "The operation was throttled by Power BI because of insufficient memory" occurs when Powerbi premium exceeds its allocated memory during dataset refresh. This issue can arise due to resource-intensive operations, large datasets, or inadequate capacity in the premium environment. If memory usage exceeds the limit, the refresh operation will fail.

If you find this post helpful, please mark it as an "Accept as Solution" and give a KUDOS.

[dbeavon3]

@v-karpurapud 

Can you expand on the part where you said: "inadequate capacity in the premium environment"?

Are you saying this is inadequate capacity is our responsibility or Microsoft's?  

We often encounter the error:
The operation was throttled by Power BI because of insufficient memory

.. even when refreshing a small 6GB dataset.   It seems like it is the fault of Microsoft and not of the customer.  We are supposed to be allowed to use 25 GB per dataset in a F64 capacity.

[v-karpurapud]

Hello @JosieGuo 

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-karpurapud]

Hi @JosieGuo 

Hope You are doing well!

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.

[TomMartens]

Hey @JosieGuo ,

check the dates available in your semantic model. You can also use DAX Studio to inspect the size of your model.

Regards,

Tom

[JosieGuo]

the available date only from 9/1/2024 to now, and next month the available date will be started from 10/1/2024. but the dataset size still increase.