August 1, 2011

Accessing SQL Server from Simio

Getting information from an external data source is a common requirement for a simulation tool. This can be either for dynamically building the simulation model or for reading values, e.g. arrival times, during the simulation run. Simio supports the ladder out of the box mainly with the following two features:
  • Tables can be filled from and bound to Excel or CSV files
  • The Read and Write steps allow access to text files during the simulation run

Import or bind tables to Excel or CSV files in Simio.

The ambitious modeler can enable Simio to access any other data source by writing custom steps and elements against the Simio API in C#. Not everyone is keen to programming, though, and to make life a little easier for those who want to read from a SQL Server database, we have started to implement a set of elements and steps to do exactly this.

The new element appears in the user defined step menu.

To begin with, we have written an element that represents a connection to a SQL Server database. The first step to use this connection is the "SQLReadSingleValue" step, which expects  a SQL statement that returns a single column and row i.e. exactly one value. The return type can either be numeric (double) or string at this point. The SELECT-statement can contain parameters  in the form of placeholder questionmarks. The user specifies the parameter values in a list of expressions in a repeated property group in the order as they appear in the SELECT-statement.

As mentioned, this element and step is only the start. There is already a plan for a step to insert rows into a SQL Server table. There are many other things we think about. Here are some thoughts of what could be done with the API in combination with SQL Server connectivity:
  • A way to fill Simio tables from SQL Server, which is now possible from Excel or CSV only.
  • Read multiple values at once and assign them to multiple state variables; this would probably be implemented within the same step as the single value select.
  • Generate models or parts of it from information in a SQL Server database. This would be implemented in a Simio Design Add-In.
  • Generate experiments from information in a SQL Server database. This would result in a Simio Experiment Add-In.
  • Write statistics to SQL Server during the simulation run; this could offer better analysis of results compared to what is possible now. Once the data is in a relational database it could easily be transformed into a dimensional model. Think Cubes, OLAP etc.
Furthermore, all of the extensions currently written for SQL Server could quite easily be applied to other data sources. The ADO.NET library for contains classes for connecting to Oracle or any OLE DB or ODBC compliant data source. Right now, only the SQL Server classes are used, but could easily be substituted.

If anyone is interested in what we do here, please leave a comment or contact us via our website. Very soon we are going to offer the first elements and steps for download here for you to test. Also, we are open for any kind of cooperation with anyone who wants to develop extensions for Simio, or who requires help implementing custom logic for Simio.

We are anxious to hear your feedback!