EPSA Boosts Business Resilience with Snowflake: Accelerated Insights and Data Sharing

Since opening in 1992 in Victoria, Energy Power Systems Australia (EPSA) has grown to become Australia’s leading engine supplier. EPSA is the authorised supplier of Cat® engines, generators and power solutions in Australia, Papua New Guinea and the Solomon Islands.

EPSA offers both sales and rental options for their customers across a wide range of industries including mining, construction, oil and gas, industrial and waste, agriculture, government, utilities, residential, education, manufacturing, technology and health.

STORY HIGHLIGHTS

• Rapid Migration to Modern Data Platform: EPSA, working with Pivot Analytics successfully migrated from their outdated SQL Server-based reporting solution to Snowflake in just 6 weeks. This not only retired a system that had been in place for over 13 years but also enabled EPSA to embrace a modern data platform, setting the stage for future growth.
  

• Massive Performance Improvements: With Snowflake, EPSA experienced a significant boost in performance. Query execution times, which once took up to 10 minutes, were reduced to less than a second. This dramatic improvement ensures faster access to critical business insights and data-driven decision-making.
  

• Future-Ready Data Capabilities: The migration to Snowflake empowered EPSA with a data platform designed to support their evolving needs. It not only addresses their current data requirements but also provides the flexibility to explore new data-driven possibilities. Additionally, Snowflake's data sharing capabilities facilitate collaboration within the larger CAT group, enhancing the potential for knowledge sharing and synergy.

“The migration from Excel based reporting using ODBC and SQL database solution to a Snowflake and Power BI based solution creates a foundation for EPSA to embrace a data driven decision making mindset. It is a critical step in the evolution of the organisation. Pivot Analytics made the transition easy and EPSA looks forward to a long term strategy partnership.” Rob Setina, National IT Manager

CHALLENGE

With a diverse array of products and services serving a wide range of industries, EPSA's operations heavily rely on an ERP system that can adapt seamlessly to their evolving needs. For years, EPSA had been utilizing an on-premises version of Infor (Lawson Smart Office) as their core ERP system. In addition to their ERP, they had developed an in-house reporting solution, which involved extracting data from Infor and storing it in a SQL Server 2005 database for reporting, with the final reports being delivered through Excel.

Initially, this reporting solution met the business's requirements. However, as time went on, the company's demands outgrew the system's capabilities. Loading Excel reports that once took a matter of seconds were now taking a frustratingly long 5 to 10 minutes. To compound matters, both SQL Server and Infor (Lawson Smart Office) were approaching the end of their support lifecycles.

Recognising the urgency of the situation, EPSA realised that a new data platform wasn't just a matter of modernisation; it was critical for mitigating business continuity risks and ensuring seamless operations into the future.

SOLUTION

The journey to Snowflake commenced with a Proof of Concept (POC) designed to ensure the feasibility of two crucial aspects. Firstly, the team needed to validate that data could be seamlessly loaded into Snowflake from Infor Cloud Suite, the backbone of EPSA's operations. Secondly, it was essential to confirm that Snowflake could efficiently deliver the reporting performance improvements that EPSA required. The successful POC marked a pivotal milestone.

With the POC's success as a testament to Snowflake's capabilities, it was evident that Snowflake was the data warehouse of the future for EPSA. The migration project officially commenced on August 1st, 2023.

The migration strategy harnessed the inherent capabilities of Infor Cloud Suite, enabling a delta export of data to AWS S3. From there, an external stage was employed to facilitate the smooth transfer of data from S3 to Snowflake. To further transform the data, dbt (Data Build Tool) pipelines were meticulously crafted to transform raw data into actionable insights fit for reporting. The true complexity of the project was rooted in the intricate web of the existing data pipeline. The previous SQL Server-based solution had organically grown over time, resulting in a convoluted structure that had become difficult to manage.

In total, the transformation effort encompassed almost 300 scripts, tasked with reshaping the data extracted from 35 source tables into a cohesive structure comprising 12 destination tables. The intricacy of this undertaking was akin to untangling a web of spaghetti, as can be seen from the legacy pipeline diagram below.

Each script represented a thread in this tangled web, and the journey to Snowflake required these threads to be systematically woven into a seamless, modern data architecture. A sample of one of the new dbt transformations shown below replaces the table highlighted with the red circle in the spaghetti diagram:

RESULTS

• Migration at Pace: In a mere six weeks of intensive data pipeline development, the new Snowflake data infrastructure was operational, retiring the outdated SQL Server 2005 solution that had faithfully served EPSA for over 13 years. This rapid transition underscored the agility and efficiency of the project.
  

• Massive Performance Improvements: One of the most striking achievements was the significant boost in performance. Where query execution times had once stretched to 10 minutes, the new Snowflake platform slashed these report loading times to a few seconds. This profound improvement not only enhances productivity, but also positions Snowflake as a modern, high-performing data platform capable of supporting EPSA for years to come.
  

• A Platform for the Future: EPSA is now equipped to chart a new course in their data journey. The transition to Snowflake opens doors to innovative data utilisation within their organisation. Moreover, being part of the larger CAT group, Snowflake enables data sharing capabilities across the entire group, fostering collaboration and knowledge exchange.
  

• Success Through Strong Partnership: The success of this project was not merely a technical achievement but a testament to the strength of the partnership between EPSA, Pivot Analytics and Snowflake. Despite the complexities encountered along the way, the unwavering commitment and collaboration made the journey an absolute pleasure and a resounding success.

In summary, this transformation has not only brought immediate efficiency gains but has also set the stage for a data-rich and collaborative future for EPSA. The project's rapid pace, monumental performance improvements, and the strength of the partnership reflect a remarkable achievement that underscores the power of modern data solutions and collaborative efforts.

For more information get in touch with us via email at info@pivotanalytics.com.au or calling 1300 475 510.