- After abandoning a costly warehousing project, Oldcastle Infrastructure, a CRH Company, migrated its on-prem SQL Server and NetSuite data into Snowflake, saving the company $360,000 and enabling business-wide analysis in Tableau.
- Fivetran handles the NetSuite API better than other solutions and loads the data into Snowflake quickly and reliably.
- Based in large part on this new BI capability and data-driven decisions, Oldcastle Infrastructure grew operating profit 21.5% in 2020.
- ROI for the system in the next 12-18 months is estimated to be a minimum of $25 million in terms of cost savings, increased margins, and improved sales.
- Pipeline: Fivetran
- Connectors: SQL Server, NetSuite, Salesforce
- Destination: Snowflake
- BI Tool: Tableau
- SI: InterWorks
Oldcastle Infrastructure, a CRH company, is an industry leader in engineered building solutions offering more than 16,000 pipe, precast, stormwater, enclosure and building accessory products. Since its founding in 1978, the business has maintained most of its data on-premise but moved a small portion to the cloud a few years ago. To generate effective and comprehensive business insights, Oldcastle Infrastructure decided it was time to migrate all of its data to the cloud.
Building Pipelines Internally Leads to Cloud Migration Challenges
Oldcastle Infrastructure had visibility into its on-prem SQL Server database and its NetSuite ERP individually, but did not have a single view across both ERPs. It couldn’t view transactional, manufacturing or production data collectively. About 90% of its business data was in one on-premise ERP in SQL Server, while 10% lived in NetSuite in the cloud.
In an effort to migrate to the cloud and become more data-driven, the company started a warehousing project focused on gathering the sales data from both ERPs that had been going on for eight months when Nick Heigerick, IT Manager of BI, started at Oldcastle Infrastructure in December of 2018. The team leading the project had spent nearly five months just planning out how they were going to move the SQL Server data from on-premise into Azure cloud. While the process that the team was building could potentially work, Heigerick recognized a scalability issue: “We didn't have the expertise to manage so many moving parts in-house, so we had to decide: Are we going to pay consultants to do this forever or is there a better way?”
Once it was clear that the project wouldn’t work long-term, Heigerick and BI developers Evan Schairbaum and Parimal Shukla, neither of whom had extensive knowledge of the full SQL Server stack, needed to find a new way to achieve a more data-driven culture. “I was basically forced to innovate,” Heigerick says. “Having worked with InterWorks in the past and knowing their reputation in the BI space, I brought them on to help us figure out how to build out our BI.”
Moving Data From On-Prem to the Cloud
Oldcastle Infrastructure chose Tableau as its BI platform and Snowflake as its warehouse, and then brought on SI InterWorks to help it choose a data pipeline tool. Here's how Heigerick explains the process:
We started looking at which ETL tools were innovative enough to handle this migration. This piece of the puzzle had been our downfall in the previous effort. InterWorks recommended Fivetran and it was the first SaaS solution that we found just worked. We thought, This is going to get us to where we need to be.
A reliable NetSuite connector was critical. Heigerick trialed another solution, but found that the NetSuite data loaded very slowly, and he wasn’t confident that it would maintain the API:
NetSuite is constantly changing its API, making conventional pipeline strategies difficult to maintain, especially when it has been, and continues to be, customized. I initially thought I would have to pay someone a lot of money to monitor what happens in the background, but Fivetran keeps up with the API, grabs all of the data, and automatically centralizes it into Snowflake. The first project never even attempted to grab the data. They just didn’t think it was possible.
In addition to providing reliable, centralized data, Fivetran has saved Oldcastle Infrastructure substantial time and money. “If the team had used Fivetran from the start, it would have been successful in weeks,” says Heigerick. “With Fivetran, I replicated all of our data in 10 business days – every table and every field – from both our on-prem and cloud ERPs, which saved us about $360,000 in initial setup and maintenance of our SQL Server and Netsuite connectors.”
Besides connecting to SQL Server and NetSuite, Oldcastle Infrastructure now utilizes connectors to other SaaS applications including Salesforce, Coupa and Box – providing richer insights as data analysts are able to combine data sets to make better decisions.
Moving to the cloud with its modern data stack was a critical decision for the business’ future scalability. “Migrating to the cloud allowed us to minimize maintenance and increase stability without sacrificing flexibility," says Heigerick. "It also brings the scalability that traditional on-prem setups cannot attain without significant cost." The company estimates that every 4 years, it will save another $500,000 - $750,000 in IT maintenance costs.
Adding Snowflake to the Stack
Heigerick notes that while Oldcastle brought on other turn-key solutions, it decided to move its data from Azure SQL Server into Snowflake to reduce manual warehouse maintenance and administrative DBA tasks:
Snowflake does a lot of the optimization around SQL Server for you with its micro-partitioning and so many other wonderful things. I don’t have to have a dedicated DBA; I don’t have to learn a new coding language; I don’t have to manage APIs. The ability to scale up and down dynamically and the ability to break out load compute from transformation compute, from consumption compute – there is infinite value in that.
Global Reports From a Single Source of Truth
According to Heigerick, the inability to generate global perspective reports was one of the biggest reporting challenges Oldcastle Infrastructure faced:
We couldn’t look at sales for all of Oldcastle Infrastructure in a single report because our data did not live in a single place. No one had figured out how to unite sales from NetSuite and from our on-prem ERP and even if we did figure it out, it wasn’t going to be scalable. I knew that if we didn’t build the right foundation the house was just never going to hold up. We would not be able to innovate fast enough to keep up with the demand for visibility into the data. Now we’ve created a scalable BI solution that is not a bottleneck in our ability to deliver on projects.
While the business was able to build reports around financials in the past, it was limited to monthly rolled-up figures that took about ten days to generate. By the time the business had the results, the numbers were stale. But with Fivetran automated ELT, speed and scalability with Snowflake, and reporting and visualization within Tableau, reports are created much more quickly, with reliable and up-to-date data.
Heigerick says that the reports he built using Tableau are the most popular standardized reports across the business, including a report that shows sales, the cost of goods sold, and margin dollars and percentage, carved out by the five most popular dimensions that the business has, including customer and item:
We’re answering the most common questions that people didn’t have common visibility into, and we’re answering them at a global level. We have transaction-level data updating every three hours and we could have it every 15 minutes if we wanted. It is giving us a single source of truth and allowing us to harmonize processes and identify inefficiencies. We were never going to get here prior to this stack. Now, we have enabled the entire business. We can do whatever we want.
Not only has Oldcastle Infrastructure benefited, but so has its parent company CRH, North America’s largest building materials company. Oldcastle Infrastructure is now easily able to report all of its critical business metrics to CRH on a regular basis – saving hundreds of hours. Additionally, Oldcastle Infrastructure has been recognized across CRH for its modern data stack approach, which is being adopted by other companies within the umbrella organization. By delivering cross-company metrics across the entire organization, Oldcastle Infrastructure and CRH will be able to realize additional multi-million dollar ROI in the years to come.