A Proudly Home-Grown Solution
Our steel manufacturer client proudly designed their own Supply Chain software solution decades ago. Everything involving requisitioning, manufacturing, and sales was handled in a custom solution they built on mainframe servers. Occasionally, I would stumble upon decades-old comments between lines of COBOL code and be reminded that this is a home-grown, one-of-a-kind system. For example, a happy anniversary message to my colleague's mom and dad 20 years ago was among the many lines of code I reviewed. These happy notes aside, this legacy database posed serious challenges to the transformation project because of how different it was from the target system, Microsoft Dynamics 365.
The Challenge of Missing Data
We suffered from a total absence of certain master data in the legacy system. Missing data in the system posed challenges to the project and called into question the feasibility of data migration. To explain how this business could operate without master data, they intentionally built their software solution around transactions. This design allowed steel product to be defined dynamically by customer orders. Additional specifications for finished products were derived from steel industry standards (ASTM) and physical, chemical, and mechanical properties. The programmers of this custom mainframe manufacturing software recognized that they could carry out business without maintaining a Product Master! Creatively, the legacy solution used attributes on work orders and sales orders to define the finished product.
Likewise, there was no master data for Formula, Routes, and Operations – these functional areas document the raw material and the processing steps needed to manufacture finished product for customer orders. The cold drawing, stress relieving, turning, grinding, and other cold finishing steps were driven by the deeply knowledgeable foremen on the plant floor and scheduled accordingly. Effectively, there was no 'single source of truth' for several data objects that were needed in Dynamics 365.
Developing a Product Master from Transactional Data
Before we could go live in Dynamics 365, we first needed to create a Product Master. Our conversion program considered 2 years’ worth of sales orders, work orders, and purchase orders. Dozens of fields across these tables were used to derive physical and chemical attributes of the product. After extensive research and data profiling, we grouped transactions on matching attribute fields and could confidently define a Product Master for each distinct combination or attributes. 95,000 sales orders, work orders, and purchase orders boiled down to 5,000 distinct products successfully converted to Dynamics 365.
Developing Formulas and Routes from Transactional Data
To standardize manufacturing processes, we were also tasked with creating master Formula and Route data for Dynamics 365. Surprisingly, most of the metalworking instructions we needed for conversion were non-existent in the system. We found that only a few weeks' worth of scheduling data was retained at any given time. To overcome this gap, the business restored 2 years' worth of scheduling data from tape backups, and we used these historical backups to extrapolate the Formula, Route, and Operations. In addition to historical scheduling data, we worked with manufacturing experts to understand undocumented complexities in their manufacturing process. For example, certain steps needed to be structured inline (like when steel passed directly from a turning machine to a grinding machine) and others needed to be discretely broken out for capacity planning. Also, certain operations were structured as alternates to give the buisness greater flexibility in the manufacturing process. As a result, the business now has a source of truth for the plant floor that does not rely on tribal knowledge.
A Brilliant Legacy System Unraveled
Reflecting on the experience, I recognize that the legacy database and software was the result of over 20 years of ongoing development by loyal, lifelong employees. The clever and unconventional software this group created contributed to the cold finishing for millions of pounds of steel. Very likely, I have driven in a car, or been in a skyscraper, or flew in an airplane made with steel that at some time was a data point in this AS/400 mainframe server. I am humbled to have been called to help this business maintain their leadership position in the global supply chain. Together we unraveled this steel mill’s one-of-a-kind solution and migrated it onto a system that will carry the business forward for the next 20 years