A blending example

A mining company has two types of ore available: Ore 1 and Ore 2. The ores can be mixed in varying proportions to produce a final product of varying quality. For the product we are interested in, the `grade' (a measure of quality) of the final product must lie between the specified limits of 4 and 5. It sells for REV= £125 per ton. The costs of the two ores vary, as do their availabilities. The objective is to maximize the total net profit.

Model formulation

Denote the amounts of the ores to be used by use₁ and use₂. Maximizing net profit (i.e., sales revenue less cost COST_o of raw material) gives us the objective function:

∑

o ∈ ORES

(REV-COST_o) · use_o

We then have to ensure that the grade of the final ore is within certain limits. Assuming the grades of the ores combine linearly, the grade of the final product is:

∑o ∈ ORES GRADEo · useo

∑o ∈ ORES useo

This must be greater than or equal to 4 so, cross-multiplying and collecting terms, we have the constraint:

∑

o ∈ ORES

(GRADE_o-4) · use_o ≥ 0

Similarly the grade must not exceed 5.

∑o ∈ ORES GRADEo · useo

∑o ∈ ORES useo

≤ 5

So we have the further constraint:

∑

o ∈ ORES

(5-GRADE_o) · use_o ≥ 0

Finally only non-negative quantities of ores can be used and there is a limit to the availability AVAIL_o of each of the ores. We model this with the constraints:

∀ o ∈ ORES: 0 ≤ use_o ≤ AVAIL_o

Implementation

The above problem description sets out the relationships which exist between variables but contains few explicit numbers. Focusing on relationships rather than figures makes the model much more flexible. In this example only the selling price REV and the upper/lower limits on the grade of the final product (MINGRADE and MAXGRADE) are fixed.

Enter the following model into a file blend.mos.

model "Blend"
 uses "mmxprs"

 declarations
  REV = 125                      ! Unit revenue of product
  MINGRADE = 4                   ! Minimum permitted grade of product
  MAXGRADE = 5                   ! Maximum permitted grade of product
  ORES = 1..2                    ! Range of ores

  COST: array(ORES) of real      ! Unit cost of ores
  AVAIL: array(ORES) of real     ! Availability of ores
  GRADE: array(ORES) of real     ! Grade of ores (measured per unit of mass)

  use: array(ORES) of mpvar      ! Quantities of ores used
 end-declarations

! Read data from file blend.dat
 initializations from 'blend.dat'
  COST
  AVAIL
  GRADE
 end-initializations

! Objective: maximize total profit
 Profit:= sum(o in ORES) (REV-COST(o))* use(o)

! Lower and upper bounds on ore quality
 sum(o in ORES) (GRADE(o)-MINGRADE)*use(o) >= 0
 sum(o in ORES) (MAXGRADE-GRADE(o))*use(o) >= 0

! Set upper bounds on variables (lower bound 0 is implicit)
 forall(o in ORES) use(o) <= AVAIL(o)

 maximize(Profit)                 ! Solve the LP-problem

 ! Print out the solution
 writeln("Solution:\n Objective: ", getobjval)
 forall(o in ORES)  writeln(" use(" + o + "): ", getsol(use(o)))

end-model

The file blend.dat contains the following:

! Data file for 'blend.mos'
COST: [85 93]
AVAIL: [60 45]
GRADE: [2.1 6.3]

The initializations from / end-initializations block is new here, telling Mosel where to get data from to initialize named arrays. The order of the data items in the file does not have to be the same as that in the initializations block; equally acceptable would have been the statements

initializations from 'blend.dat'
 AVAIL GRADE COST
end-initializations

Alternatively, since all data arrays have the same indices, they may be given in the form of a single record, such as BLENDDATA in the following data file blendb.dat:

        !   [COST AVAIL GRADE]
BLENDDATA: [ [85   60   2.1]
             [93   45   6.3] ]

In the initializations block we need to indicate the label of the data record and in which order the data of the three arrays is given:

initializations from 'blendb.dat'
 [COST,AVAIL,GRADE] as 'BLENDDATA'
end-initializations

Re-running the model with new data

There is a problem with the model we have just presented — the name of the file containing the costs date is hard-wired into the model. If we wanted to use a different file, say blend2.dat, then we would have to edit the model, and recompile it.

Mosel has parameters to help with this situation. A model parameter is a symbol, the value of which can be set just before running the model, often as an argument of the run command of the command line interpreter.

model "Blend 2"
 uses "mmxprs"

 parameters
  DATAFILE="blend.dat"
 end-parameters

 declarations
  REV = 125                      ! Unit revenue of product
  MINGRADE = 4                   ! Minimum permitted grade of product
  MAXGRADE = 5                   ! Maximum permitted grade of product
  ORES = 1..2                    ! Range of ores

  COST: array(ORES) of real      ! Unit cost of ores
  AVAIL: array(ORES) of real     ! Availability of ores
  GRADE: array(ORES) of real     ! Grade of ores (measured per unit of mass)

  use: array(ORES) of mpvar      ! Quantities of ores used
 end-declarations

! Read data from file
 initializations from DATAFILE
  COST
  AVAIL
  GRADE
 end-initializations

 ...

end-model

The parameter DATAFILE is recognized as a string, and its default value is specified. If we have previously compiled the model into say blend2.bim, then the command

mosel run blend2 DATAFILE="blend2.dat"

will read the cost data from the file we want. Or to compile, load, and run the model using a single command:

mosel exec blend2 DATAFILE="blend2.dat"

Notice that a model only takes a single parameters block that must follow immediately after the uses statement(s) at the beginning of the model.

Reading data from spreadsheets and databases

It is quite easy to create and maintain data tables in text files but in many industrial applications data are provided in the form of spreadsheets or need to be extracted from databases. So there is a facility in Mosel whereby the contents of ranges within spreadsheets may be read into data tables and databases may be accessed.

In addition to the documentation of the Mosel modules mmodbc and mmsheet in the Mosel language reference manual, you will find further detail and examples of using the SQL/ODBC and spreadsheet interfaces in other documents of the Xpress distribution: the whitepaper Using ODBC and other database interfaces with Mosel explains how to set up an ODBC connection and discusses a large number of examples showing different SQL/ODBC features; the whitepaper Generalized file handling in Mosel also contains several examples of the use of ODBC. To give you a flavor of how Mosel's ODBC and spreadsheet interfaces may be used, we now read the data of the blending problem from a spreadsheet and then later from a database.

The ODBC technology is a generic means for accessing databases and some spreadsheets such as certain versions of Microsoft Excel also support (a reduced set of) ODBC functionality. Mosel also provides a specific interface to Excel spreadsheets, an example of which is shown below (Section Excel spreadsheets). This interface that supports all basic tasks of data exchange should be used for working with Excel data. A generic alternative for working with spreadsheets in .xls, .xlsx, or .csv format, including on non-Windows platforms, is discussed in Section Generic spreadsheet example.

Excel spreadsheets

Let us suppose that in a Microsoft Excel spreadsheet called blend.xls you have inserted the following into the cells indicated:

Table 3.1: Spreadsheet example data

	A	B	C	D	E	F
1
2		ORES	COST	AVAIL	GRADE
3		1	85	60	2.1
4		2	93	45	6.3
5

and called the range B3:E4 MyRange.

The following model reads the data for the arrays COST, AVAIL, and GRADE from the Excel range MyRange. Note that we have added "mmsheet" to the uses statement to indicate that we are using the Mosel spreadsheet module.

model "Blend 3"
 uses  "mmxprs", "mmsheet"

 declarations
  REV = 125                      ! Unit revenue of product
  MINGRADE = 4                   ! Minimum permitted grade of product
  MAXGRADE = 5                   ! Maximum permitted grade of product
  ORES = 1..2                    ! Range of ores

  COST: array(ORES) of real      ! Unit cost of ores
  AVAIL: array(ORES) of real     ! Availability of ores
  GRADE: array(ORES) of real     ! Grade of ores (measured per unit of mass)

  use: array(ORES) of mpvar      ! Quantities of ores used
 end-declarations

! Read data from spreadsheet blend.xls
 initializations from "mmsheet.excel:blend.xls"
  [COST,AVAIL,GRADE] as "MyRange"
 end-initializations

 ...

end-model

Instead of naming the ranges in the spreadsheet it is equally possible to work directly with the cell references (including the worksheet name, that is, `Sheet1' in our case):

 initializations from "mmsheet.excel:blend.xls"
  [COST,AVAIL,GRADE] as "[Sheet1$B3:E4]"
 end-initializations

or alternatively, work with row and column counters:

 initializations from "mmsheet.excel:blend.xls"
  [COST,AVAIL,GRADE] as "[Sheet1$R3C2:R4C5]"
 end-initializations

And we can also select specific columns from a range:

 initializations from "mmsheet.excel:blend.xls"
  GRADE as "MyRange(#1,#4)"
 end-initializations

If the range definition contains the header line with column titles (so, MyRangeWithHeader is the area B2:E4) we can also select specific columns via their titles:

 initializations from "mmsheet.excel:blend.xls"
  GRADE as "skiph;MyRangeWithHeader(ORES,GRADE)"
 end-initializations

Database example

If we use Microsoft Access, we might have set up an ODBC DSN called MSAccess. NB: this is where to check whether the DSN is set up with Windows 2000 or XP: Start » Settings » Control Panel » Administrative Tools » Data Sources (ODBC) » ODBC drivers.
Suppose we are extracting data from a table called MyTable in the database blend.mdb. There are just the four columns ORES, COST, AVAIL, and GRADE in MyTable, and the data are the same as in the Excel example above. We modify the example above to be

model "Blend 4"
 uses  "mmxprs", "mmodbc"

 declarations
  REV = 125                      ! Unit revenue of product
  MINGRADE = 4                   ! Minimum permitted grade of product
  MAXGRADE = 5                   ! Maximum permitted grade of product
  ORES = 1..2                    ! Range of ores

  COST: array(ORES) of real      ! Unit cost of ores
  AVAIL: array(ORES) of real     ! Availability of ores
  GRADE: array(ORES) of real     ! Grade of ores (measured per unit of mass)

  use: array(ORES) of mpvar      ! Quantities of ores used
 end-declarations

! Read data from database blend.mdb
 initializations from "mmodbc.odbc:blend.mdb"
  [COST,AVAIL,GRADE] as "MyTable"
 end-initializations

 ...

end-model

With ODBC, we can use the field names to select specific columns from a table:

 initializations from "mmodbc.odbc:blend.mdb"
  GRADE as "MyTable(ORES,GRADE)"
 end-initializations

Instead of using the initializations block that automatically generates SQL commands for reading and writing data it is also possible to employ SQL statements in Mosel models. The initializations block in the model above is equivalent to the following sequence of SQL statements:

 SQLconnect('DSN=MSAccess; DBQ=blend.mdb')
 SQLexecute("select * from MyTable ", [COST,AVAIL,GRADE])
 SQLdisconnect

The SQL statement "select * from MyTable" says `select everything from the table called MyTable'. By using SQL statements directly in the Mosel model it is possible to have much more complex selection statements than the ones we have used.

To use other databases, for instance a mysql database (let us call it blend), we merely need to modify the connection string — provided that we have given the same names to the data table and its columns:

 initializations from "mmodbc.odbc:DSN=mysql;DB=blend"

ODBC, just like Mosel's text file format, may also be used to output data. The reader is referred to the ODBC/SQL documentation for more detail.

Generic spreadsheet example

We shall work once more with the Microsoft Excel spreadsheet called blend.xls shown in Table Spreadsheet example data where we have defined the range B3:E4 MyRange.

This spreadsheet can be accessed via MS Excel as shown above. However, this access method is only available on platforms where Excel is installed. The module mmsheet also provides more generic interfaces for working with .xsl, .xlsx and CSV format files (usable, for example, under Linux or MacOS). The corresponding Mosel model looks as follows.

model "Blend 3 (spreadsheet)"
 uses "mmsheet", "mmxprs"

 declarations
  REV = 125                      ! Unit revenue of product
  MINGRADE = 4                   ! Minimum permitted grade of product
  MAXGRADE = 5                   ! Maximum permitted grade of product
  ORES = 1..2                    ! Range of ores

  COST: array(ORES) of real      ! Unit cost of ores
  AVAIL: array(ORES) of real     ! Availability of ores
  GRADE: array(ORES) of real     ! Grade of ores (measured per unit of mass)

  use: array(ORES) of mpvar      ! Quantities of ores used
 end-declarations

! Read data from spreadsheet blend.xls
 initializations from "mmsheet.xls:blend.xls"
  [COST,AVAIL,GRADE] as "MyRange"
 end-initializations

 ...

end-model

The only modification we have made is quite subtle: in the filename we have replaced mmsheet.excel by mmsheet.xls.

Variant: Assuming that we have saved the data from our spreadsheet into the CSV format file blend.csv, we need to switch to the CSV interface for accessing this data file. A CSV file contains a single worksheet and it is not possible to define named ranges. We therefore now refer directly to the cells via the cell references (similarly to what has been shown for Excel in Section Excel spreadsheets but without stating a sheet name):

 initializations from "mmsheet.csv:blend.csv"
  [COST,AVAIL,GRADE] as "[B3:E4]"
 end-initializations

or alternatively, using row and column counters:

 initializations from "mmsheet.csv:blend.csv"
  [COST,AVAIL,GRADE] as "[R3C2:R4C5]"
 end-initializations