Blend - data input from external sources
|
|
|
| Type: | Blending problem |
| Rating: | 2 (easy-medium) |
| Description: | 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.
|
| File(s): | blend.mos, blend2.mos, blend3.mos, blend3c.mos, blend3e.mos, blend4.mos, blend4a.mos |
| Data file(s): | blend.dat, blendb.dat, blend.csv, blend.sqlite, blend.xls, blend.mdb |
|
|
|
| blend.mos |
(!******************************************************
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
|
| blend2.mos |
(!******************************************************
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
|
| blend3.mos |
(!******************************************************
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
|
| blend3c.mos |
(!******************************************************
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
|
| blend3e.mos |
(!******************************************************
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
|
| blend4.mos |
(!******************************************************
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')
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
|
| blend4a.mos |
(!******************************************************
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 Access database blend.mdb
SQLconnect('DSN=Access; DBQ=blend.mdb')
! 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 SQLite database blend.sqlite
SQLconnect('blend.sqlite')
! Read data from the mysql database blend
! SQLconnect('DSN=mysql; DB=blend')
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
|
© 2001-2020 Fair Isaac Corporation. All rights reserved. This documentation is the property of Fair Isaac Corporation (“FICO”). Receipt or possession of this documentation does not convey rights to disclose, reproduce, make derivative works, use, or allow others to use it except solely for internal evaluation purposes to determine whether to purchase a license to the software described in this documentation, or as otherwise set forth in a written software license agreement between you and FICO (or a FICO affiliate). Use of this documentation and the software described in it must conform strictly to the foregoing permitted uses, and no other use is permitted.