Power BI Server - Refresh failing in random manner for same data set via ODBC connection

Hi KMSudharsan ,

The issue you're facing with intermittent refresh failures on Power BI Server via an ODBC connection can stem from several factors. Let’s address your concerns systematically and provide solutions.

1. Possible Causes for Random Refresh Failures

a) ODBC Connection Stability

• Driver Limitations: ODBC drivers sometimes have limitations with concurrent or repeated requests.
• Timeouts: The ODBC connection might time out due to server overload or poor network connectivity.

b) Data Source Limitations

• Your data source might have constraints on the number of simultaneous queries or connections it can handle.

c) Power BI Server Settings

• Frequent refreshes may cause resource contention, especially if multiple datasets are being refreshed concurrently on the same server.

d) Refresh Queue

• Back-to-back refreshes for the same dataset could collide, as Power BI Server places refresh requests in a queue, and conflicts may arise if one is still running while another starts.

e) Data Volume or Query Complexity

• Large datasets or complex queries could sometimes lead to intermittent timeouts, especially if the data source server is under heavy load.

2. Power BI Server Refresh Limitations

a) Back-to-Back Refreshes

• While Power BI does not impose a hard restriction on how frequently you refresh, practical limitations arise from:
  • Dataset size: Larger datasets require more time to process.
  • Query complexity: Intensive queries may lock resources.
  • Concurrency: Server resources are shared across all scheduled and on-demand refreshes.

b) Minimum Time Between Refreshes

• While no official minimum exists, it’s advisable to allow sufficient time between refreshes for:
  • Data source recovery.
  • Query execution to complete.
  • Resource availability on Power BI Server.

3. Recommendations and Solutions

a) Optimize Data Queries

• Simplify queries used in the dataset.
• Minimize transformations in Power Query and move them to the data source if possible.

b) ODBC Driver Optimization

• Ensure you are using the latest version of the ODBC driver.
• Check the driver settings for any timeout configurations and increase them if needed.
• Verify the driver’s compatibility with your data source.

c) Schedule Refreshes Appropriately

• Avoid back-to-back refreshes. Introduce a buffer time between refreshes, allowing one refresh to complete before triggering another.

d) Monitor Server Performance

• Use the Performance Monitor in Power BI Report Server to identify bottlenecks during refreshes.
• Look for high memory or CPU usage, which might indicate server overload.

e) Retry Logic

• Enable retry logic in the Power BI Gateway settings (if applicable).
• This allows failed refreshes to retry automatically after a brief interval.

f) Reduce Data Set Size

• Use incremental refresh to limit the amount of data being processed during refreshes.
• Split large datasets into smaller, manageable datasets if possible.

g) Check Logs for Failures

• Review the Power BI Server logs (RSPowerBI or RSLog logs) to identify the exact cause of failure.
• Common errors include:
  • Timeout errors.
  • Connection interruptions.

h) Connection Pooling

• Enable connection pooling in the ODBC driver settings to reduce the overhead of creating new connections for every refresh.

4. Alternative Approaches

a) Use a Data Gateway

• If the data source is external, consider using an On-Premises Data Gateway for more robust handling of refreshes.

b) Stagger Refresh Times

• If multiple datasets are being refreshed, stagger the refresh schedules to avoid server overload.

c) Error Handling

• Implement error-handling mechanisms to capture failures and alert administrators for immediate resolution.

5. Testing Suggestions

1. Test the refresh process manually for multiple attempts to identify the failure point.
2. Simulate heavy load on the data source to see if it impacts refresh stability.
3. Try connecting via a different driver or data source method to rule out driver-related issues.

Summary

To prevent random refresh failures:

• Optimize queries and drivers.
• Introduce a buffer between refresh attempts.
• Monitor server performance and logs.
• Stagger refresh schedules and reduce dataset size where possible.

If the problem persists, investigate the server logs or escalate to Microsoft support with the error details. Let me know if you need help with any specific part of the troubleshooting process!

Please mark this as solution if it helps you. Appreciate Kudos.