During the COVID-19 pandemic, our client needed to conduct a complex Power BI analysis of office building usage by its worldwide workforce to determine which buildings/locations should be closed and the financial impact of those closures. The analysis needed to include data on the operational costs and building usage, as well as the criticality of various job types and real-time worldwide COVID-19 trends and hot spots.
Unfortunately, the reports developed in-house were very inefficient and took a long time to render, causing serious stability problems. They needed a better solution.
The client connected with Beyondsoft through the Microsoft Power BI partner program. Beyondsoft’s Power BI experts determined that the complexity of the calculations, sheer volume of data, and the inefficient data model all contributed to performance bottlenecks. The prolonged time needed to render all the visuals made the in-house developed reports unstable.
To resolve these issues, Beyondsoft leveraged Microsoft Power BI, Oracle Server, Smartsheet, Excel, and R Scripting to optimize coding and data and eliminate the bottlenecks. The most impactful optimizations included:
- Reducing data load size by millions of rows to improve data refresh and data transformation times.
- Tuning Oracle SQL code to improve and filter unneeded data elements.
- Updating the data model to create a reduced memory-footprint of the analytics engine.
- Creating and updating data relationships to allow enhancement of additional metrics and KPIs.
- Rewriting calculation code to decrease complexity.
- Completing a query plan optimization analysis to find and resolve query inefficiencies.
The series of Power BI optimizations significantly reduced the rendering time, down from six minutes to just 20 seconds for each report. The client was delighted with the 18X increase in efficiency. The vast improvement allowed the client to reliably make proactive decisions to address business continuity, risk mitigation, and cost reduction in response to the changes brought by the COVID-19 pandemic.