Successfull decision support systems depend on high quality data. These days
such data is often found in relational databases, ranging form small Access
databases to full blown ERP systems. Being able to move data reliably from those systems
into an optimization model can be an important first step in building an effective
For larger data sets, a proper database can be a more reliable data store than
a spreadsheet. Where spreadsheets have relative large error rates in their
content, databases have tools to help protect data such as data types and
A number of tools have been developed to make it easier to exchange data between
relational databases and GAMS. Most of these tools are now part of the standard GAMS
Often it is a good approach to handle data extraction in separate step and store the
results in a GDX file. Using the above tools we have converted some very large databases
with hundreds of tables to a single large GDX file.
In many cases GDX files are useful as intermediate data stores. The attached diagram shows a complicated
data collection step where data from several sources are combined in GDX file, ready to be used
by a regional partial equilibrium model. The data sources include Microsoft Access databases, Excel
spreadsheets and GAMS include files.
GDX files can also be used as intermediate data stores within a GUI based application. This example
runs GAMS from MS Access. The data is extracted by queries and stored in a GDX file, which is read by the
GAMS model that solves a Traveling Salesman Problem (TSP) or a Minimum Spanning Tree (MST) model.
The solution is stored back in the database and displayed as a graph.
The databases for this model were converted from
MS Access and Excel files using SQL2GMS into a single large GDX file.
The combined GDX file can be used as data store for agricultural policy evaluation models.
CGE Modelers can concentrate on their GAMS models and don't need to worry further about
Other database tools
Some older and more exotic tools are described here.
- GDXviewer: provides a quick way to generate SQL insert/update scripts.
- GAMS/ORAQueue: a queueing package to submit GAMS jobs in an Oracle environment. A web based submission tool is used to submit GAMS jobs using a standard browser. The code can be used to develop robust Web based optimization applications.
- Mijdbc: a JDBC driver for Illustra, an older commercial object-relational database.