CHAPTER 13 SIMPROCESS Database

The Windows version of SIMPROCESS includes a Microsoft Access 2000 database (SimProcDB.mdb) designed to hold simulation results from simulation runs. This database includes predefined queries, graphs (forms), and reports, each of which can be modified to tailor the database for output analysis. The procedures for connecting the database to SIMPROCESS using ODBC are explained in the Getting Started Manual. (An Access 97 database is available upon request.)

SIMPROCESS can use other non-Access SQL databases by making appropriate entries in the sProcDB.properties file provided by the installer. This file is installed on both Windows and non-Windows systems and is pre configured for use with the Access database described above. Additional information is included in this chapter to aid in using a non-Access SQL database patterned after the Access database (SimProcDB.mdb) to hold simulation results.

Note that the discussion in this chapter is only concerned with storing simulation results in SimProcDB.mdb or in a user-created SQL database patterned after SimProcDB.mdb. There are expression statements that allow SIMPROCESS to read from or write to any SQL database. See "Interfacing With A Database" on page 296 for more information.

Committing Results to the Database

To commit simulation results to the database, select Commit to Database from the Report menu. The menu item will not be enabled until a simulation run is complete (either terminating normally or user terminated). If the database is not found, an error dialog will appear. If this occurs, recheck the database connection through the ODBC control panel if using the Access database, or recheck the values in the sProcDB.properties file. Normally, the Commit To Database dialog will appear. A design name and scenario name must be entered. The design and scenario comments are optional. What constitutes a design will be discussed in the next section.

Once the information has been entered, click on Commit. At this point SIMPROCESS verifies that the design name and scenario name are allowable. If so, the results are stored in the database. When finished, the status bar shows Commit Complete.

System, Design, and Scenario

Three tables in the database control the results placed there: System, Design, and Scenario. The System table is the highest level table and contains the model name. Therefore, the database is designed to hold results from more than one model. If a record is deleted from the System table, all records related to that model are deleted from the Access database. (In order for this to be true in another SQL database, the appropriate relationships and/or constraints must be established when the database is created.) The Design table holds data from different versions of the same model. A model design consists of the following:

If any of the above change, then a new design name is required. For instance, assume results have been committed to the database with a design name of Design 1. After that a Process alternative is added to the model and the model is run again. If the same design name as before is used, SIMPROCESS will prompt for a new design name because the number and names of Process alternatives have changed.

Note that there will be no prompt for a new design name if a different alternative is run that was already in the model. If another alternative is run, the design name or scenario name can be changed. When a design is deleted from the Design table, all records associated with that design are removed from the Access database. (In order for this to be true in another SQL database, appropriate relationships and/or constraints must be established when the database is created.) Therefore, to reuse a design name for the same model name (and there has been a design change), first delete the design.

A scenario is used to track various runs of the same system (model) and design. For example, one run might have a certain resource level at 3 while another run has the same resource level at 4. Differentiating those runs in the database is done through the scenario name. The scenario comment field is a good place to document the important settings for that scenario. Again, if a scenario is deleted, all records associated with that scenario will be deleted from the Access database. (As before, in order for this to be true in another SQL database, appropriate relationships and/or constraints must be established when the database is created.)

In the Access database, the Manage Results form should be used to delete results for a Model (System), Design, or Scenario. DO NOT modify the tables directly. Using the Manage Results form ensures the tables do not become corrupted. This form can be run from the Forms tab in Access. Also, when using Launch Database Application from the Report menu, the Manage Results form launches automatically. If the form is blank, then there are no run results in the database.

The Launch Database Application menu item is only enabled on Windows systems. It will be enabled even if the sProcDB.properties file is configured for a non-Access database.

IMPORTANT: It is good practice to regularly compact the Access database, particularly after deleting records. This can be done from the Tools menu of Access by choosing Database Utilities.

Database Table Relationships

Select Relationships... from the Tools menu in Access to view all the tables and the relationships between them in the Access database. This is helpful in understanding how the queries were constructed. Because other database tools may or may not support constraints and relationships in the same way as Access, it will be up to the user to create comparable mechanisms there if desired.

Database Queries

The predefined queries in the SIMPROCESS Access database are designed to offer the same information provided in the Standard Report. (See "Standard Report" on page 186.) The queries can be used as-is or copied and modified. The queries are not restricted by system, design, or scenario. So when the Entity Cycle Time query is run, all entity cycle times without restriction will be displayed. Copying a query and restricting its search is one example of tailoring a query for analysis.

Even though the whole criteria cannot be seen, note that the StatType field from the Statistics table is restricted by "Total Entities generated" or "Entities remaining in system" or "Total Entities disposed." These are the statistics types that make up Entity Count. Other fields could be restricted as well. For instance, the SystemName field from the System table could be restricted so only one model's results would be available. In the example below the query to the demonstration model CallCenter is restricted by placing "CallCenter" in the Criteria row of the query under SystemName.

The queries that generate results across replications were developed using the queries that give results by replication. So if the Entity Count query is restricted to CallCenter, then the Entity Count Across Replications query will also be restricted to CallCenter. Looking at the Design View for Entity Count Across Replications, the only data source for the query is the Entity Count query.

The average, standard deviation, minimum, and maximum were determined by selecting the appropriate statistic in the Total row for the field from the Entity Count query that was to be summarized across replications. This was accomplished by not including the Replication field from the Entity Count query.

Forms (Graphs) and Reports

The Forms tab contains the predefined graphs plus the Manage Results form. These graphs are based on the predefined queries. Therefore, the Entity Count Across Replications form (or graph) is based on the Entity Count Across Replications query. In order to restrict the graph to only certain records, go to the original query (in this case Entity Count since Entity Count Across Replications is based on it) and restrict it.

The Reports tab contains the predefined reports. Like the graphs, the reports are based on the predefined queries. Therefore, the Entity Count report is based on the Entity Count query. In order to restrict the report to only certain records, go to the original query (in this case Entity Count) and restrict it.

When using a non-Access SQL database, the predefined queries, graphs and reports described in this chapter may serve as a basis for developing SQL statements for retrieving and analyzing database contents. Those using SIMPROCESS on non-Windows systems where the SimProcDB.mdb file is not installed may obtain a copy for reference at the SIMPROCESS web site, or contact SIMPROCESS Technical Support for assistance.

Launch Database Application

This menu item on the Report menu (only enabled on Windows systems) brings up a dialog that selects the database and database application to use. SimProcDB.mdb and Microsoft Access are the defaults. (The remaining portion of these instructions assume MS Access is the database application.) If the database field is blank, then Access will start and prompt to open a database. If only a database is in the field (with no path), SIMPROCESS assumes the database is in the SPUser directory. If this is not the case, Access will start and prompt for a database. When the database desired is not in SPUser, enter the full path, and Access will open with that database.

If SimProcDB.mdb is entered in the database name field, the Manage Results form will launch automatically when the database opens. If other actions are desired, simply close the form and continue. Always use the Manage Results form to delete records from SimProcDB. If the tables are edited directly, they could become corrupted and run results placed in the database would be lost. The Manage Results form can be launched from the Forms tab. Copies of SimProcDB.mdb may be used for results. However, the Manage Results form will not launch automatically.

SIMPROCESS and Other Databases

Most of this chapter assumes the use of the Microsoft Access database with SIMPROCESS. Those not using Windows will be compelled to use another database, and Windows users may wish to have the flexibility to do so. This section will provide information which those users must know to employ another database for analyzing results.

To store results in any database, SIMPROCESS must have the file sProcDB.properties in the SPUser directory. The one provided by the installer is configured for use with the Access database. Also included there are two example files which can aid in using another SQL database:

The mysql.sProcDB.properties file is an example of what needs to be put into the sProcDB.properties file in order to use MySQL, a popular open source database, with SIMPROCESS. It contains two required properties and others that are commented.

· jdbc.drivers - This property must provide the fully qualified name of a Java class which is a JDBC driver compatible with the database to be used. The one in this example is from the MySQL Connector/J driver, available with MySQL at www.mysql.com. The driver must be in the SIMPROCESS classpath at runtime, so the JAR file containing it should be placed into the jre/lib/ext directory under the SIMPROCESS installation directory (see "Java Extension Mechanism," beginning on page 603 for more information).

· jdbc.url - This property identifies a specific database and is used to connect to it via the driver named above. Details on the possible forms and values of this property will be documented with available Java JDBC drivers. It is important to note, however, that SIMPROCESS will assume that any value beginning with "jdbc:odbc" is the Access database. The example in this file shows using a MySQL database named "simprocess" on the local system.

· jdbc.username - This property is disabled in the example. If the database configuration requires a user name, remove the "#" to enable the property and give it an appropriate value.

· jdbc.password - This property is disabled in the example. If the database configuration requires a user password, remove the "#" to enable the property and give it an appropriate value.

The simprocessdb.sql file is an example of Data Definition Language (DDL) for creating a database in MySQL that is suitable for use with SIMPROCESS. The file contains necessary DDL statements to drop and create a database named "simprocess" and then to create each of the tables needed for storing simulation results, some of which require insertion of static data. While the Access database contains some tables and/or columns with spaces in their names, this DDL uses underscore characters instead. When the value of the jdbc.url property does not begin with "jdbc:odbc", SIMPROCESS will use these names to allow support for the widest range of SQL databases. Other features, such as foreign keys, referential integrity, cascading deletes, etc., are not supported by all databases, a fact which should be considered in the management of any selected SQL database.

The DDL statements in this example name the database tables and columns using the same case that SIMPROCESS uses in its internal SQL statements, though not all databases will require or respect case. For instance, testing showed that MySQL server 3.23.52 insisted that table names in SQL statements match the case used in the DDL, while MySQL server 4.0.14-standard allowed the use of all lower case names irrespective of the DDL used.

SIMPROCESS users are encouraged to send information to the SIMPROCESS Technical Support team about their own experiences with other non-Access SQL databases. Reports of good (or bad) JDBC drivers for various databases are welcomed. Please send compressed example files similar to these to simprocess@caci.com. When sending such reports and/or examples, please be sure and provide as much detail as possible about any special circumstances, platforms involved, etc., and indicate whether it's all right to share any samples with others.