
Data Analytics to Obtain Production Line Run Rates for Scheduling
We took an approach that would simplify the problem and make the solution algorithm transparent and easier to understand.
Challenge
A major food and beverage manufacturer asked Hallam-ICS to build a data analytics application for determining the Scheduling Run Rate of their packaging lines at multiple sites. The Scheduling Run Rate is used by personnel to plan different types of product to run on the production lines. The Run Rate for different products was found to vary greatly, based on these factors:
- Characteristics of the product
- Packaging arrangements and sizes
- OEM design speed of the line equipment
- Variations in line equipment reliability
The project objective was to calculate predictive Production Line Run Rates based on historical data from the past 4-weeks, 12-weeks, and 26 weeks, that can accurately predict the actual time needed to package batches of product for all combinations of product types and production lines.
Solution
We took an approach that would simplify the problem and make the solution algorithm transparent and easier to understand. The team documented the approach and reviewed the design with end users prior to development. It was important that the data analysis results be traceable back to the originating data, so that the calculated results could easily be verified and trusted. It was critical that the report run fast and be available to users with newly processed results for all production lines. The team achieved these feature requirements and deployed a successful application and report.
The project required the following expertise:
- Data solution design skills
- Database table design and build
- Microsoft SQL Server stored procedures programming
- Microsoft SQL Server Reporting Services report build
- Microsoft Power BI report for analysis and data verification
The Production Line Run Rate Report was required to:
- Read production data from an existing system.
- Calculate Production Line Run Rate based on a multiple-step algorithm.
- Obtain product data and categorize into a derived product type, based on product characteristics and packaging operation category.
- Output a report for each product type and production line with actionable results and traceable data details.
Results
Our team accomplished the following:
- Accurate and Predictive Scheduling: Developed a data analytics application that calculates predictive Production Line Run Rates using historical data from 4-week, 12-week, and 26-week intervals, enabling more accurate scheduling and planning for diverse product types and production lines.
- Traceable and Transparent Results: Designed a solution with a transparent algorithm and traceable data analysis results, ensuring that calculated outcomes could be easily verified and trusted by end users.
- Efficient Reporting and Insights: Delivered fast, actionable reports using Microsoft SQL Server Reporting Services and Power BI, providing categorized product insights and line-specific performance metrics to optimize production efficiency across multiple sites.