Blend - data input from external sources

This example is taken from the 'Mosel User Guide', Section 2.2. A blending example.

In many applications data are provided in the form of spreadsheets or need to be extracted from databases. Mosel has facilities whereby the contents of ranges within spreadsheets may be read into data tables and databases may be accessed.

Mosel's ODBC interface (module mmodbc) may be used to read the data from databases. 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. The ODBC interface can be used in initializations blocks that automatically generate the required SQL statments and it is also possible to employ SQL statements directly in Mosel models (see blend4.mos).

Mosel also provides specific interfaces to Excel spreadsheets through the module mmsheet. These interfaces that support all basic tasks of data exchange (model files blend3*.mos) should be used for working with Excel data.

• blend.mos: Reading data from text files (requires blend.dat or blendb.dat)
• blend2.mos: Using runtime parameters (requires blend.dat)
• blend3.mos: Reading data from an Excel spreadsheet, using the generic spreadsheet driver (requires blend.xls)
• blend3c.mos: Reading data in CSV format (requires blend.csv)
• blend3e.mos: Reading data from an Excel spreadsheet, using the excel I/O driver (requires blend.xls)
• blend4.mos: Reading data from a database (requires blend.mdb or blend.sqlite)

(!******************************************************
   Mosel User Guide Example Problems
   ================================= 

   file blend.mos
   ``````````````
   Reading data from file.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2001
*******************************************************!)

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

! Alternatively, uncomment to read data from blendb.dat
(!
 initializations from 'blendb.dat'
  [COST,AVAIL,GRADE] as 'BLENDDATA'
 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
 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

(!******************************************************
   Mosel User Guide Example Problems
   ================================= 

   file blend2.mos
   ```````````````
   Using parameters.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2001
*******************************************************!)

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

! 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
 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

(!******************************************************
   Mosel User Guide Example Problems
   ================================= 

   file blend3.mos
   ```````````````
   Reading data from an Excel spreadsheet
   - using the generic spreadsheet driver -
   
   (c) 2012 Fair Isaac Corporation
       author: S. Heipcke, Nov 2012
*******************************************************!)

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 ***
(! Spreadsheet range contains data only (no header line):
 initializations from "mmsheet.xls:blend.xls"
  [COST,AVAIL,GRADE] as "MyRangeNoHeader"      ! or: "[Sheet1$B3:E4]" 
 end-initializations
!)

! Or (spreadsheet range includes a header line as with ODBC):
 initializations from "mmsheet.xls:blend.xls"
  [COST,AVAIL,GRADE] as "skiph;MyRange"        ! or: "skiph;[Sheet1$B2:E4]" 
 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
 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

(!******************************************************
   Mosel User Guide Example Problems
   ================================= 

   file blend3c.mos
   ````````````````
   Reading data from an Excel spreadsheet
   - using the CSV driver -
   
   (c) 2013 Fair Isaac Corporation
       author: S. Heipcke, Jan. 2013
*******************************************************!)

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.csv ***
! Spreadsheet range contains data only (no header line):
 initializations from "mmsheet.csv:blend.csv"
  [COST,AVAIL,GRADE] as "[B3:E4]"    ! or: "[R3C2:R4C5]"
 end-initializations

(! Or (spreadsheet range includes a header line as with ODBC):
 initializations from "mmsheet.csv:blend.csv"
  [COST,AVAIL,GRADE] as "skiph;[B2:E4]" 
 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
 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

(!******************************************************
   Mosel User Guide Example Problems
   ================================= 

   file blend3e.mos
   ````````````````
   Reading data from an Excel spreadsheet
   - using the excel IO driver -
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2007
*******************************************************!)

model "Blend 3" 
 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 ***
(! Spreadsheet range contains data only (no header line):
 initializations from "mmsheet.excel:blend.xls"
  [COST,AVAIL,GRADE] as "MyRangeNoHeader"      ! or: "[Sheet1$B3:E4]" 
 end-initializations
!)

! Or (spreadsheet range includes a header line as with ODBC):
 initializations from "mmsheet.excel:blend.xls"
  [COST,AVAIL,GRADE] as "skiph;MyRange"        ! or: "skiph;[Sheet1$B2:E4]" 
 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
 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

(!******************************************************
   Mosel User Guide Example Problems
   ================================= 

   file blend4.mos
   ```````````````
   Reading data from a database.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2002, rev. Sep. 2014
*******************************************************!)

model "Blend 4" 
 uses  "mmodbc", "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 the SQLite database blend.sqlite
 initializations from "mmodbc.odbc:blend.sqlite"
  [COST,AVAIL,GRADE] as "MyTable"
 end-initializations

(! Alternatively:
! Read data from the SQLite database blend.sqlite
 SQLconnect('blend.sqlite')

! Read data from the mysql database blend
! SQLconnect('DSN=mysql; DB=blend')

! Test connection status
 if not getparam("SQLsuccess") then
  writeln("Database connection failed")
  exit(1)
 end-if

 SQLexecute("select * from MyTable ", [COST,AVAIL,GRADE])
 SQLdisconnect
!)

! 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
 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

(!******************************************************
   Mosel User Guide Example Problems
   ================================= 

   file blend4.mos
   ```````````````
   Reading data from a database.
   
   (c) 2008 Fair Isaac Corporation
       author: S. Heipcke, 2002, rev. Sep. 2014
*******************************************************!)

model "Blend 4" 
 uses  "mmodbc", "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 the Access database blend.mdb
 initializations from "mmodbc.odbc:blend.mdb"
  [COST,AVAIL,GRADE] as "MyTable"
 end-initializations

(! Alternatively:
! Read data from the SQLite database blend.sqlite
 initializations from "mmodbc.odbc:blend.sqlite"
  [COST,AVAIL,GRADE] as "MyTable"
 end-initializations

! Read data from the Access database blend.mdb
 SQLconnect('DSN=Access; DBQ=blend.mdb')

! Read data from the SQLite database blend.sqlite
 SQLconnect('blend.sqlite')

! Read data from the mysql database blend
! SQLconnect('DSN=mysql; DB=blend')

! Test connection status
 if not getparam("SQLsuccess") then
  writeln("Database connection failed")
  exit(1)
 end-if

 SQLexecute("select * from MyTable ", [COST,AVAIL,GRADE])
 SQLdisconnect
!)

! 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
 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