We are very excited to introduce a new way of collecting data from a range of external sources, transform it, and make it available to GAMS models. Enter “GAMS Connect”! Below you can read a short description of this new tool set, and why we think it will be incredibly useful to all GAMS modelers.
Background
In the realm of software, GAMS is one of the products on the more long-lived end of the spectrum. The first published record of the initial attempts to develop a general algebraic modeling system by Alex Meeraus leads back to 1976 (International Symposium on Mathematical Programming, Budapest, p. 185) The first commercial version of GAMS was available in 1987, and since then many people have contributed to the further development of the GAMS distribution. Since GAMS does not have a module system like more conventional programming languages, a lot of these contributions were submitted as little command line tools in the Unix spirit, e.g. for reading or writing Excel files, CSV files, accessing Microsoft Access databases, interfacing with Matlab, and many more. This system has been working quite well for many years, but it is becoming increasingly difficult to keep all of these tools updated, and also to make them available for all platforms supported by GAMS. Also, the syntax for using the different tools is not uniform and can be confusing to the user. We therefore felt a more modern and unified way of reading and writing data from and to different formats was in order. As a big step in this direction, we can now unveil “GAMS Connect”. GAMS Connect builds upon the concept of “extract, transform, load” (ETL), which aims to get data from a range of different sources into a unified, central data storage (the “Connect Database”), and from there to other formats, with the help of reader agents, transformer agents, and writer agents (Fig 1).
Fig. 1: Multiple Agents share the same central database
This concept makes possible a pluggable data import / export system, which is configured via YAML syntax. Currently GAMS Connect supports CSV, GDX, and Excel as external file formats for reading and writing. Here is a simple example:
- CSVReader:
file: distance.csv
name: distance
indexColumns: [1, 2]
valueColumns: [3]
fieldSeparator: ';'
decimalSeparator: ','
- CSVReader:
file: capacity.csv
name: capacity
indexColumns: [1]
valueColumns: [2]
- GAMSWriter:
symbols:
- name: distance
newName: d
- name: capacity
newName: a
These lines instruct GAMS Connect to read two CSV files. From the first (distance.csv), we read values from column 3 into a symbol with the name “distance”, using index values from columns 1 and 2. From the second CSV file (capacity.csv) we read values from column 2 into a symbol named “capacity”, using index values from column 1. At this stage, those values reside in the GAMS Connect database only, and we can now make them available to GAMS. This is done in the last “GAMSWrite” block, which creates the symbols “d” and “a” from the previously collected data.
This way of instructing GAMS Connect to read and write data is obviously very flexible and powerful, and you can find more complex examples in the documentation .
The Connect YAML syntax can be utilized in three different places:
- Via GAMS command line parameters “ConnectIn” and “ConnectOut”
- Via embedded code Connect (likely the most common case)
- Via a standalone command line utility “gamsconnect”
Once data is in the Connect database, and before writing to the GAMS database, you can use the “Projection” agent to project and aggregate data onto a reduced index space of a GAMS symbol using statistical functions like max, min, mean, median and more. If that is not enough, you can even use Python code inside the YAML instructions to implement very complex data manipulation procedures.
Design Decisions
During the conception of GAMS Connect, we made a few very deliberate decisions:
- Connect agents are “simple” by design and each one supports exactly one functionality. The power of GAMS Connect stems from the ability to string multiple agents together via YAML.
- Connect agents provided by us will be platform independent.
- All agents will be controlled by a consistent syntax and hence make life easier for our users (they are also case sensitive, which is different from usual GAMS syntax).
- Code readability is king, therefore we will avoid abbreviations and instead use long, explicit, camelCased parameter names.
- Fail early: the YAML syntax is validated first, before executing any instructions, to allow early discovery of mistakes.
Try it!
We are very excited about GAMS Connect and encourage you to try it out for yourself. Update to GAMS 39 and give it a spin! Also make sure to check our new releases for more GAMS Connect functionality in the future. Over time we will implement more agents (e.g. for SQL databases, HTML, Txt and more), integrate Connect with GAMS Studio, and even allow creating your own Connect agents in Python!