Regarding comparison of Full & Incremental Refresh in Power BI Service

[MangeshVC]

Hi All.

    We have an MS SQL Server database, and the volume of data getting added daily is of the order 150,000 to 200,000.

    We have used Stored Procedure to aggregate the data at the DB and then created the model at Power BI.

    After aggregation the data size reduced by approximately 80-90%.

    When we have a scheduled refresh(Full Refresh) at Power BI Service, it took around 10-15 minutes.

Then for testing purpose we have created a model for an Incremental Refresh(Entire table as it is, without any aggregation). When the same model is set for scheduled refresh, Initial full refresh took around 2 hour.

But then each incremental refresh took the similar time i.e. 10-15 minutes. 

Now can I comment that the approach of Aggregating the data at Data source using the Stored Procedure, is equivalent to the approach of Incremental Refresh for large data size.

Please Guide. 

[GilbertQ · Accepted solution]

Hi @MangeshVC 

The reason that this happens is when you are doing an incremental refresh, it's got to create the partitions, then load the partitions with data and then add those partitions to your existing model and all of that does take some time. So in your instance it appears to be just as much as the same time, but in larger models the incremental refresh can make the overall refresh significantly quicker.

[v-pbandela-msft]

Hi @MangeshVC,

Thank you @rohit1991  for the helpful response.

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  as  Solution" and give a 'Kudos' so other members can easily find it.

Thank you,
Pavan.

[v-pbandela-msft]

Hi @MangeshVC,

Thank you for reaching out in Microsoft Community Forum.

Thank you @GilbertQ  for the helpful response.

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

Please continue using Microsoft community forum.

Regards,
Pavan.

[rohit1991 · Accepted solution]

Hi @MangeshVC ,
Your approach of using a stored procedure to aggregate data before a full refresh in Power BI Service may yield similar refresh times to an incremental refresh for your current dataset size, but they are fundamentally different in how they process and store data.

1. Stored Procedure + Full Refresh: This method reduces data volume significantly (by 80-90%) before it reaches Power BI, leading to faster processing and lower storage requirements. Each refresh reloads the entire dataset, meaning performance depends on how efficiently the database aggregates data before sending it to Power BI.

2. Incremental Refresh: The first refresh is slow because it loads all historical data into partitions. Future refreshes only process new or changed data, reducing overall query load on the database and improving scalability over time.

While both methods show similar refresh times in your case (10-15 minutes), incremental refresh becomes more beneficial as data volume grows because it avoids reloading unchanged historical data. If your dataset keeps increasing, incremental refresh will scale better, whereas full refresh might become slower as the dataset grows. However, if you prioritize storage efficiency and fast aggregation at the database level, your stored procedure approach is still a strong alternative. The choice depends on whether you need long-term scalability (incremental refresh) or an optimized, aggregated dataset (stored procedure).

[GilbertQ · Accepted solution]

Hi @MangeshVC 

The reason that this happens is when you are doing an incremental refresh, it's got to create the partitions, then load the partitions with data and then add those partitions to your existing model and all of that does take some time. So in your instance it appears to be just as much as the same time, but in larger models the incremental refresh can make the overall refresh significantly quicker.