In DEX 2.1.0.1 we included support for reading from and writing to databases, similar to the way the DEX library supports data exchange with CSV, Excel and Parquet files. The library supports the SQLite, MySQL, PostgreSQL and SQLServer databases.
Generating application databases
Based on a DEX mapping, the library is capable of generating the database tables described in the mapping on-the-fly.
The generated database schema can optionally be extended with tables to hold the elements for all root sets in your data. In that case, the tuples for multi-dimensional data tables refer to these root set tables via foreign keys, for additional data integrity and performance.
In addition, every table in the generated database can be extended to hold multiple versions of the data, e.g. to store the input data and results for multiple scenarios.
With the existing functionality in DEX to auto-generate mappings for a single table, or entire datasets consisting of multiple tables, solely based on identifier annotations, this offers app developers an easy way to generate a fully functional and well-performing application database structure for any AIMMS app that is capable of storing application data for multiple scenarios.
Comparison to existing database support in AIMMS
The existing support for reading from and writing to databases in AIMMS was primarily aimed to allow app developers to work with existing, external, databases, with a database schema outside the control of the app developer. It supports modes to read slices of the data in such tables, or to insert, update or overwrite data in the database tables, often leading to performance issues because of peculiarities in the underlying schema that may influence the SQL statements we generate and/or the complexity of the underlying SQL statements and mechanisms we employ to ensure data integrity.
In contrast, the database support in the DEX library is much more opinionated. It takes control to create a database that we consider to be a useful and well-performing application database structure for most AIMMS apps. It closely fits with the way AIMMS organizes its multi-dimensional data, with support for storing multiple versions of that data.
The DEX library only supports inserting a new version of the data into a database table. Such a version can act as a snapshot of the application data to exchange with external applications, or can be the result of a specific import of data from an external application.
We strongly believe in considering a version of the data stored in the application database to be immutable. This makes computations reproducible, and allows to reliably compare one version of the data to another. As a consequence we offer no functions to update, or extend, an existing version of the data in a table, but rather have you store a new version of the data.
Comparison to CDM
Many customers have used CDM as a means to automatically create an application database around an AIMMS app, and found out that importing multiple versions of extensive input data from external sources into a CDM database may eventually lead to performance problems.
This is because CDM was never intended as a means of exchanging data with external applications, or even to store very large datasets computed by an AIMMS application.
The main design goal of CDM was to add multi-user support to interactive AIMMS apps, allowing multiple users working in separate AIMMS sessions to make simultaneous changes to the shared data in the app, while still allowing them to run scenarios and evaluate their usability before sharing such changes with the other users.
As data in a CDM database may change continuously, it is hardly usable to provide a consistent data set for exchange with an external application, as its contents is effectively a moving target.
DEX database support is complementary to CDM, explicitly aiming at exchanging snapshots of data either imported from external systems or intended for external applications. Because of the intended immutability of versions of the data in DEX-generated databases, it provides perfect reproducibility.
Alternatives
We emphasize that a DEX-generated application database is certainly not the only way to accomplish a storage scheme capable of storing and exchanging multiple versions of the data relevant to an AIMMS app.
An equally valid, or in certain scenarios maybe even preferable, approach may be to store a scenario, or an external dataset to import, as a collection of DEX- or externally generated Parquet or CSV files in a single location, e.g. folder on a local or shared disk, an AWS S3 bucket, or a container in Azure Blob storage.
The database support in DEX just offers you more options, allowing you to choose the data storage and exchange mechanism of your preference.