In this article, you will learn how to export data from individual replications to an external database. This is an advanced tutorial, and assumes some exposure to databases and SQL. It also assumes that you are familiar with the Experimenter and/or Optimizer feature in FlexSim. This tutorial uses PostgreSQL, but should be compatible with the most popular SQL database engines.
Model Description
Let's assume you have a model with at least one Statistics Collector, as well as an experiment ready to go. The model used in this example is fairly simple:
As the model runs, a Statistics Collector fills in a table. The table makes a new row for every item that goes into the Sink, recording the time, and the Type label of the item:
For the experiment, we don't have any variables, but we do have one Performance Measure: Input of the Sink. Of course, an actual model would have many Variables, Scenarios, and Performance Measures, but this model leaves out those details.
For reasons which come up later, this model also has two global variables, called g_scenario and g_replication. We will use these during the experiment phase.
Connecting to the Database
To connect to a database, you can use the Database Connector tool. Each Database Connector handles the connection to a single database. If you need to connect to more than one database, you will need more than one Database Connector.
For this model, I have added a Database Connector, and configured the connection tab as follows:
This configuration allows me to connect to a PostgreSQL database called "flexsim_test" running on my computer at port 9001. You can test the connection by clicking the "Test Connection" button. The test attempts to connect, as well as query the list of tables found in the database.
Setting Up the Export
Next, take a look at the Export tab:
This tab specifies that we are exporting data from StatisticsCollector1 to the table called experiment_results in the database (this table should exist before you set up the export). The Append to Table box means that when the export occurs, the data will be added to the table. Otherwise, the data would be cleared from the target table.
The other interesting thing here is that we are exporting more columns than the Statistics Collector has, namely the Scenario and Replication number. However, the expression in the From FlexSim Column column must be valid FlexQL (FlexSim's internal SQL language). Wrapping the values in Math.floor() leaves the values unchanged, and works well with FlexQL.
Setting up the Experimenter
Finally, let's take a look at just a little bit of code that makes the Experimenter dump data to a database. There is code in Start of Experiment, Start of Replication, and End of Replication:
In the Start of Experiment, we need to clear the table. The code in this trigger connects to the database, runs the necessary query, and the closes the connection to the database.
In the Start of Replication trigger, the code simply copies the replication and scenario values into the global variables we created for this purpose.
In the End of Replication trigger, the code uses function_s to call "exportAll" on the database connector that we created. This uses the settings on the Export tab to dump the data from StatisticsCollector1 to the database table, including the replication and scenario columns.
These same triggers run during an optimization, so the same logic will apply.
Run the Experiment
Finally, you can run the experiment. When each replication ends, the Statistics Collector data will be exported to the database. Databases can handle many connections simultaneously. This is important, because the child processes that run replications will all open individual connections to the database at the end of each replication. This leads to many concurrent connections, which most databases are designed to handle.
But how can we tell that it worked? If you connect to the database with another tool, you can see the result table. Note that there are 1700 rows, and that data from Replication 5 is included.
Additionally, you could use the Import tab to import all the data, or summary of the data data, into FlexSim.
Conclusion
Storing data in a database during an experiment is not difficult to do. There are many excellent tools for analyzing and visualizing database tables, which you could then use to further explore and understand your system.
