Topic
  • 9 replies
  • Latest Post - ‏2013-07-11T10:03:27Z by gargius
gargius
gargius
22 Posts

Pinned topic Creation of independent subproblems

‏2013-06-10T12:20:29Z |

Hello,

 

I have to deal with following challenge:

The data for my problem are all in the same tables. Now I want to create hundreds of independent subproblems out of it. One subproblem is clealy defined by the column subproblem in every table. How can I make CPLEX to solve hundreds of these subproblems sequentially and independently?

I tried it with a main statement at the end of the .mod file. But it does not work because a defined variable "SubproblemNumber" is not available in the model formulation.

I tried it also by creating own tables for every subproblem during the data import process from SQL in the .dat file. But the problem is that it is not possible to define a variable in the SQL statement, only a fixed value.

I attached an executable .mod and .dat file. Has anybody an idea to solve it?

 

Thanks!

Attachments

  • AlexFleischer
    AlexFleischer
    1270 Posts

    Re: Creation of independent subproblems

    ‏2013-06-11T12:20:50Z  

    Hi

     

    let me give you an example:

    Suppose you have a small model sub.mod

     

    float maxOfx = ...;
    dvar float x;

    maximize x;
    subject to {
      x<=maxOfx;
    }

     

     

    then you can call it many times with different data with

    main {
      var source = new IloOplModelSource("sub.mod");
      var cplex = new IloCplex();
      var def = new IloOplModelDefinition(source);
      var opl = new IloOplModel(def,cplex);
     
     
      for(var k=11;k<=20;k++)
      {
      var opl = new IloOplModel(def,cplex);
        
      var data2= new IloOplDataElements();
      data2.maxOfx=k;
      opl.addDataSource(data2);
      opl.generate();

      if (cplex.solve()) {
         writeln("OBJ = " + cplex.getObjValue());
      } else {
         writeln("No solution");
      }
     opl.end();
     
     
    }  
     
    }

    and the output is

     

    OBJ = 11
    OBJ = 12
    OBJ = 13
    OBJ = 14
    OBJ = 15
    OBJ = 16
    OBJ = 17
    OBJ = 18
    OBJ = 19
    OBJ = 20

     

    regards

     

     

     

  • StephenHall
    StephenHall
    23 Posts

    Re: Creation of independent subproblems

    ‏2013-06-11T14:13:24Z  

    Hi,

    very nice example Alex.

    I had some similar problem and I found  a similar solution that allow you to still use run configuration:

    • use setting with Run config
    • not hard code the file name of the model
    • use the data visualization, profiler of the IDE
    float maxOfx = ...; //it is a external variable
    
    dvar float x;
    
    maximize x;
    subject to {
      x<=maxOfx;
    }
    
    main {
    
      var def = thisOplModel.modelDefinition;
      var data = thisOplModel.dataElements;
     
      for(var k=11;k<=20;k++)
      {
          var opl = new IloOplModel(def,cplex);
            
          data.maxOfx=k;
          opl.addDataSource(data);
          opl.generate();
        
          if (cplex.solve()) {
             writeln("OBJ = " + cplex.getObjValue());
          } else {
             writeln("No solution");
              }
        opl.end();
      }   // end for with k
    } //end main
    

     

    It gives the same result.

    But be aware of some restriction:

    1. that it works only if the external data are set of tuples or array or primitive data. You can't have a tuple composed of tuple, it should be only  string, int or float. 
    2. you can only modify external data. It means that if you want play with some data you need to declare them as  int mydata= ...;

    I hope that will help

  • gargius
    gargius
    22 Posts

    Re: Creation of independent subproblems

    ‏2013-06-14T18:09:13Z  

    Hi,

    very nice example Alex.

    I had some similar problem and I found  a similar solution that allow you to still use run configuration:

    • use setting with Run config
    • not hard code the file name of the model
    • use the data visualization, profiler of the IDE
    <pre dir="ltr">float maxOfx = ...; //it is a external variable dvar float x; maximize x; subject to { x<=maxOfx; } main { var def = thisOplModel.modelDefinition; var data = thisOplModel.dataElements; for(var k=11;k<=20;k++) { var opl = new IloOplModel(def,cplex); data.maxOfx=k; opl.addDataSource(data); opl.generate(); if (cplex.solve()) { writeln("OBJ = " + cplex.getObjValue()); } else { writeln("No solution"); } opl.end(); } // end for with k } //end main </pre>

     

    It gives the same result.

    But be aware of some restriction:

    1. that it works only if the external data are set of tuples or array or primitive data. You can't have a tuple composed of tuple, it should be only  string, int or float. 
    2. you can only modify external data. It means that if you want play with some data you need to declare them as  int mydata= ...;

    I hope that will help

    Thanks for your examples. It is working. But there is still a problem:

    All data for all subproblems are in the same tables. And if CPLEX import the data from the SQL server for all problems at the same time the running time gets very high although the amount of data is not very big (strictly speaking CPLEX stops). Now I want to import the rows for every subproblem each but the problem is I cannot use the variable "k" or in my case "actualSubproblem" in the SQL statements in the .dat file.

    Has anybody an idea?

     

    Thanks and regards

  • StephenHall
    StephenHall
    23 Posts

    Re: Creation of independent subproblems

    ‏2013-06-17T08:34:53Z  
    • gargius
    • ‏2013-06-14T18:09:13Z

    Thanks for your examples. It is working. But there is still a problem:

    All data for all subproblems are in the same tables. And if CPLEX import the data from the SQL server for all problems at the same time the running time gets very high although the amount of data is not very big (strictly speaking CPLEX stops). Now I want to import the rows for every subproblem each but the problem is I cannot use the variable "k" or in my case "actualSubproblem" in the SQL statements in the .dat file.

    Has anybody an idea?

     

    Thanks and regards

    I would advice you to import all the data in one problem as you have all the data in one object:

    var data = thisOplModel.dataElements;
    

    from this object you can feed other models. you can find such example in the doc : http://pic.dhe.ibm.com/infocenter/cosinfoc/v12r5/topic/ilog.odms.ide.help/OPL_Studio/opllanguser/topics/opl_languser_script_fc_column.html

     

    The other idea would be to do a SQL call through JavaCall in "main" OPLscript. I have never tried it and it seems a lot of work. In addition I afraid samml SQL query will be slower then a big one.

    <Script object or Java object>=IloOplCallJava(<class name> or <Java object>,<method name>,[<method signature> or ""],[<parameters>,...]);

    http://pic.dhe.ibm.com/infocenter/odmeinfo/v3r4/topic/ilog.odms.ide.odme.help/Content/Optimization/Documentation/ODME/_pubskel/ODME_pubskels/startall_ODME34_Eclipse1160.html

    good luck and let me know how it goes

  • StephenHall
    StephenHall
    23 Posts

    Re: Creation of independent subproblems

    ‏2013-06-17T09:20:14Z  
    • gargius
    • ‏2013-06-14T18:09:13Z

    Thanks for your examples. It is working. But there is still a problem:

    All data for all subproblems are in the same tables. And if CPLEX import the data from the SQL server for all problems at the same time the running time gets very high although the amount of data is not very big (strictly speaking CPLEX stops). Now I want to import the rows for every subproblem each but the problem is I cannot use the variable "k" or in my case "actualSubproblem" in the SQL statements in the .dat file.

    Has anybody an idea?

     

    Thanks and regards

    Yo can do conditional SQL query in the .DAT or .MOD

     

    http://pic.dhe.ibm.com/infocenter/odmeinfo/v3r4/topic/ilog.odms.ide.odme.help/Content/Optimization/Documentation/ODME/_pubskel/ODME_pubskels/startall_ODME34_Eclipse1214.html

  • gargius
    gargius
    22 Posts

    Re: Creation of independent subproblems

    ‏2013-06-19T13:50:07Z  

    Thank you, it works very well!

    But now I cannot export the result data into SQL (without the main function it has worked before). I have defined some tupels and sets after the model formulation in the .mod file for the result data (they are displayed as result data in the Interactive Optimizer). Then I have a DBUpdate statement in the .dat file. But the tables are empty after solving all the subproblems.

     Thank you and best regards

     

  • StephenHall
    StephenHall
    23 Posts

    Re: Creation of independent subproblems

    ‏2013-06-19T14:13:53Z  
    • gargius
    • ‏2013-06-19T13:50:07Z

    Thank you, it works very well!

    But now I cannot export the result data into SQL (without the main function it has worked before). I have defined some tupels and sets after the model formulation in the .mod file for the result data (they are displayed as result data in the Interactive Optimizer). Then I have a DBUpdate statement in the .dat file. But the tables are empty after solving all the subproblems.

     Thank you and best regards

     

    Hi,

    the reason is that in the main block the dataElement doesn't take publishers.

    Look at the extract of the user maneul below: Chapter 3. page 101 of IBM ILOG CPLEX Optimization Studio, OPL Language User's Manual, Version12Release4

     

    Data elements and data publishers
    When calling the method dataElements on an IloOplModel instance, you obtain a
    container of all the data elements of this model. This container doesnotinclude
    the data source publishers: if the IloOplModel instance has been created by means
    of a data source that contained publishers, these publishers will not be included in
    the data elements structure. For example, if you have created an IloOplModel
    instance with a .datfile containing a publisher like:
    result to DBUpdate (db,"INSERT INTO writeOPL(id) VALUES (?)");
    and you want to use the same publisher on another IloOplModel instance, you
    must use a specific.datfile containing only the publishers and add it to the new
    IloOplModel, as shown in Reusing data source publishers.
    Reusing data source publishers
    main {
    var Source = new IloOplModelSource("writeDB.mod");
    var Def = new IloOplModelDefinition(Source);
    var Cplex = new IloCplex();
    var Data = new IloOplDataSource("writeDBfromScript.dat");
    var Opl = new IloOplModel(Def,Cplex);
    Opl.addDataSource(Data);
    Cplex.solve();
    var DataElts = Opl.dataElements;
    var Opl2 = new IloOplModel(Def,Cplex);
    Opl2.addDataSource(DataElts);
    var Data2 = new IloOplDataSource("publisherData.dat");
    Opl2.addDataSource(Data2);
    DataElts.lb = 5;
    Opl2.generate();
    if (Cplex.solve()) {
    Opl2.postProcess();
    }
    else
    writeln("no solution");
    }
    writeFromScript.dat :
    lb=2;

    DBConnection db("access","testDB.mdb");
    result to DBUpdate (db,"INSERT INTO writeOPL(id) VALUES (?)");
    publisherData.dat would be:
    DBConnection db("access","testDB.mdb");
    result to DBUpdate (db,"INSERT INTO writeOPL(id) VALUES (?)");

  • gargius
    gargius
    22 Posts

    Re: Creation of independent subproblems

    ‏2013-06-20T14:55:55Z  

    Hi,

    the reason is that in the main block the dataElement doesn't take publishers.

    Look at the extract of the user maneul below: Chapter 3. page 101 of IBM ILOG CPLEX Optimization Studio, OPL Language User's Manual, Version12Release4

     

    Data elements and data publishers
    When calling the method dataElements on an IloOplModel instance, you obtain a
    container of all the data elements of this model. This container doesnotinclude
    the data source publishers: if the IloOplModel instance has been created by means
    of a data source that contained publishers, these publishers will not be included in
    the data elements structure. For example, if you have created an IloOplModel
    instance with a .datfile containing a publisher like:
    result to DBUpdate (db,"INSERT INTO writeOPL(id) VALUES (?)");
    and you want to use the same publisher on another IloOplModel instance, you
    must use a specific.datfile containing only the publishers and add it to the new
    IloOplModel, as shown in Reusing data source publishers.
    Reusing data source publishers
    main {
    var Source = new IloOplModelSource("writeDB.mod");
    var Def = new IloOplModelDefinition(Source);
    var Cplex = new IloCplex();
    var Data = new IloOplDataSource("writeDBfromScript.dat");
    var Opl = new IloOplModel(Def,Cplex);
    Opl.addDataSource(Data);
    Cplex.solve();
    var DataElts = Opl.dataElements;
    var Opl2 = new IloOplModel(Def,Cplex);
    Opl2.addDataSource(DataElts);
    var Data2 = new IloOplDataSource("publisherData.dat");
    Opl2.addDataSource(Data2);
    DataElts.lb = 5;
    Opl2.generate();
    if (Cplex.solve()) {
    Opl2.postProcess();
    }
    else
    writeln("no solution");
    }
    writeFromScript.dat :
    lb=2;

    DBConnection db("access","testDB.mdb");
    result to DBUpdate (db,"INSERT INTO writeOPL(id) VALUES (?)");
    publisherData.dat would be:
    DBConnection db("access","testDB.mdb");
    result to DBUpdate (db,"INSERT INTO writeOPL(id) VALUES (?)");

    I can advice you to have your model in to separate file:

    • -  "externalDataStructure.mod"
    • - "mathModel.mod"

    In the "mathModel.mod" you just add this line "include "externalDataStructure.mod";"

    Then you can spilt your SQL call into 2 .dat files:

    • "importFromDB.dat"
    • "exportFromDB.dat"

    This way you can built a model to use the publisher in "exportToDB.dat" with a model with just the data structure.

    remember to never generate thisOplModel  ( thisOplModel.generate()) because it will thisOplModel object and the dataElements will become immutable. That is why you should just reuse the "IloOplModelDefinition" and "IloOplDataSource"  from it

     

    // export the data to database
    var writeDBsrc = new IloOplModelSource( "externalDataStructure.mod");
    var writeDBdef = new IloOplModelDefinition(writeDBsrc);
    var writeDBModel = new IloOplModel(writeDBdef,cplex);
    var DataElets = thisOplModel.dataElements;  // this assuming the data are in thisOplModel
    writeDBModel.addDataSource(DataElets);


    var writeDBData = new IloOplDataSource("exportFromDB.dat");
    writeDBModel.addDataSource(writeDBData);
    writeDBModel.generate();

    if (cplex.solve()) {
        writeln("WriteDB model solve and postprocess in action");
        writeDBModel.postProcess();
    }  else  writeln("no solution");

     

     

     

    I tried this but I am not sure what should be included in "externalDataStructure.mod" and  "mathModel.mod"?

    In "mathModel.mod" just all the code until the definition of the tuples and the calculation for result data? And should I copy this part to "externalDataStructure.mod"? But then it is not a complete .mod file. For the two .dat files it is clear for me: All SELECT statements are in "importFromDB.dat" and all INSERT INTO statements are in "exporttFromDB.dat".

    Actually I have following main function in "mathModel.mod" for solving several subproblems sequentially and I am not sure how I can integrate the export function: (I think I should have only one solve command in the loop!!?)

    main {

      var Def = thisOplModel.modelDefinition;
      var Data = thisOplModel.dataElements;

      for(var z=1;z<=1;z++)
      {
        for (var k=1;k<=2;k++){
          var Opl = new IloOplModel(Def,cplex);
            
          Data.actualVehicle=k;
          Data.actualTestInstance=z;
          Opl.addDataSource(Data);    
          Opl.generate();
        
          if (cplex.solve()) {
             writeln("OBJ = " + cplex.getObjValue());
                  } else {
             writeln("No solution");
                 }

     

    Thanks very much and regards

     

  • gargius
    gargius
    22 Posts

    Re: Creation of independent subproblems

    ‏2013-07-11T10:03:27Z  

    This is my final solution which works well. In the SolutionToSQL.dat I have commands like:

    SolutionP1 to DBUpdate(db,"INSERT INTO SolutionP1(value1,value2,value3,value4,value5,value6) VALUES (?,?,?,?,?,?)");

     

    main {

      var Def = thisOplModel.modelDefinition;
      var Data = thisOplModel.dataElements;
     
      for(var z=1;z<=1;z++)
      {
        for (var k=1;k<=5;k++){
              
          Data.actualVehicle=k;
          Data.actualTestInstance=z;

          var Opl = new IloOplModel(Def,cplex);
          Opl.addDataSource(Data);
          var Data2 = new IloOplDataSource("SolutionToSQL.dat");
          
          Opl.addDataSource(Data2);          
          Opl.generate();
            
          if (cplex.solve()) {
             writeln(" --> objective value = " + cplex.getObjValue());
             
             Opl.postProcess();
     
          } else {
             writeln(" --> No solution");
                 }   
                  
        Opl.end();
      }  
    }}