There was recently a discussion in our Yahoo Crystal Ball User Group (CBUG) on the ways in which one can enter constraints in OptQuest, specifically, when there are too many of them. I wanted to clarify some of the options that are available for doing just that in the current releases (CB 11.1.2.X).

As I indicated in the group post, there is no neat way to do that. But there are two ways one can achieve the same result, and these came out from the follow-up research I did. Let’s discuss the two options.

The constraints can be convoluted together to create a super-constraint, which is satisfied if all the constraints are satisfied. Dave Hammal, our resident Solution Specialist, talks about this approach while answering a similar question in our LinkedIn group. Basically, one would create a bunch of Excel formulas for each constraint which would report 1 if the constraint is satisfied, 0 otherwise. One can then create a constraint using one of the aggregate functions, e.g., minimum, to indicate the satisfaction of all the constraints. This final constraint can be added to OptQuest.

This is the best way to programmatically add numerous constraints to a model. OptQuest is also exposed as a COM DevKit, and can be used from VBA. You can get the details of the DevKit methods from the Windows help file “CB COM DevKit.chm”, that is available in your installation folder under Docs (something like: Program Files\Oracle\Crystal Ball\Docs). Check out the OptSetup.AddConstraint method, which takes a string equation as one would enter it in the constraint panel. Once the VBA code is executed, the constraints are available in the model, and can also be accessed through the GUI. Note: you do not need to update/install anything in order to access the Developer Kit, this component gets installed as you install any version of Crystal Ball.

This option generally works better than the first option above, and can sometime speed up the solver execution substantially.

It would also help if you remodel the problem to decrease the number of constraints, if possible. Case in point: if you are entering constraint at an individual (e.g., item) level, it might help to model a constraint at a group level. That would not only decrease the number of constraint, but also help in loosen tight constraints in a solution space, and facilitate finding a solution. Of course, this solution may not always be feasible (e.g., you might be already modeling at an aggregate level), but at a minimum, this is worth a thought.

A general note in this context: while solving simulation optimization problems, we have seen the performance of OptQuest solver engine deteriorates somewhat for more than 300 or so decision variables. We will be interested in hearing how many constraints you are using in your optimization problem, and the solver performance you are obtaining.

Finally, as mentioned in the forum post, we would like to provide a way to directly enter these constraints from GUI, like Excel Solver, in one of the future releases.

Update (5/16/2011): Added more notes on the usage.

Update (3/18/2013): Added more notes and a third option.

As I indicated in the group post, there is no neat way to do that. But there are two ways one can achieve the same result, and these came out from the follow-up research I did. Let’s discuss the two options.

__Option 1: Constraint convolution__The constraints can be convoluted together to create a super-constraint, which is satisfied if all the constraints are satisfied. Dave Hammal, our resident Solution Specialist, talks about this approach while answering a similar question in our LinkedIn group. Basically, one would create a bunch of Excel formulas for each constraint which would report 1 if the constraint is satisfied, 0 otherwise. One can then create a constraint using one of the aggregate functions, e.g., minimum, to indicate the satisfaction of all the constraints. This final constraint can be added to OptQuest.

Note that although this option works, OptQuest would still try out substantial number of infeasible solutions before closing in on valid solutions. This method actually distorts the solution space, as OptQuest does not get to use information from the individual constraints but would only use the information from the final constraint. This in turn can increase the time taken by OptQuest to solve a problem. Even then, I think this is an easy option to try out before trying the second option described below.

Option 2: Using OptQuest DevKitOption 2: Using OptQuest DevKit

This is the best way to programmatically add numerous constraints to a model. OptQuest is also exposed as a COM DevKit, and can be used from VBA. You can get the details of the DevKit methods from the Windows help file “CB COM DevKit.chm”, that is available in your installation folder under Docs (something like: Program Files\Oracle\Crystal Ball\Docs). Check out the OptSetup.AddConstraint method, which takes a string equation as one would enter it in the constraint panel. Once the VBA code is executed, the constraints are available in the model, and can also be accessed through the GUI. Note: you do not need to update/install anything in order to access the Developer Kit, this component gets installed as you install any version of Crystal Ball.

This option generally works better than the first option above, and can sometime speed up the solver execution substantially.

__Option 3: Remodeling__It would also help if you remodel the problem to decrease the number of constraints, if possible. Case in point: if you are entering constraint at an individual (e.g., item) level, it might help to model a constraint at a group level. That would not only decrease the number of constraint, but also help in loosen tight constraints in a solution space, and facilitate finding a solution. Of course, this solution may not always be feasible (e.g., you might be already modeling at an aggregate level), but at a minimum, this is worth a thought.

__General Note__A general note in this context: while solving simulation optimization problems, we have seen the performance of OptQuest solver engine deteriorates somewhat for more than 300 or so decision variables. We will be interested in hearing how many constraints you are using in your optimization problem, and the solver performance you are obtaining.

Finally, as mentioned in the forum post, we would like to provide a way to directly enter these constraints from GUI, like Excel Solver, in one of the future releases.

Update (5/16/2011): Added more notes on the usage.

Update (3/18/2013): Added more notes and a third option.