Our client is a leading global provider of high-quality steel wire cables, synthetic cables and subsea solutions for a wide range of industries, including oil and gas, mining, construction and maritime. Founded in the early 20th century and headquartered in Western Europe, the company has a rich history of delivering innovative and reliable solutions to customers worldwide. Now, with around 200 employees, they leverage state-of-the-art production facilities to design, manufacture, and test bespoke cable products to meet the most demanding custom applications and requirements. The company’s many products are used in a variety of critical applications and that is why the urge for real-time production monitoring came up.
For the last couple of years, the factory has had significant losses due to unfulfilled orders. The pipeline exceeds the production capacity, while at the same time, the utilization of production facilities is under 70%. The ERP system does not provide solutions and real-time information is scarce.
Production comprises of 25 – 30 machines of which five are the bottleneck machines and critical to the output of the factory. The On-Time Delivery Performance was hovering at around 40% to 50%.
The company only had limited fact-based insight into the factors determining production output, reliability and delivery lead time. The Logistics Manager had started to monitor the set-up and run time of the five bottleneck machines in comparison with the planned set-up and run time.
The company requested a solution that would provide real-time monitoring and transparency of their production process. The reporting structure also had to allow for drill down from senior management to the shop floor.
A real-time production monitoring and transparency solution
Together with the client, we agreed on the functional requirements for the solution, defining relevant KPIs for their specific industry needs. The solution’s ultimate goal is to enhance the visibility of business processes by integrating data from the client’s current systems through Azure Data Factory into a Data Warehouse (DWH) within Azure SQL Database. This then allows for improved scalability through faster reporting and the consequent analysis of all relevant workflows to ensure the transparency of activities for optimal decision-making and enhanced business performance.
We established high-level architecture documentation and captured the following KPIs:
- Overall Equipment Efficiency (made up of machine capacity and performance)
- Product schedule and backlog
- Different service level KPIs – designed to report on the attainment of orders
All of these are standard for the industry, but the company only had the raw data, so it was up to us to make the most of it to advance their business needs. The proposed and delivered cloud infrastructure was also new to our client, so we made sure to ease this process by providing a pros and cons list, as well as detailed infrastructure guidelines. We further proved our commitment to the project by iteratively delivering a Proof of Concept on one of the company’s most heavily used machines to showcase the power of purposeful reporting. The PoC itself led to reworks of the final KPIs and revealed a bottleneck in the company’s scheduling mechanism.
An in-depth look to the solution
Our work led to in-depth reporting from various systems, including the company’s ERP system and finance management, HR and scheduling systems, as well as issue and machine maintenance trackers. As mentioned earlier, Azure Data Factory was used for integration between the source systems and the DWH, while Azure SQL Database was used as the core of the DWH. It was designed with a three-layer structure to allow for the efficient processing and storage of the data, further ensuring data integrity.
The presence of staging, physical and semantic layers avoids the reworking of existing reports when certain data definitions change. The second of these layers integrated the company’s functional requirements to allow for the calculation and reporting of production and logistics KPIs, while the semantic layer defined the business logic that will build the reporting measures and KPIs. We utilized Power BI for the User Interface (UI). This connects to and visualizes all types of data, and seamlessly integrates the visuals into the apps for everyday use. The user access management to dashboards was based on the Active Directory permissions structure.
Given the critical application of the company’s products, security was a key concern. We created a custom security plan outlining how our solution fits into their current infrastructure to ensure compliance with the requirements of the National Institute of Standards and Technology (NIST). We also adhered to the ISO 27001 standards and developed General Data Protection Regulation (GDPR) documentation to ensure that principles such as data minimization and the de-identification of personally identifiable information were met. Overall, the end solution was enabling real-time production monitoring and transparency.
Our solution allowed for both real-time dashboards and scheduled data refreshes in order to remove the overhead of manual data transfer from source systems to our client’s old Excel-based reporting. The design process, from safety regulations through to KPI definition and then to the final product, fits both the current and the future needs of our client. This allowed for business continuity through bottleneck visualization and insight. In the end, the solution we provided was both sustainable and end-user-friendly. This was important for its adoption and ongoing use as our client continues to define their KPIs and grow through a data-driven approach.