Decision Optimization OPL model input and output data file formats

Input and output data formats for Decision Optimization OPL models

Last updated: Dec 05, 2024

For deployment you must use the following input and output data formats for OPL Decision Optimization models.

OPL data formats

In an OPL model you must declare a tupleset, for each table that you use. The following example shows an extract of an OPL model:

tuple parameters {
	int maxTrucks;
	int maxVolume;
}
parameters Parameters = ...;

tuple location {
  key string name;
}
{location} Hubs = ...;

tuple spoke {
  key string name;
  int     minDepTime;
  int     maxArrTime;
};
{spoke} Spokes = ...;

dvar int+ TruckOnRoute[Routes][TruckTypeIds] in 0..Parameters.maxTrucks;

[...]

minimize TotalCost;

subject to {
	[...]
}

Input data

The input data can be populated from an external data source. The input data for OPL models can be provided in one of these formats:

.csv file
.dat file
JSON document
Microsoft Excel workbook (.xlsx and .xls) files are deprecated.
To connect to Microsoft Excel files, use a data-connector. The data-connector transforms your Excel file into a .csv file. For more information, see Referenced data.

.dat file

All OPL data structures are supported. For example,

{Parameters = <100, 5000>;

Hubs = { <"G">, <"H"> };

Spokes = { <"A", 360, 1080>, <"B", 400, 1150> };

JSON document or Microsoft Excel workbook

You can use only tuples and tuple sets as inputs in the OPL model.

Supported types for tuple fields are int, float or string.

To map the input values to your OPL model, you must follow these rules:

The OPL element must have the same name as the JSON property or Excel worksheet.
A tuple set can be populated by a JSON property array or a worksheet.
A tuple element can be populated by a JSON property object, or with a single row Excel sheet.

JSON format can be used for OPL model integration so that it is easier to generate input data and to parse the results.

{
  "Parameters": {
    "maxTrucks": 100,
    "maxVolume": 5000
  },
  "Hubs": [
    {  "name": "G"  },
    {  "name": "H"  }
  ],
  "Spokes": [
    { "name": "A",
      "minDepTime": 360,
      "maxArrTime": 1080 },
    { "name": "B",
      "minDepTime": 400,
      "maxArrTime": 1150  },
   . . .
}

You can use an Excel file instead of using a .dat file. This option is different from IBM ILOG CPLEX Optimization Studio where the Excel file must be specified as an external source in the .dat file. In Decision Optimization the Excel file must be included with the model and cannot be called from a .dat file.

The limitation on only using tuples and tuple sets as OPL input is to facilitate integration with data sources. For example, SQL data sources can be accessed and data-streamed with a minimum of effort; NoSQL data sources can be accessed and data can be transformed automatically to tables. If necessary, the optimization model developer can reformulate the data to populate other data structures during the optimization, but this manipulation must not affect the input or output data.

Output data

If your output is a text file, then the objective function, and values of the decision variables are provided in an unstructured format.

If your output format is JSON, .csv, or Excel, then you must define what you want to export back to the client in the post-processing block. Post-processing is all the code that follows the subject to section in the .mod file. Thus to define JSON, .csv, or Excel output, you must declare tuple or tuple sets in the post-processing.

If you do not declare output elements in the post-processing block of the .mod file, no output data is generated.

In the following example, the output file will contain the value of Result and NbTrucksOnRouteRes and the objective function because these elements are defined in the post-processing.

subject to {
   [...]
}

tuple result {
  float totalCost;
}
result Result;

execute {
     Result.objValue = cplex.getObjValue();
}

tuple nbTrucksOnRouteRes {
  key string	spoke;
  key string	hub;
  key string	truckType;
  int			nbTruck;
}
{nbTrucksOnRouteRes} NbTrucksOnRouteRes =
  {<r.spoke, r.hub, t, TruckOnRoute[r][t]> | r in Routes, t in TruckTypeIds :
                                                              TruckOnRoute[r][t] > 0};