Monday, April 18, 2011

Live databases

Recently I was helping out with some AMPL modeling effort. The client used the ODBC table facilities of AMPL to read data directly from an SQL server database. Although this sounds very efficient and sexy, in many cases it may be better to not to get the data directly from a live database. The main issue is that runs may not be reproducible (the database content may change). Also it is difficult to share by email or ftp a corporate database. Instead I often suggest to make a snapshot in a format that is somewhat more convenient to share. In this case we used Access as a staging database. This will allow us to easily save instances and get these into the hands of external consultants. Actually I am thinking if it would be worthwhile to invest a little bit of time to generate AMPL data files (text). This will lead to smaller files, and AMPL can read those files very fast. Especially when the model is under active development this scheme is very convenient.

For another client I used a similar approach with GAMS. The client generated one big XML file from SQL Server, and we imported this to Access where we added some extra data checks and data preparation steps. The next step was to convert this to a GDX file which can be imported efficiently into GAMS. In this case we moved some of the data preparation into Access while this could have been done also in GAMS. The main reason is that there are developers available (both externally and in-house) with good knowledge of Access but without knowledge and experience in GAMS. Available skill sets can be a determining factor in deciding what to do where in the whole chain.