Importing data from a variety of corporate database systems into optimization models is frequently an essential requirement for optimization projects. One of the advanced features of MPL is the database connection option that directly links MPL with relational databases and other data sources. This option enables the model developer to gather both indexes and data values from various data sources and import them directly into the model. After the model has been optimized, the solution output can be exported back into the database. This, along with the run-time features of MPL, allows the model developer to easily create customized end-user applications for optimization using the built-in data entry and reporting capabilities of the database.
The database connection in MPL has the ability to access data from many different sources, such as databases, Excel spreadsheets, external data files, and the Internet. This gives the model developer the flexibility to choose the most efficient and convenient way to incorporate the data into the model. Among the data formats that are supported by MPL are: Microsoft Access and Excel, ODBC, Paradox, FoxPro, Dbase, SQL Server, and Oracle.
Relational databases are designed to store and retrieve structured data enabling the user to access corporate data such as plants, products, machines, and etc., on the symbolic level. The purpose of the modeling language is to take this structured data and generate a mathematical matrix from the model that the optimization solver can process.
One alternative to using a modeling language is to write a customized matrix generator in a programming language. This kind of programming is very difficult and causes the resulting application to be highly dependent on specific methods and libraries, of both the database and the solver. MPL offers clear benefits to this approach, because with the database connection it can automatically map the columns of the database tables to the indexes and data vectors of the model without involving any programming. This gives the model developer unprecedented flexibility and expressive power and the ability to focus mainly on formulating the model and connecting the data instead of programming.
In the following sections, we will describe each import and export operation in detail. Refer to the Database Options dialog box in Chapter 4.9: The Options Menu: Database Options to select the database you will be using.