Packaging Optimization Applications

After implementation of the optimization model it is often needed to package the model into a user-friendly software package or to embed the optimization routines into a larger system. This can mean interfacing with enterprise databases or desktop office applications. Below we mention some possible congifurations. Although these examples are simple, the background machinery to run GAMS is fairly complex. First it is needed to find out where GAMS is installed. The path needs to be extended to be able to call GAMS and to find the needed DLL's. Finally complexities are introduced to support transparently both GDX file formats of GAMS version 22.5 and before and of GAMS version 22.6. These examples can be used as a starting point for implementing a practical application.

Using Excel as front-end

Excel is a powerful spreadsheet program with more than enough bells-and-whistles to make it suitable as a front-end for optimization applications. With an installed base of more than 500 million, there is a good chance the user is already familiar with Excel. The examples in this section use VBA (Visual Basic for Applications) code to call GAMS and to exchange data with GAMS.

The following algorithm has been implemented in these examples:
  1. Find the GAMS system directory.
  2. Augment the path with the GAMS system directory so that we can call GAMS and make use of the GDX DLL's.
  3. Locate the Windows TEMP directory.
  4. Copy the GAMS model to the temp directory.
  5. Write GDX files with input data for the model.
  6. Call GAMS to solve the model.
  7. Read GDX files with solution data.
  8. Update the GUI front-end.

An Excel-based Portfolio Example

This model implements the calculation of an efficient frontier, as graph that is used in portfolio theory to depict pareto optimal portfolio's. In this application the user can change what investment instruments are available. When the solve button is pressed a GDX input file is created, a GAMS model is solved containing a number of NLP models, and the solution results in the form of a GDX file are read back. The graph is then updated to refect the user specifications. This application is small enough to fit in the student/demo version of GAMS. Although this is a small one sheet application, it can be used as the basis for more complex applications with multiple sheets.

Sub solve()
   Dim WorkDir As String
   Dim SysDir As String
   Dim DllName As String
   WorkDir = TempDir()
   Call ClearSolution
   Call AddGAMSPath(SysDir) ' needed to find gdxio.dll and gams.exe
   Call ExportGDXFile(WorkDir, DllName)
   Call WriteGAMSModel(WorkDir & "portfolio.gms")
   Call RunGAMS(WorkDir & "portfolio.gms", WorkDir)
   Call ImportGDXFile(WorkDir)
End Sub
 This code implements the basic algorithm described above.
Downloads

An Excel-based Sudoku Example

This model requires a valid GAMS and GAMS/Cplex license to run. In this application we solve a large Sudoku problem using a MIP formulation. Cplex can solve this (large) model quickly without doing any branch-and-bound nodes. The presolver solves this model completely. The spreadsheet uses VBA code to call GAMS and to read and write GDX files to exchange data with GAMS. The model generated is too large to fit in the Student/Demo version of GAMS.
Downloads

Excel based advanced reporting with Pivot Tables

This application allows the user to perform sensitivity analysis by varying some parameters. The Excel spreadsheet will generate and evaluate the policy scenarios and present the results in Pivot Tables. Pivot Tables are a powerful tool to present complex, voluminous data, and can be easily customized to present the model results in a meaningful way. In addition, associated Pivot Charts can be used to create powerful Charts providing extra insight in the results. The application also allows to run scenario analysis from a Memory Stick. In this case a minimal run-time GAMS system can be placed on the USB stick, creating a portable application.

Using Access as front-end

Much of the VBA code used in the above examples can be used when calling GAMS from MS Access. An important extension is to allow for a progress window, with a multi-line text-box that shows progress by displaying the log. For longer running jobs it is also important to introduce an Interrupt (Ctrl-C) and Stop (Ctrl-Break) facility. We have implemented this using Access as shown in the picture above. This approach can also be used in an Excel based application.
Note: the progress window is implemented using a TextBox component from MS Forms 2.0.
Code to interrupt a long running GAMS job. The low-level code to handle the code-injection and the remote-thread management is implemented as a C DLL callable by VBA. 
Private Declare Function GamsInterrupt Lib "gamsinterrupt.dll" _
  (ByVal pid As Long, ByVal InterruptType As Long) As Long
'----------------------------------------------------
Private Sub SendInterrupt(InterruptType As Long)
   Dim rc As Long
   Dim pid As Long
   pid = getpid()
   rc = GamsInterrupt(ByVal pid, ByVal InterruptType)
End Sub
'----------------------------------------------------
Private Sub InterruptButton_Click()
' try to interrupt
 Call SendInterrupt(0)
End Sub
'----------------------------------------------------
Private Sub KillButton_Click()
' try to kill
 Call SendInterrupt(1)
End Sub
 

$if not set db $error db is not set

$onecho >  mdb2gms.txt
i=%db%
x=db.gdx
q1=select distinct code from cities
s1=i
q2=select city1,city2,distance from distances
p2=d
$offecho

$call =mdb2gms.exe @mdb2gms.txt
set i 'cities';
alias (i,j,k);

parameter d(i,j) 'distances';
$gdxin db.gdx
$load i,d
display i,d;
 Passing data from Access to GAMS is very easy with MDB2GMS. We can use this GAMS code to handle this.

The %db% macro can be passed on to GAMS via the command line.

The complete example has a main menu with some buttons, a progress window described above and a report window displaying the traveling salesman tour or the minimum spanning tree as found by the optimization model.

The basic architecture as demonstrated in this mini-application has been used in several commercial systems.

Downloads

Microsoft Solver Foundation Examples

The new Microsoft Solver Foundation is a .NET optimization library and Excel GUI. It includes solver for LP, MIP and CSP models and includes a simple modeling language OML. The Excel GUI is useable to build quick prototypes. More refined implementations can be build using the .NET libraries.

Here are some examples:

Linear Programming
This is the trnsport.gms model from the GAMS model library implemented in OML. All the input data are in the spreadsheet. The OML modeling language can represent this model very cleanly.
Contraint Programming
The problem of finding Magic Squares can be conveniently formulated as a Constraint Programming model. The all-different constraint can be written using the unequal condition. Using the "Domain over Weighted Degree" heuristic we can solve problems with N=10 very quickly.

Home