Scheduled Refresh Global Failure

[KyleD]

Hi,

I work for a mid-sized financial institution, and we maintain around 450 Power BI reports in our ecosystem. These are split up between 42 workspaces, and each report is one-to-one with a dataset. The total size of all datasets is under 100GB averaging around 2GB per workspace, with the largest workspace being around 4.5GB. We seem to reach an upper limit somewhere between 400-450 for scheduled refreshes before we see catastrophic failure. For context, we may have between 4-10 daily failures for various reasons when there are under 400 datasets refreshing simultaneously. However, once we cross a threshold (around 400) then we will see 50+ failures each day, with the majority of errors being SQL Timeout. All of our datasets are imported from an AZURE SQL database, so when configuring the daily refresh schedule, all of these datasets are refreshing via Cloud-to-Cloud connection. Our last job in the Data warehouse finishes just before 6am daily, and we open for business at 8am. This leaves only a 2 hour window for all 450 datasets to successfully refresh. Has anyone else had similar performance issues? All workspaces are Pro.

Thanks

[ToddChitt]

Azure 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?

[KyleD]

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.  

[KyleD]

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.