Database Connectivity

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 application.

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 referential integrity.

GAMS

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 distribution.
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.

GDX files

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 Access tables.

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.

Home