Refreshing Power BI reports when on-premises data sources are in use through the Power BI Gateway gets refreshing limits quickly. Here are some very important strategies to rightly manage the data refreshes and optimize them:
Optimize Queries and Data Models: The queries in Power Query should actually work efficiently. A complex query or unnecessary transformations may slow the refresh process. If possible, divide large queries into smaller, manageable parts. Filter data before it ever reaches Power BI so that you don't have to work with unnecessary amounts of data.
Incremental Refresh: The data refresh in Power BI takes place with the help of the increment refresh; the data refreshed changes rather than the whole dataset. Such refreshing would definitely take less time and load on the gateway. This works very efficiently for large datasets, wherein a limited portion of data gets updated too frequently.
Scheduled Refresh: Heterogeneous Refresh Schedule: Different refresh schedules should be staggered for reports that do not need to be updated currently. Rather than refreshing all reports simultaneously, have them refresh intermittently. This reduces strain on the gateway and ensures that the system will not crash.
Monitor the Performance of Gateway: You ensure the well-maintained Power BI Gateway. Regularly monitor the gateway's performance, check for bottlenecks, and stay updated with them. If this system is heavily loaded, scaling the gateway by adding more nodes or moving the whole server to a more robust server handling bigger loads is also advisable.
Optimization of Data Sources: The optimization of data sources is for databases like SQL Server or other accessible on-premise sources. Indexing is for query optimization, and sometimes, tables are partitioned to ease the load on the gateway while refreshing.
Utilize DirectQuery or Live Connection: For some use cases, rather than using imported data, direct links or live connections to your on-premises data sources are recommended. This means that the data is accessed in real time, thus reducing the need for frequent refreshing.
No Overlapping of Gateway Refreshes: Most datasets refresh all at the same time, which incurs throttling issues. They should thus be staggered to reduce the number of datasets refreshing at the same time to avoid the exceedance of the gateways in the message of refresh.
The aforementioned can be applied to effectively determine the refresh process, thus avoiding hitting refresh limits and regular data updating without manually doing it.
