Continuing from the PERT example used in the Importing Data with ODBC in a PERT Model section, we can add modifications to export the solution values of the earliest and latest start times (ES and LS) back out to the PERTODBC data source. We will put these into a blank table titled SOLUTION. We will also export the members of the TASKS set in order to label our table. The data column, or field, that receives the TASKS members should be formatted as text, while the columns receiving the ES and LS attributes should be declared as numeric. Here is a look at the blank table we will be exporting to:

Access Database: PERTODBC.MDB

After modifying the model to export the data back to the TASKS table, we have (with the relevant changes in bold):

SETS:

  TASKS: TIME, ES, LS, SLACK;

  PRED( TASKS, TASKS);

ENDSETS

 

DATA:

  TASKS = @ODBC( 'PERTODBC', 'TASKS', 'TASKS');

  PRED = @ODBC( 'PERTODBC', 'PRECEDENCE', 'BEFORE', 'AFTER');

  TIME = @ODBC( 'PERTODBC');

  @ODBC( 'PERTODBC', 'SOLUTION', 'TASKS',

   'EARLIEST START', 'LATEST START') =

     TASKS, ES, LS;

ENDDATA

 

@FOR( TASKS( J)| J #GT# 1:

ES( J) = @MAX( PRED( I, J): ES( I) + TIME( I))

);

 

@FOR( TASKS( I)| I #LT# LTASK:

LS( I) = @MIN( PRED( I, J): LS( J) - TIME( I));

);

 

@FOR( TASKS( I): SLACK( I) = LS( I) - ES( I));

 

ES( 1) = 0;

LTASK = @SIZE( TASKS);

LS( LTASK) = ES( LTASK);

Model: PERTODBC

With the data statement:

@ODBC( 'PERTODBC', 'SOLUTION', 'TASKS',

 'EARLIEST START', 'LATEST START') =

   TASKS, ES, LS;

we are sending the set TASKS to the text column TASKS, and the ES and LS attributes to the numeric columns EARLIEST START and LATEST START. The data table is called SOLUTION, while the ODBC data source name is PERTODBC.

Once the model has been solved, the updated data table will resemble:

Access Database: PERTODBC.MDB

At the top of the solution report window, you will also notice an export summary report. There will be one report for each @ODBC statement in the model used for exporting data. This report lists details as to the operation of the @ODBC export. In the case of our PERT model, you should see the following report:

 

   Export Summary Report

   ---------------------

   Transfer Method:       ODBC BASED

   ODBC Data Source:      PERTODBC

   Data Table Name:       TASKS

   Columns Specified:     3

      TASKS

      EARLIEST

      LATEST

   LINGO Column Length:            7

   Database Column Length:         7

 

The Transfer Method will always list "ODBC BASED" when doing ODBC exports. Next, the data source and table names are listed along with the number of columns specified and the column names. The LINGO Column Length field lists the number of elements in each of the attributes. The Database Column Length lists the length of the receiving columns in the database. In general, the LINGO Column Length will agree with the Database Column Length. If not, LINGO must either truncate its output or it will have insufficient data to fill out the columns in the database.

Export summary reports are not displayed when LINGO is in terse output mode. To place LINGO in terse output mode, click on the Terse Output checkbox on the Interface tab of the Solver|Options dialog box.

This version of the PERT model and its supporting database are contained in the SAMPLES directory. Feel free to run the model to experiment with it if you like—you will find it under the name PERTODBC. The supporting Access database file, PERTODBC.MDB, is also in the SAMPLES subdirectory and you will need to register it with the ODBC Administrator as described in ODBC Data Sources.

Note that we exported start and finish times for all the tasks in the project.  If we were dealing with a large project there could be thousands of tasks to consider.  With such an abundance of tasks, we might be interested in reporting only those tasks that lie on the critical path.  We'll modify our PERTODBC example one last time to accomplish this using the @WRITEFOR reporting function.

For those unfamiliar with the concept of a critical path, it is the subset of tasks such that if any are delayed the entire project will be delayed.  Generally, and somewhat counterintuitive to what one would expect, the set of tasks on the critical path will tend to be quite small compared to the total number of tasks in a large project.  A task is considered to be on the critical path when its earliest start time is equal to its latest start time (i.e., there is no slack with respect to when the task must be started).

Below, we have modified PERTODBC to export only those tasks on the critical path.

MODEL:

SETS:

  TASKS: TIME, ES, LS, SLACK;

  PRED( TASKS, TASKS);

ENDSETS

 

DATA:

  TASKS = @ODBC( 'PERTODBC', 'TASKS', 'TASKS');

  PRED = @ODBC( 'PERTODBC', 'PRECEDENCE', 'BEFORE', 'AFTER');

  TIME = @ODBC( 'PERTODBC');

  @ODBC( 'PERTODBC', 'SOLUTION', 'TASKS',

   'EARLIEST START', 'LATEST START') =

    @WRITEFOR( TASKS( I) | ES( I) #EQ# LS( I):

     TASKS( I), ES( I), LS( I));

ENDDATA

 

@FOR( TASKS( J)| J #GT# 1:

ES( J) = @MAX( PRED( I, J): ES( I) + TIME( I))

);

 

@FOR( TASKS( I)| I #LT# LTASK:

LS( I) = @MIN( PRED( I, J): LS( J) - TIME( I));

);

 

@FOR( TASKS( I): SLACK( I) = LS( I) - ES( I));

 

ES( 1) = 0;

LTASK = @SIZE( TASKS);

LS( LTASK) = ES( LTASK);

END

We specify a conditional expression to test for the earliest start time equaling the latest start time, thereby restricting the list of exported tasks to those that lie on the critical path.  Note that if you limit the number of output records there won't be enough records to completely fill our output table.  We can have LINGO fill out any extra fields with blanks and zeroes by enabling the Fill Out Ranges and Tables option.  Doing this, the solution table will resemble the following after solving the model:

Note that that LINGO nulled out all the extra records.