Leveraging Excel for Parameter Management and Batch Execution

By paul ~ October 4th, 2006. Filed under: Best Practices, Tips & Tricks.

Microsoft Excel can be a powerful allly when attempting to tame large Foresight modeling projects.  We have benefited greatly from its capabilities as applied to

  • Parameter management
  • Batch execution
  • Data analysis

This article will deal with the first two, parameter management and batch execution.  In a future article, we’ll describe some powerful ways to use Excel and Access for data analysis.

Accompanying this article is an example model that you’ll want to download and look at.  You can download it by clicking here.

The example model is just a simple packet handling model that was thrown together to do some what-if analysis given inter-component communication costs and resource bandwidths.  Inside the model folder is an Excel workbook called Parameters.xls which is the focus of this article.

Parameter Management

Foresight provides the capability to read model parameters from external text files.  This is a powerful capability as it makes it possible to externally vary key model parameters from simulation execution to simulation execution.  Wise modelers will use this capability extensively to make their models as flexible as possible. Large models may have thousands of parameters that must be managed.  Managing them directly in text files can be cumbersome, and this becomes even more frustrating when the user desires to setup and simulate many different configurations.  With the aid of some simple Visual Basic macros, Excel can be used to manage parameters with ease.

In the Excel workbook, a sheet is devoted to each major division of parameters.  What I usually do is to try and give a sheet to each reusable component type, and that is what is done in the example.  There is a sheet each for the Component reusable, the TrafficGen reusable, and the Process Resource library elements.  Sometimes it is more convenient to devote a sheet to a group of reusable components.  Once you understand how it works you can adapt the methodology to fit your needs.  The rows in the sheet are the parameters for the component(s), with a column for each instance of the component.  Cell A1 of the sheet must contain the word “Parameters” to indicate to the macro that this is a parameter worksheet that should be written out.  The first row in each column contains the name of the instance.  These names must be unique within each sheet.

Parameters in Excel

When the workbook is saved, the WriteModelParameters macro is executed.  This macro loops through the sheets, and for each sheet that has the word “Parameters” in cell A1, creates a file named after the name of the sheet with .fsp appended.  (For example, Components.fsp.)  The parameters are written to this file in the form:

InstanceName_ParameterName: value

For example:

C1_rsrcName: “Proc1”
C1_qDepth: 1
C1_inConst: 100

These are referenced in the Foresight model in the parameters block for the reusable or library instances as shown below:

File parameters in Foresight

The format is:

ParameterName:= @FileName:ParameterName;

For Example, for the C1 instance:

qDepth:= @Components.fsp:C1_qDepth;
rsrcName:= @Components.fsp:C1_rsrcName;

Note that these files are written to the folder that contains the workbook, which is assumed to be the model folder.

When the simulator is turned on or reset, the referenced parameter files are read and the model is configured accordingly.  If there are problems obtaining the parameter values from the file(s), the user is alerted to the problem.

The formatting of the sheets, beyond the location of the data, is completely ignored by the macro.  The only requirement is that the parameter rows and columns must be contiguous as the macro stops at the first blank row or column.  Formatting, color, and comment documentation information added to the sheets can make the model much easier to use and understand.  The values for any parameter can be specified by any legal formula and may depend on the values of other parameters on other sheets.  This is a very powerful capability that can make configuration setup much easier.  You may even want to provide Excel forms for model users to fill in to configure a simulation.

foresight.ini setup

The foresight.ini file contains many parameters that affect the execution of Foresight.  These can be configured in the ForesightSetup sheet.  In this example, only the most frequently modified parameters are included.  As part of writing the parameters as described above, the foresight.ini file is written from the contents of the ForesightSetup sheet to the model folder.  Note that the WriteForesightINI macro may have to be modified to provide special handling for some of the foresight.ini parameters.  Please let us know if you need help with this.  Note that the external function call library (specified by the udfSharedLibraryPath parameter) is not actually needed by this model.  It was included simply to demonstrate how to correctly set up the External Call library and to test that the macros worked.

Batch Execution

Few models can provide the most benefit if they are only executed interactively using the Foreisght GUI.  The greatest benefit comes when many configurations can be executed automatically and the results analyzed and compared.  In this manner, the design space can be explored, sensitivity analyses performed, and the corners of the sytem’s behavior explored.  Again, a few simple macros in Excel can make the chore of setting up and running experiments much easier.

Batch execution setup in Excel

In the example provided, the BatchSetup sheet is used to set up and execute simulations.  Before I explain how this works and how to use it, get a feel for what it does by simply selecting columns D and E and pressing the “Execute Selected” button in cell A1.  Note that you must have Foresight v5.3.1 or v5.3.3 installed and the model must be on the same logical drive on which Foresight is installed. When execution has finished, you will find two new folders in the model folder, named cfg_1000_1000_1_mbps and cfg_2000_2000_1_mbps.  These folders contain the parameters and simulation results for these two configurations which were executed automatically.

Using this mechanism in your model is straightforward.  First, it provides the most benefit if your parameters are also maintained in the Excel workbook.  Configurations to be simulated are described in columns.  Parameters to be modified from simulation to simulation are described in rows.  The first rows (3 – 16) are dedicated to parameters used by the macro to configure the simulation.  These include the name of the folder in which to put the results, the “run until” time, and the recorder file names.  If a recorder file is not desired, simply leave that cell blank.  Rows 12-14 are outputs that simply record the start, stop, and elapsed time for the simulation.  Rows 18 and on are model parameters that you wish to vary from simulation to simulation.

To set up a series of experiments, first you must select the set of parameters that you wish to vary.  List these in row 18 and below.  In the worksheets that use those parameters, put a reference to that row in column B.  In the example, we want to vary the processor bandwidth of Proc1, so that appears in A18.  In the ProcSetup sheet, in cell B2, which provides the value for the Rate parameter to Proc 1, we put a reference to =BatchSetup!$B$18.

Once this has been done for all of the parameters that will be varied, we can create the configuration specifications for the simulations themselves.  We do this in columns, starting in Column C (do not use Column B).  In the Folder Name row, (cell D4 in our example), insert a name for the folder to be created.  This name must be unique, and life is easiest if a formula is used to construct the name from the cells below, as is done in the example.  Set the Run Until Time and provide names for any desired user input or log files.  Finally, provide the values for the parameters to be varied.  Note that the User Input File Name specifies a user input file for input to the simulation, not a recorder file for output.

When the “Execute Selected” button is pressed, the values in each selected column are copied to column B, in turn.  Then, a folder is created with the given name and the current versions of all parameters are written to the folder, a modified version of foresight.ini is written based on the ForesightSetup sheet, the working directory is changed to the newly created folder, and Foresight is executed in batch mode with the parameters provided.  When execution is finished, the original foresight.ini is written and the working directory is changed back to the model folder.  During simulation, any output or recorder files will be written to the folder.

Note that for this to work correctly, files written using the MiniSpec file I/O features should be written to the current working directory or to a file who’s path is specified by parameter and can be modified in the BatchSetup sheet to target the desired output folder.  The easiest thing to do is just to write all output files to the current working directory.

If you have other kinds of input files that your model relies on, such as stimulus files, you will need to enhance the macros to copy these into place OR have their paths be set by parameters that can be modified in the BatchSetup sheet.

Other Things

Note that there appears to be a problem with this macro that occurs when the Foresight installation folder and the model are on different logical drives.  I believe this is arising from the fact that the “cd” command on Windows will change folder path, but not the current drive (who’s stupid idea was that?)  So, the macro may need to be fixed to detect the need to change the target drive and do that at the same time as the ChDir(), but until then, it works if the model is on the same logical drive as the Foresight installation.

Another “nice” enhancement would be to add a button that will terminate the batch run and all subsequent runs.  I’ve tried many different mechanisms to accomplish this with no success.  If you figure it out, please share it!  Hitting the close button the Foresight command prompt window that is brought up with each execution is a quick and safe way to kill the Foresight execution.

We aren’t expert Visual Basic programmers here at Foresight, so if you fix or enhance this, please share it with the rest of us.  Your comments and recommendations are welcome.  The model and Visual Basic macros are provided as examples only.

1 Response to Leveraging Excel for Parameter Management and Batch Execution

  1. System Modeling Perspectives » Blog Archive » A Few “Best Practices”

    […] Click here for the post describing in detail how to accomplish this. […]