Midwest energy provider improves operational excellence with Snowflake and Power BI

Client background

This S&P 500 company provides regulated electric and natural gas service to over one million customers in the Midwest. It is committed to exceptional service, responsible resource use and competitive pricing. The company proactively pursues emerging technologies and clean, safe, sustainable methods of energy production. It has been nationally recognized for its holistic approach to corporate responsibility, which considers the three pillars of ESG: environmental, social and corporate governance.

The business challenge

With its commitment to achieving operational excellence, the company identified key areas of its business for specific teams to manage and measure performance, such as planning, a life cycle, customer service, materials, contractors, energy production and more. These teams carefully defined the top metrics and appropriate thresholds to determine if these metrics are meeting their targets month to month and what actions could be taken to drive improvement and overall growth.

To facilitate the tracking and sharing of metric performance, each team leveraged a spreadsheet on SharePoint to input metric values, confirm if metrics improved from the prior month, whether they passed or failed compared to their thresholds and lastly, provide any free-form insights to lend additional context to the analysis.

It became increasingly time-consuming and challenging to manually collect the teams' metric spreadsheets, combine and deliver the overall performance summary to the broader organizational audience to analyze monthly. With the company's desire to achieve and maintain operational excellence in its key areas, it became apparent it would need a more robust and automated reporting solution to stay on top of analyzing performance and driving out timely actions to meet its goals.

Strategy and solution

Baker Tilly worked collaboratively with this company to define and execute a strategy to automate and streamline the monthly collection of operational metrics from business area leaders, and develop an intuitive, interactive dashboard using data visualization best practices.

This automated reporting solution for improving operational excellence metric tracking consisted of the following:

• Developed a Power Automate flow to copy the metric spreadsheets in SharePoint to a location for easy ingestion into the Snowflake enterprise data warehouse
• Developed a python script to ingest copies of the metric spreadsheets into the Snowflake enterprise data warehouse using an automated scheduling tool
• Shaped and combined metric data using Snowflake stored procedures and tasks
• Designed and developed a dimensional model with facts and dimensions in Snowflake to represent the metric data by key business terms and measures defined and governed by the operational teams
• Developed a certified Power BI semantic model that incrementally refreshes daily using deployment pipelines
• Designed a Power BI dashboard with a well-defined user experience to enable users to view summary level rollups of the combined operational metrics, and drill through to details of each area's metrics
• Applied data visualization principles and best practices in Power BI to ultimately speed up the time to insight, leading to faster calls to action for operational leaders

While automating the operational metric process for reporting provided multiple immediate benefits, Baker Tilly plans to assist the company with its next goal of improving how operational leaders can explore source data to analyze and confirm current metric values which feed the operational dashboard. Baker Tilly will continue to assist this company in its path to achieving operational excellence with the use of automation and data visualization.