Forum Discussion
Scheduled Refresh Global Failure
Super UserAzure SQL Server is not a genie. It has its limits. And it sounds like you are pushing those limits in your two hour window. Have you tried scaling up the Azure SQL Database(s) to a larger size? I would start with something *really big*, like rediuculously big, to ensure it has enough DTU / IOPS to handle the load. If that works, then scale it back until you start to see timeouts.
Another option: Try performance tuning your Power BI queries and the indexes and tables that support them. Have you run a Power Query SQL Statement in SSMS and analyzed the Actual Execution Plan for trouble spots?
I don't believe we have analyzed using a Power Query SQL statement. I'll share this with our development team. As far as the optimization methods, initially when we designed our deployment method, we didn't envision the reporting ecosystem growing this large this fast. Over the past 6 months we have been very cognizant of indexing and performance optimization. However, we have refrained from consolidating datasets to this point. Obviously reducing the number of datasets that need to refresh daily would alleviate this issue but, this would require an entire redesign of our deployment method and Power BI organizational structure.
I don't believe we have analyzed using a Power Query SQL statement. I'll share this with our development team. As far as the optimization methods, initially when we designed our deployment method, we didn't envision the reporting ecosystem growing this large this fast. Over the past 6 months we have been very cognizant of indexing and performance optimization. However, we have refrained from consolidating datasets to this point. Obviously reducing the number of datasets that need to refresh daily would alleviate this issue but, this would require an entire redesign of our deployment method and Power BI organizational structure.
