The Model Definitions Tree Window for the Planning7 Model
Often, when working with large models, the data for the model tends not to be dense; as in the previous models we worked on, but rather in a sparse format. Dense data can be perceived in the same manner as spreadsheet data. Ordinarily, it is used for data vectors with not more than two dimensions, where every column and every row is filled with data.
Sparse data, on the other hand, typically involves multiple dimensions, but does not necessarily contain values for every combination of the indexes. Sparse data is usually stored in a table format, where each column represents an index or a data value. When working with large sparse data sets, it is common to work with the data in a table format. This allows you to easily skip certain combinations of the index, that are not valid, by omitting them in the table.
Sometimes, when formulating production planning models, the decision involves which machines to use to produce the products. As all of the machines are not available in every plant, this introduces a sparsity into the model. When we define the data and the variable vectors for the model, we will then utilize that sparsity to ensure that the size of the model does not become too large. This can be accomplished, either by using a standard WHERE command on a data vector, or by using the IN operator to connect the relevant indexes.
The IN operator in MPL allows you to select one of the domain indexes from a multidimensional index. For example, if you have a multidimensional index that specifies which machines are available in which plants, you can use the IN operator to sum over all the machines for that particular plant.
INDEX
plant := (p1, p2, p3, p4);
machine := (m11, m12, m13, m21, m22, m31, m32, m41);
PlantMach[plant,machine] :=
(p1.m11, p1.m12, p1.m13,
p2.m21, p2.m22,
p3.m31, p3.m32,
p4.m41);
In the above example, we have defined a multidimensional index called PlantMach that connects the plants to the corresponding machines.
The PlantMach index can then be used, selectively, to choose only the machines that are available in a particular plant. For example:
SUBJECT TO
PlantCapacity[plant] :
SUM(machine IN PlantMach: Produce[machine]) <= MaxCapacity[plant];
In the above example, we sum together how much is produced on each machine at that particular plant. Then we make sure that the total production is limited to the maximum capacity.
Just as you can store the data in external data files, you can also store indexes in external index files. Index files allow you to store the elements of an index in a file instead of specifying them directly in the model. When you are defining an index with an index file use the keyword INDEXFILE with a filename instead of the usual list of elements. For example:
INDEX
product := INDEXFILE("Product.idx");
month := INDEXFILE("Month.idx");
plant := INDEXFILE("Plant.idx");
The index file is just a standard text file containing a list of the index elements for the particular index. You can separate the elements in the file with either a comma, a space, or both. For example here is a sample index file for the product index:
! Product.idx - Index element for the product index
A1, A2, A3
Generally, when working with sparse models, the data involved is quite large and comes from other applications, such as corporate or desktop databases. In previous sessions, the data was typed into the model file or stored in a dense data file. When working with large data sets, you need a more efficient method to import the data into MPL from other applications. For this purpose, MPL has the ability to read the data from a sparse data file. This file allows you to enter the data in a standard table format, which is closer to the actual characteristics of the data, for example, from a relational database. An example of a sparse data file could be as follows:
ProdCost[plant, machine, product] := SPARSEFILE("ProdCost.dat");
The file ProdCost.dat contains the data in column oriented format with the indexes listed in the first three columns and the corresponding data value at the end of each line as follows:
p1, m11, A1, 73.30,
p1, m11, A2, 52.90,
p1, m12, A3, 65.40,
.
.
.
p4, m41, A2, 63.30,
p4, m41, A3, 53.80
Please note, MPL allows you also to store multiple data columns in a single sparse data file. You specify which column by adding a comma and the data column number after the file name inside the parentheses.
ProdCost[plant, machine, product] := SPARSEFILE("ProdCost.dat", 2);
Using sparse data files is common in real world modeling. These files can end up being quite large, with multiple indexes and containing lots of data. Frequently, you will have multiple index files and sparse data files storing all the data and leaving the model file only to contain the actual model statements, such as the variables, objective function and constraints.
In this session, you will update the model to have multiple machines distributed between the plants. You will use the model you created in session 6, and make the necessary additions and updates to it.
Since we now have different machines within each plant, the production cost and the production rate now have different value for each machine. The following is a table with a single line for each plant, machine, product combination that is applicable.
| Plant | Product | Product | ProdCost | ProdRate |
|---|---|---|---|---|
| p1 | m11 | A1 | $73.30 | 500 |
| m11 | A2 | $52.90 | 450 | |
| m12 | A3 | $65.40 | 550 | |
| m13 | A3 | $47.60 | 350 | |
| p2 | m21 | A1 | $79.00 | 550 |
| m21 | A3 | $66.80 | 450 | |
| m22 | A2 | $52.00 | 300 | |
| p3 | m31 | A1 | $75.80 | 450 |
| m31 | A3 | $50.90 | 300 | |
| m32 | A1 | $79.90 | 400 | |
| m32 | A2 | $52.10 | 350 | |
| p4 | m41 | A1 | $82.70 | 550 |
| m41 | A2 | $63.30 | 400 | |
| m41 | A3 | $53.80 | 350 |
The production decision, how much we want to produce of each product, needs to take into account that we now have multiple machines. Therefore, you will update the Produce variable to include the machine index and then use a WHERE condition to exclude the elements that are not applicable, such as plant p1, machine m11, and product A3.
Listed below is the entire model formulation for Planning7. The additions to the model are highlighted in boldface in order to make it easy for you to see the changes from the model in Session 6.
TITLE
Production_Planning7;
INDEX
product := (A1, A2, A3);
month := (Jan, Feb, Mar, Apr);
plant := (p1, p2, p3, p4);
toplant := plant;
fromplant := plant;
machine := (m11, m12, m13, m21, m22, m31, m32, m41);
DATA
Price[product] := (120.00, 100.00, 115.00);
Demand[plant, product, month] := DATAFILE("Demand6.dat");
ProdCost[plant, machine, product] := SPARSEFILE("Produce.dat", 1);
ProdRate[plant, machine, product] := SPARSEFILE("Produce.dat", 2);
ProdDaysAvail[month] := (23, 20, 23, 22);
InvtCost[plant, product] := DATAFILE("InvtCost.dat");
InvtCapacity[plant] := (800, 400, 500, 400);
ShipCost[fromplant, toplant] := DATAFILE ("ShipCost.dat");
VARIABLES
Produce[plant, machine, product, month] -> Prod
WHERE (ProdCost > 0);
Inventory[plant, product, month] -> Invt;
Sales[plant, product, month] -> Sale;
Ship[product, month, fromplant, toplant]
WHERE (fromplant <> toplant);
MACROS
TotalRevenue := SUM(plant, product, month: Price * Sales);
TotalProdCost := SUM(plant, machine, product,month: ProdCost * Produce);
TotalInvtCost := SUM(plant, product, month: InvtCost * Inventory);
TotalShipCost := SUM(product, month, fromplant, toplant: ShipCost * Ship);
TotalCost := TotalProdCost + TotalInvtCost + TotalShipCost;
MODEL
MAX Profit = TotalRevenue - TotalCost;
SUBJECT TO
ProdCapacity[plant, machine, month] -> PCap:
SUM(product: Produce / ProdRate) <= ProdDaysAvail;
PlantBal[plant, product, month] -> PBal:
SUM(machine: Produce) + Inventory[month-1]
+ SUM(fromplant: Ship[fromplant, toplant:=plant])
=
Sales + Inventory
+ SUM(toplant: Ship[fromplant:=plant, toplant]);
MaxInventory[plant, month] -> MaxI:
SUM(product: Inventory) <= InvtCapacity;
BOUNDS
Sales < Demand;
END
Start the MPL application.
Choose File | Open and open the model from the previous session Planning6.mpl.
Choose File | Save As to save it as a new model file Planning7.mpl.
Change the title for the model to reflect that you are working with the Planning7 model.
TITLE
Production_Planning7;
In this model, each plant now has multiple machines. To create an index for the machines add the following definition for the machine index in the INDEX section.
INDEX
product := (A1, A2, A3);
month := (Jan, Feb, Mar, Apr);
plant := (p1, p2, p3, p4);
toplant := plant;
fromplant := plant;
machine := (m11, m12, m13, m21, m22, m31, m32, m41);
The production cost and the production rate now need to include the machine index since we have different data values for each machine. Also, since the data is now sparse, that is not every plant has every machine, you are going to store the data in a sparse data file. MPL allows you to store multiple data columns in a single sparse datafile. You specify which column to read by adding comma and the data column number after the filename.
Update the definitions for the ProdCost and the ProdRate data vectors to include the machine index and change the filenames to a new sparse datafile called Produce.dat. For the production cost specify column number 1 after the filename and for the production rate specify column number 2.
DATA
Price[product] := (120.00, 100.00, 115.00);
Demand[plant, product, month] := DATAFILE("Demand6.dat");
ProdCost[plant, machine, product] := SPARSEFILE("Produce.dat", 1);
ProdRate[plant, machine, product] := SPARSEFILE("Produce.dat", 2);
ProdDaysAvail[month] := (23, 20, 23, 22);
InvtCost[product] := DATAFILE("InvtCost.dat");
InvtCapacity[plant] := (800, 400, 500, 400);
ShipCost[fromplant, toplant] := DATAFILE("ShipCost.dat");
Now you need to create the sparse data file Produce.dat from the data given in the problem description earlier in this session.
To create the data file for the production cost open a new editor window for a data file called Produce.dat and type in the following:
!
! Produce.dat - Production Cost and Rate
!
! ProdCost[plant, machine, product]:
! ProdRate[plant, machine, product]:
!
p1, m11, A1, 73.30, 500,
p1, m11, A2, 52.90, 450,
p1, m12, A3, 65.40, 550,
p1, m13, A3, 47.60, 350,
p2, m21, A1, 79.00, 550,
p2, m21, A3, 66.80, 450,
p2, m22, A2, 52.00, 300,
p3, m31, A1, 75.80, 450,
p3, m31, A3, 50.90, 300,
p3, m32, A1, 79.90, 400,
p3, m32, A2, 52.10, 350,
p4, m41, A1, 82.70, 550,
p4, m41, A2, 63.30, 400,
p4, m41, A3, 53.80, 350,
The Produce variable now needs to have the machine index in the declarations as we need to know on which machine each product is produced. Furthermore, since not all of the machines are in every plant we need to exclude the index combinations that are not valid. This is done by using a where condition on the ProdCost data vector. The combinations of indexes are used only when ProdCost is greater than zero or when expanding the Produce variable. Enter the changes to the Produce variable as follows:
VARIABLES
Produce[plant, machine, product, month] -> Prod
WHERE (ProdCost > 0);
In the macro for the total production cost, add the index machine to reflect that the Produce variable now contains the machine index.
MACROS
TotalRevenue := SUM(plant, product, month: Price * Sales);
TotalProdCost := SUM(plant, machine, product,month: ProdCost * Produce);
TotalInvtCost := SUM(plant, product, month: InvtCost * Inventory);
TotalShipCost := SUM(product, month, fromplant,toplant: ShipCost * Ship);
TotalCost := TotalProdCost + TotalInvtCost + TotalShipCost;
In the declaration for the production capacity constraint, the machine index must be included since we now have a separate capacity limit for each machine in the plant. Enter the changes to the ProdCapacity constraint as follows:
SUBJECT TO
ProdCapacity[plant, machine, month] -> PCap:
SUM(product: Produce / ProdRate) <= ProdDaysAvail;
In the plant balance constraint there is now a separate Produce variable for each machine. As we need to add together the total production for the particular plant, we now need to sum over the machine index when referring to the Produce variable. To do this add the following summation to the PlantBal constraint:
PlantBal[plant, product, month] -> PBal:
SUM(machine: Produce) + Inventory[month-1]
+ SUM(fromplant: Ship[fromplant, toplant:=plant])
=
Sales + Inventory
+ SUM(toplant: Ship[fromplant:=plant, toplant]);
The next step is to solve the model 'Planning7.mpl' by choosing Solve CPLEX from the Run menu. If everything goes well MPLwill display the message 'Optimal Solution Found'. If there is an error message window with a syntax error please check the formulation you entered with the model detailed earlier in this session.
You will use the model definitions window again, as in session 6, to look at the parts of the solution that we are interested in. To open the model definitions window for the Planning7 model choose Model Definitions from the View menu.
The Model Definitions Tree Window for the Planning7 Model
To look at the values for the Produce variable either double click on the produce icon of the variable tree or select it and press the View button. This will display a view window containing the solution values for only the Produce variable which are shown below:
VARIABLE Produce[plant,machine,product,month] :
plant machine product month Activity Reduced Cost
--------------------------------------------------------------------
p1 m11 A1 Jan 4300.0000 0.0000
p1 m11 A1 Feb 4200.0000 0.0000
p1 m11 A1 Mar 5487.5000 0.0000
p1 m11 A1 Apr 5300.0000 0.0000
p1 m11 A2 Jan 6480.0000 0.0000
p1 m11 A2 Feb 5220.0000 0.0000
p1 m11 A2 Mar 5411.2500 0.0000
p1 m11 A2 Apr 5130.0000 0.0000
p1 m12 A3 Feb 9049.3506 0.0000
p1 m12 A3 Mar 916.1616 0.0000
p1 m12 A3 Apr 10803.1169 0.0000
p1 m13 A3 Jan 8050.0000 0.0000
p1 m13 A3 Feb 7000.0000 0.0000
p1 m13 A3 Mar 8050.0000 0.0000
p1 m13 A3 Apr 7700.0000 0.0000
p2 m21 A1 Jan 5100.0000 0.0000
p2 m21 A1 Feb 6200.0000 0.0000
p2 m21 A1 Mar 6538.8889 0.0000
p2 m21 A1 Apr 7600.0000 0.0000
p2 m21 A3 Jan 4422.6136 0.0000
p2 m21 A3 Feb 3927.2727 0.0000
p2 m21 A3 Mar 5000.0000 0.0000
p2 m21 A3 Apr 3681.8182 0.0000
p2 m22 A2 Jan 6900.0000 0.0000
p2 m22 A2 Feb 6000.0000 0.0000
p2 m22 A2 Mar 6900.0000 0.0000
p2 m22 A2 Apr 6600.0000 0.0000
p3 m31 A1 Jan 3300.0000 0.0000
p3 m31 A1 Feb 5964.9351 0.0000
p3 m31 A1 Mar 2550.0000 0.0000
p3 m31 A1 Apr 4477.4026 0.0000
p3 m31 A3 Jan 4700.0000 0.0000
p3 m31 A3 Feb 2023.3766 0.0000
p3 m31 A3 Mar 5200.0000 0.0000
p3 m31 A3 Apr 3615.0649 0.0000
p3 m32 A1 Jan 800.0000 0.0000
p3 m32 A1 Feb 135.0649 0.0000
p3 m32 A1 Mar 2150.0000 0.0000
p3 m32 A1 Apr 1322.5974 0.0000
p3 m32 A2 Jan 7350.0000 0.0000
p3 m32 A2 Feb 6881.8182 0.0000
p3 m32 A2 Mar 6168.7500 0.0000
p3 m32 A2 Apr 6542.7273 0.0000
p4 m41 A1 Jan 4300.0000 0.0000
p4 m41 A1 Feb 4100.0000 0.0000
p4 m41 A1 Mar 5073.6111 0.0000
p4 m41 A1 Apr 4500.0000 0.0000
p4 m41 A2 Jan 2270.0000 0.0000
p4 m41 A2 Feb 5018.1818 0.0000
p4 m41 A2 Mar 2500.0000 0.0000
p4 m41 A2 Apr 5527.2727 0.0000
p4 m41 A3 Jan 3327.3864 0.0000
p4 m41 A3 Mar 2633.8384 0.0000
--------------------------------------------------------------------
The Produce variable is now defined over four indexes: plant, machine, product and month. For each plant the model decides which machine is the most efficient to produce the products at that particular plant. This table could be used as a basis for a production schedule for the whole company.
The other variable that is interesting in this model is the Inventory variable. If you go to the tree window again and open up a view window for the Inventory variable you will get the following solution values:
VARIABLE Inventory[plant,product,month] :
plant product month Activity Reduced Cost
-----------------------------------------------------------
p1 A2 Jan 800.0000 0.0000
p2 A2 Jan 400.0000 0.0000
p3 A3 Jan 500.0000 0.0000
p4 A3 Jan 400.0000 0.0000
-----------------------------------------------------------
As you can see the model has decided to produce products A2 and A3 during January to ensure we have enough on hand for February.
Most of the plants are now running at full capacity. If you go to the tree window again and open up a view window for the ProdCapacity constraint you will get the following solution values:
CONSTRAINT ProdCapacity[plant,machine,month] :
plant machine month Slack Shadow Price
-----------------------------------------------------------
p1 m12 Jan 23.0000 0.0000
p1 m12 Feb 3.9595 0.0000
p1 m12 Mar 20.2682 0.0000
p1 m12 Apr 1.2033 0.0000
p2 m21 Jan 3.6947 0.0000
-----------------------------------------------------------
As you can see plant p1 has some extra capacity for machine m12 and plant p2 has some extra capacity for machine m21. Other than that all of the machines in every plant is running at full capacity to fulfill demand.