Using data warehouse to aggregate data from multiple subsidiaries

Context

Our client was a US-based manufacturing company that supplies the commercial construction industry, with annual revenue of around $120M. Besides the main operating company in the US, it has two other global subsidiaries, in Germany and China. All three operating companies use different ERP systems: US uses Infor XA, Germany uses Microsoft Dynamics, and China uses Yonyou ERP. The US teams — including the leadership — lacked an efficient way to directly access data from their non-US subsidiaries; financial reporting and ad hoc data requests to the local management teams were the primary means to get numbers from overseas.

The newly appointed CEO and CFO sought greater visibility into the company's global operations. They believed that there were significant inefficiencies in the job scheduling and production processes in the US, causing production delays and quality issues. Globally, they wanted visibility into the bookings data, to be used as a leading indicator of revenue 2-3 quarters down the road.

Objectives

The client asked us to set up a data warehouse containing both commercial and production data from all its subsidiaries, so that automated KPIs and reports could be developed. Additionally, the production data needed to be refreshed daily, while the commercial data could be on a weekly refresh schedule.

Project delivery

We worked on each subsidiary sequentially based on urgency, starting with the US which is the largest operating company with the most amount of data. Following a similar process as this case study, we first developed a data catalog together with key business users including the leadership team, VP of Operations, EVP of Revenue, VP of Global Strategic Sales, and the IT manager. We set up a Microsoft Azure SQL Server as the data warehouse and PowerBI as the reporting platform. Our data engineers then set up the pipelines to replicate data out of Infor XA, extracting the data as JSON files that get loaded into the SQL Server using Azure Blob storage and Azure Data Factory.

Once the pipelines were deployed and tested in the US, we moved to Germany following the same deployment process. Germany's ERP is Microsoft Dynamics which uses an on-prem SQL Server as its database, so we were able to leverage the built-in SQL Server Replication feature to copy the data into the Azure SQL Server, simplifying the deployment process. However, we did have to set up some custom pipelines to replicate the labor time tracking system which uses a local Access database.

Finally, for China the Yonyou system uses an on-prem SQL Server as its database, so we used the same process as Germany.

Outcome

The deployment of the data warehouse provided US teams with access to daily raw data from their subsidiaries. After the data warehouse deployment, we supported the client in developing a series of reports and dashboards that provided the leadership team with near real-time visibility into most aspects of their business.

Here are some specific examples of how the client has since leveraged the data warehouse:

1. Executive Dashboard: an automated dashboard that contains KPI trends and scorecard for bookings, revenue, gross margin, EBITDA, throughput, labor productivity, and quality. Detailed in this case study
2. Pricing analysis: the commercial team asked us to assess the pricing algorithm of the European subsidiary and determine if/how it should be fixed to address the margin leakage issue there. See this case study
3. Operations Dashboard: at the request of the VP of Operations, we developed a detailed scorecard for plan vs. production, labor productivity, on-time delivery, supplier on-time delivery, quality, and cost of customer claims. This automated scorecard showed each KPI as green or red against its target
4. S&OP Dashboard: the new management team instituted an S&OP process by which sales and operations teams would align to better plan production capacities. To support this, we were asked to develop a dashboard that provided teams with near real-time visibility into the upcoming committed and available capacities for all assembly lines
5. Material Shortage Report: we supported the client in developing a report that flagged which inventory items are likely going to run short in the coming weeks, based on the current production schedule and orders backlog. The client requested this report when they opened their second North American plant, which complicated their operations and led to the breakdown of some long-established processes