Mathematical Optimization and Databases: Solving a Multi-Site Production Planning Problem with Python
Production planning problems are a class of optimization problems that play a vital role in various industrial and manufacturing domains.
Introduction
Given the scale, sparsity, and complexity of modern production networks and infrastructure, mathematical optimization based on modern optimization tools and simulation frameworks has become an indispensable tool. These tools help manufacturers quickly and efficiently construct plans to optimally meet strict delivery deadlines, improve productivity, eliminate underutilization/overutilization within production lines, minimize costs, and maximize profits.
Many of these mathematical optimization solutions must process large datasets residing in database systems, whether on-premises or in the cloud. The main aim of this article is to explore the development of an optimization workflow that solves a class of production planning problems by interacting with database systems. We utilize a group of open-source frameworks in Python to develop this workflow, including the PuLP library to model and solve optimization problems, SQLite to implement the database engine, pandas to preprocess and manipulate data, and matplotlib and seaborn to visualize and analyze the input and output of the optimization workflow.
Multi-Site Production Planning
Problem statement
The Multi-Site Production Planning Problem (MSPP) is a class of optimization problem commonly encountered in industrial and manufacturing domains where production occurs across multiple sites or facilities. In this article, we model MSPP as an extended version of the simple production planning problem, a classical optimization problem. The objective of MSPP is to determine the optimal production plan for each site to maximize profit while satisfying a variety of functional constraints. An overview of the problem is shown below, and the main components of the problem are described as follows:
1. Multiple Production Sites: MSPP involves multiple production sites or facilities, each capable of producing various products or components. These sites may have varying production capacities, capabilities, and costs. In this article, we assume the problem belongs to the class of separable optimization problems. This means we assume no interaction between production sites, allowing MSPP to be solved by decomposing it into a series of Single-Site Production Planning Problems. These can be processed either serially or in parallel to satisfy the planning requirements of different factories or production units.
2. Inventory: Production planning involves various raw material requirements depending on the product type. Production volumes need to be maintained at specific levels to avoid stockouts or excess inventory.
3. Production Capacity: Production involves utilizing a sequence of machines across production lines to process raw materials into final products. There is a known required labor in terms of machine hours to produce a specific product by a specific machine. On the other hand, each production machine has capacity constraints in terms of available machine hours that limit the amount of production volume it can undertake within a given time horizon.
4. Market Demand: There is a known demand for products or components that must be met as defined by field planners. The planning process should meet the upper and lower bounds of market demand as defined by the field planners.
5. Time Horizon: The production planning typically covers a certain time horizon, depending on the industry and planning requirements. In the current study, we assume weekly production planning requirements.
6. Objective Function: The main objective of the planning problem under consideration is to maximize profit by increasing revenue, taking into consideration a variety of costs like production and raw material costs.
Mathematical Model
In the current article, we model MSPP using a Mixed Integer Linear Programming (MILP) framework, which has a linear objective function, linear constraints, and integer decision variables. The main components of the mathematical model are defined as follows:
- Decision variable: we first define decision variables as follows:
2. Constrains: We consider the following constraints on the decision variables:
2. 1. Market demand: The overall production volume for a specific product should not exceed the upper and lower bounds of market demands.
2. 2. Raw material inventory: The production volume at a specific site must not exceed the available raw material inventory levels.
2. 3. Production capacity: The total labor required to produce products on a particular machine should not exceed the weekly available time for that machine:
3. Objective value: The main objective of the optimization problem is to maximize profits by optimally planning the production volume, taking into consideration production and raw material costs.
Solution to Multi-Site Production Planning
We wrote a program in Python to solve MSPP and to demonstrate the feasibility of integrating databases with optimization workflow. The complete source code can be found Here. In this section, we discuss the main components of the implementation environment, the optimization approach, and briefly discuss the obtained optimization results.
Implementation Environment
Here we briefly highlight some of the main Python packages utilized to implement the optimization workflow:
· PuLP Library: The mathematical model of MSPP was implemented using PuLP, a popular open-source linear programming (LP) modeling language in Python. It provides a simple syntax for constructing linear and mixed-integer linear programming models and interfaces with a variety of LP solvers.
· CBC Solver: PuLP can interface with various LP solvers to solve optimization problems. The default one is the CBC (Coin-or Branch and Cut) solver. CBC is an open-source LP and mixed-integer programming (MIP) solver developed as part of the COIN-OR project. CBC uses a combination of branch-and-bound and cutting plane algorithms to solve LP and MIP problems efficiently. This is the family of problems to which MSPP belongs.
· SQLite3: The database environment required for the current implementation was implemented using SQLite3, a versatile and user-friendly database engine suitable for applications that require a robust, yet lightweight, data storage solution. Most importantly, it allows interactions with databases using a nonstandard variant of the SQL query language.
Synthetic Data Generation
We used synthetic data (randomly generated data) to evaluate the feasibility and performance of the implemented optimization workflows. The main characteristics of the generated data are as follows:
· We selected the number of production sites to be large enough to moderately represent the complex nature of modern production infrastructure. In this experiment, we set the total number of production sites as 100.
· For each production site, we have three different product groups with 10 products in each group, resulting in a total of 30 products per production site. The statistical characteristics of the generated product data are shown below.
As shown, the product data is generated to have similar patterns in terms of market upper and lower bounds. Moreover, the inventory of raw materials is generated to be sufficient to cover market demands. On the other hand, product data is generated to have various patterns in terms of required raw materials per product, raw material costs, product labor time and product prices. The profit margin is high for some products, while it is relatively lower for others. Therefore, the goal of the current production planning is to identify and maximize the production volume of products that enjoy a relatively higher profit margin.
· For each production site, we have three different types of machines. The statistical characteristics of the generated machine data are shown below.
As shown, the machine data is generated to have various patterns in terms of production capacity, operation cost, and required labor to produce products. Some machines support higher machine availability, which can be easier to schedule, while others support lower machine availability; therefore, they can be more difficult to schedule, posing some challenges to the current production planning task.
Optimization Workflow
In this article, we implemented a serial optimization workflow to solve the problem at hand. The architecture of the implemented workflow is shown below. The process solves the MSPP sequentially by retrieving the relevant data for a specific production site ID and solving it as a Single Site Production Planning Problem. Once completed, the result is saved into the database for further analysis. This process repeats for each production site ID in the list of available production sites.
Results and Evaluation
The experiment reveals the feasibility of executing the above-mentioned optimization workflow, which constructed the optimal production plan for 100 production sites within 138 seconds. An example of the planning results is shown below, with ProductionVolume being the outcome of the optimization process. For each product, the table illustrates the planned production volume, production deadline, specific production site, and expected costs and profits. Field planners can utilize these planning results to analyze profit, costs, and productivity and to implement effective production scenarios.
To evaluate the quality of the obtained solution, we analyzed the profit margin of the planned production volume. As shown below, to maximize profit, the optimizer successfully identified and maximized the production of products with higher profit margins up to the demand upper bounds. Conversely, the production volume of products with lower profit margins was kept at a minimum to cover demand lower bounds. It is also notable that the production of some high-margin products fell below the demand upper bounds. This trade-off was made by the optimizer to alleviate limitations within production capacity or machine available time, preventing it from perfectly satisfying the demand upper bounds for all such products.
Additionally, we analyzed machine operation time in terms of the operation ratio, defined as the ratio of actual operation time to total available time. The optimizer effectively planned product sequences to production machines to maximize operation ratio and minimize machine idle time. As shown below, the planned machines operated with a high operation ratio, equaling or exceeding 85%, with the largest group of machines operating at ratios equaling or exceeding 95%.
Conclusion
In this article, we addressed the Multi-Site Production Planning Problem, a class of Mixed Integer Linear Programming optimization problems commonly encountered in industrial and manufacturing domains. The goal is to construct optimal plans that maximize profits while addressing various constraints and functional requirements across multiple production sites or facilities. Considering the sparse and complex nature of modern production networks, the design and implementation of mathematical optimization solutions should account for the interaction with large datasets residing in database systems, whether on-premises or in the cloud. We addressed this issue by implementing and evaluating the performance of a serial optimization workflow that interacts with databases using a group of open-source packages in Python.
