Microsoft Solver Foundation for Excel Guide
Microsoft Solver Foundation for Excel Guide
This documentation is provided to you for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.
Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright,
no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form
or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express
written permission of Microsoft Corporation.
Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering
subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the
furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual
property.
Microsoft and Excel are registered trademarks or trademarks of Microsoft Corporation in the United States and/or other
countries.
o Modeling Editor. Simplifies the modeling experience by using a collection of modeling panes
and includes a modeling syntax checker and other similar features exposed through the Solver
Foundation ribbon bar for Excel.
o Excel Cell binding. Binds static or dynamic data in a range to and from the model.
o Automatic Results and Report generation. Displays all results and reports on separate Excel
sheets for easy viewing, saving, or integration into other workflow systems.
o Import/Export capabilities. Supports MPS, QPS, SMPS, and OML formats. You can deploy a
model from Excel directly to C# - avoiding the need for the application developer to re-write any
model code.
o Improved Microsoft Office Integration. Deploy models from Excel to SharePoint using our
OMLX file (model + metadata). This provides a script-safe and secure method to transfer models
between client and a server.
This release of the Solver Foundation for Excel add-in provides several improvements:
Support for third-party solvers. Third-party solvers can be configured the same way as Solver
Foundation’s built-in solvers through the Directives tab.
Initial values can be optionally specified for decisions, improving performance for solvers with warm
start capabilities.
Sparse parameter support. Tables that are bound to parameters need not specify values for all
possible indexes. Unspecified entries are assumed to be zero.
A new options dialog makes it easier to control which output the add-in displays when solving. You
can configure the add-in to display progress information during a solve operation.
Inline set definition is now supported. The elements of a set can be specified directly in the Sets
dialog.
This document covers how you can model, solve, and generate reports by using the Solver Foundation
for Excel add-in. Please direct queries and feature suggestions online at [Link].
The best way to learn how to use the Solver Foundation for Excel add-in is to experiment with a sample.
In this tutorial we will work with the “Misra1a” sample, found in the Microsoft Solver
Foundation\Samples\Excel\NLP subdirectory in your Solver Foundation installation. Here’s what the
Misra1a spreadsheet looks like:
Models are entered in the right-hand pane. Models are built from input parameters, decisions, goals (in
the form of objective functions), constraints, and sets. Constraint and goal expressions are specified
using the Optimization Modeling Language (OML), a declarative modeling language. You can bind input
parameters and output decisions to cell ranges in a worksheet. The Solver Foundation ribbon provides
commands to validate, export, solve, and configure the models that you build.
The process for building and solving a model often varies, but the diagram below shows a typical
workflow. Model creation is primarily done in the modeling pane, and solving and reporting is done
using the ribbon.
Sets. These are collections of objects that are used as indexes in Parameters or Decisions. For
example, a matrix Parameter takes two Sets as indexes.
Parameters. These are the “inputs” of the solver – data is bound from an Excel sheet to the model.
Parameters can be indexed, or singled-valued constants of type Reals, Integers, or Booleans.
Decisions. These are the “outputs” or results of the model being solved. Data can be bound to a
specific Excel sheet. Supported types for Decisions can be Reals, Integers, or Booleans.
Goals. This is where you define the business goal or goals you are trying to accomplish. These are
used to specify a quantity or equation that should be maximized or minimized.
Constraints. This is where you can add constraints to the model. These are restrictions placed on
Decisions.
Directives. Ability to provide solver hints and other solver specific attributes.
Model. Shows the model in OML format. Advanced users can manually edit OML directly from this
pane.
Log. Solver output details. This is similar to the command line interface output.
Sets
A Set is an unordered list of items that define the valid set of indexes for a Parameter or Decision. In
Misra1a we have a set of data points that we want to fit. Click on the Sets tab to see the list of sets that
have been defined in the model:
The Set pane provides fields for the name, how set items are specified, and the domain for the set. The
items in a set are normally determined automatically based on the Parameters and Decisions in which
they participate. In Misra1a, the elements of the Point set are determined by the “x” and “y” Parameters
7 © 2006-2011 Microsoft Corporation
that we will review in a moment. Another way to specify items is to select the “Values” setting for the
Items dropdown. This allows you type in the permitted items as a list:
The Automatic option is usually the best choice because it is often inconvenient to explicitly type in the
values for a set.
The Domain for a set defines the data type for its items. For many models, this data type is unimportant
so the default value of “Any” is fine. If you use the indexes in a goal or constraint expression you may
wish to change the Domain to Real or Integer. The domain for set items will be checked for correctness
when you try to solve the model. If you select the “Values” option for the Items field, the domain is
assumed to be Real.
Parameters
The Parameters tab allows you to associate (or “bind”) data from an Excel sheet to the model. The
following parameter types are available:
Parameters can be single-valued or indexed using Sets. Both random and non-random parameters can
be indexed. You can use the Binding Editor to associate Sets with Parameters. Click on the Binding
button for a parameter to display the Binding Editor. The two parameters in the Misra1a model are the
“x” and “y” coordinates for each point. This data is stored in a table called “Data”:
A single row or column. Uncheck the “Range Includes Column Headers” and select the range
containing the data. In the first Set dropdown, choose the Set that represents the indexes for
the Parameter.
A matrix containing data values. Uncheck the “Range Includes Column Headers” and select the
range containing the data. In the first Set dropdown select the Set that represents the first
(row) index. In the second Set dropdown select the second (column) index. The Column Header
controls are disabled.
A table containing rows containing indexes and values. The table has headers that describe the
contents of each column. Check the “Range Includes Column Headers” checkbox. Typically one
or more columns will represent indexes and one column contains the values. For each column
that represents an index, select the appropriate Set and Column Header. In the Value Field
dropdown, select the dropdown that contains the parameter values.
In this case we are binding to a table, so we have checked the checkbox and specified one index set and
a value field.
- Decision. An output value that represents values that the solver should determine.
- Recourse Decision. An output value in a stochastic model that is made in response to the
realization of a random parameter.
Decisions, just like Parameters, can be indexed using Sets. The Misra1a model has two decisions: the
“beta” variables that determine the nonlinear data fit between the x and y parameters.
Click on the button next to the Binding field to associate the Decision with spreadsheet data. The
Misra1a spreadsheet associates the decisions with cells in columns G and H in the spreadsheet:
Notice that in this case the output is a single cell, so the “Range Includes Column Headers” checkbox has
been cleared.
The Initial Value range selector at the bottom of the dialog allows you to specify an initial guess for the
value of the decision. In this case, the initial value for the “b1” decision was specified in cell H7. Initial
values are optional.
Goals
A goal combines Parameters and Decisions in order to express how a model is to be optimized. Typically,
goals represent business objectives or preferences such as “maximize profit”, or “minimize waiting
time.” In Misra1a, the goal is to minimize the sum of squares deviation between the predicted fit:
The constraint above is indexed over the Routes set using the Foreach expression. The constraint says
that for each route, the demand is the sum of the products of the passenger capacities and airplane
fleet allocation, minus the number of passengers bumped from the route
In Solver Foundation 3.1, the Directives tab has been changed so that you can change any directive
property. A complete list of Directive properties can be found by referencing the MSDN documentation
for Solver Foundation.
Solver Foundation 3.1 also provides support for third-party plug-in solvers in the Excel add-in. In order to
register a third-party solver for use with the Excel add-in,c reate a
[Link] file in the %ProgramFiles%\Microsoft Solver
Foundation\<verson>\MsfForExcel folder of the installation to register a particular solver for a specific
model type. A sample configuration file is given below. Be sure to change the Version number to match
<?xml version="1.0"?>
<configuration>
<configSections>
<section name="MsfConfig"
type="[Link], [Link]
dation, Version=[Link], Culture=neutral, PublicKeyToken=AFB4C8CE5FE12974"
allowLocation="true" allowDefinition="Everywhere" allowExeDefinition="MachineToAp
plication"
restartOnExternalChanges="true" requirePermission="true" />
</configSections>
<MsfConfig>
<MsfPluginSolvers>
<MsfPluginSolver name="solver3"
capability="LP"
assembly="[Link]"
solverclass="[Link]"
directiveclass="[Link]"
parameterclass="[Link]" />
<MsfPluginSolver name="solver4"
capability="MILP"
assembly="[Link]"
solverclass="[Link]"
directiveclass="[Link]"
parameterclass="[Link]" />
<MsfPluginSolver name="solver1" capability="LP" assembly="[Link]" solverclass
="[Link]"
directiveclass="[Link]" param
eterclass="[Link]"/>
<MsfPluginSolver name="solver2" capability="MILP" assembly="[Link]" solvercla
ss="[Link]"
directiveclass="[Link]" param
eterclass="[Link]"/>
</MsfPluginSolvers>
</MsfConfig>
</configuration>
If the solver was not registered correctly, an error dialog will be displayed when you launch Excel.
Otherwise, registered solvers will be displayed in the Directives dropdown. You can set their properties
the same way as with built-in solvers.
Model. Basic functionality for displaying, importing, exporting, and deploying models.
o Model. Hides or shows the modeling pane window.
o Import. Supports importing OML, MPS and QPS files. The add-in supports file sizes up to 10
MB. Larger files can be processed by the command-line tool MSFCLI, or by writing .NET
code to call Solver Foundation Services.
o Export. Save as MPS, QPS (when appropriate) or OML.
o Deploy. A model can be deployed to SharePoint (via OMLX format) or Visual Studio (via C#).
o Check. Performs syntax checking on the model. Results of this check will be displayed below
the model in the Model Validation text box within the modeling pane.
Solve. The main runtime to invoke a solution.
o Solve. Runs the appropriate solver, and generates results in the ‘Solver Foundation Results’
sheet.
o (Solve) Next. Iterate through Constraint Programming problems that have multiple
solutions. Multiple solutions will be shown sequentially in the ‘Solver Foundation Results’
sheet.
o Stop. Interrupts the Check, Solve or Summary functions mid-stream.
Summary. Generates a report overview plus any solver execution details.
Settings. These options enable the user to fine tune what details are generated.
o Clear Log On Solve. The Log tab will clear after each Solve.
o Decisions. Display detailed Decision information when available.
o Directives. Display Directive related information.
o Infeasibility. Include solver specific sensitivity information.
o Sensitivity. Include solver-specific feasibility information when available.
o Solver Details. Include details such as solve time, algorithms used, etc.
Click on the icon in the lower-right hand corner to bring up additional settings:
A valid model can be solved at any time by simply pressing the Solve button. Solver Foundation
automatically generates results, and if you specify an output binding, any Excel valid action (graphing,
charting, etc.) can be used. The output is displayed in the “Solver Foundation Results” worksheet. You
can also view the Log tab to see additional details:
Sample Projects
The Solver Foundation installation includes several samples to help you get started. These samples
demonstrate a variety of solver and application domain types:
These samples span classical problem domains in mathematical programming and operations research:
linear programming (LP), quadratic programming (QP), constraint programming (CP), nonlinear
programming (NLP) and stochastic programming (SP).
If you use the Automatic Editing Mode in the Modeling Pane, the comments may be removed from the
OML code. To work around this issue, use Annotation to add metadata to a parameter, decision,
constraint, or goal.
Please refer to the Solver Foundation OML Syntax Document for more information.
<Model xmlns:xsi="[Link]
xmlns:xsd="[Link]
...
</Model>
Name Element
Name element contains the name of the model.
ModelText Element
ModelText element contains the entire OML model text.
<ModelText>Model[
Parameters[
Sets, Products, Factories, Areas, Promotions
],
...
]</ModelText>
DataBindings Element
DataBindings element contains information for binding to different type of data sources. DataBindings
element can contains 0 to n BindingSourceInfo element. Each BindingSourceInfo element contains
information for setting up input and output data binding when solving a model.
<DataBindings>
<BindingSourceInfo>
<Name>XML</Name>
...
</BindingSourceInfo>
<BindingSourceInfo>
<Name>SQL</Name>
...
BindingSourceInfo Element
In BindingSourceInfo element there are two metadata elements:
Name element contains the name of the binding source. The name should be unique in
DataBindings collection.
Connection element contains the connection information for the data source. For SQL Server it
will be connection string; For XML file it will be the filename; For SharePointSite it will be the Url
to the site collections.
<BindingSourceInfo>
<Name>SQL</Name>
<Connection>Data Source=(local);Initial
Catalog=SupplyChainPlanning;Integrated Security=SSPI;</Connection>
<ParameterBindings>
<InputBindingInfo>
<Name>manufactureLoads</Name>
<TableName>manufactureLoads</TableName>
<ValueField>ManufactureLoads</ValueField>
<IndexFields>Product</IndexFields>
</InputBindingInfo>
...
</ParameterBindings>
<DecisionBindings>
<OutputBindingInfo>
<Name>Plan</Name>
<TableName>Plan</TableName>
<ValueField>value</ValueField>
<IndexFields>Products,Areas,Promotions,Factories</IndexFields>
</OutputBindingInfo>
InputBindingInfo Element
InputBidingInfo contains binding information to a parameter:
<InputBindingInfo>
<Name>manufactureLoads</Name>
<TableName>manufactureLoads</TableName>
<ValueField>ManufactureLoads</ValueField>
<IndexFields>Product</IndexFields>
</InputBindingInfo>
<InputBindingInfo>
<Name>gasDemand</Name>
<TableName>gasDemand</TableName>
<LowerBoundField>LowerBound</LowerBoundField>
<UpperBoundField>UpperBound</UpperBoundField>
<IndexFields>country</IndexFields>
</InputBindingInfo>
<InputBindingInfo>
<Name>gasDemand</Name>
<TableName>gasDemand</TableName>
<MeanField>LowerBound</MeanField>
<VarianceField>UpperBound</VarianceField>
<IndexFields>country</IndexFields>
</InputBindingInfo>
OutputBindingInfo Element
OutputBindingInfo contains information for single decision binding:
<OutputBindingInfo>
<Name>TotalCost</Name>
<TableName>TotalCost</TableName>
<ValueField>Cost</ValueField>
<IndexFields>Products,Areas</IndexFields>
</OutputBindingInfo>
Directives Element
Directives element contains all directives will be used to solve the model. Directives element contains 0
to n directives. Each directive represent an instance can be created by SFS. There should be only one
instance per type can exist in directives collection.
<Directives>
<DirectiveInfo>
<TypeName>SimplexDirective</TypeName>
<Enabled>true</Enabled>
<Properties>
<DirectivePropertyInfo>
<Name>Algorithm</Name>
<Value xsi:type="xsd:int">2</Value>
</DirectivePropertyInfo>
DirectiveInfo Element
DirectiveInfo element contains all information for a SFS directive object. DirectiveInfo element contains
following elements:
DirectivePropertyInfo Element
DirectivePropertyInfo contains name value pair for each directive object property. DirectivePropertyInfo
element contains following elements: