SIMUL8 feature spotlight: SQL server connections
In this feature spotlight, we take a look at SIMUL8 2020’s enhanced Database Connections, now compatible with SIMUL8 for the web and SQL servers.
With the rise of digital twins, many of our users not only want to populate their simulations with larger data sets, but to be able to do this in real-time. That’s why we’ve introduced a new and improved SQL connection setup wizard and syntax builder in SIMUL8 2020 to help you build multiple database connections and queries, without the need to use Visual Logic.
You can use this powerful feature to pull in data generated from sources such as ERP systems or production schedules to continually feed your simulations with data. You can also use the syntax builder to quickly populate a query of data sets to import. For example, you might want to only pull in orders expected on a certain date.
Let’s take a look at an example of the enhanced feature in action.
Here we have a simulation of a manufacturing line. We want to pull data, in this case an order schedule, from our ERP system on a daily basis.
As with all of SIMUL8’s data connection features, SQL Connections can be accessed from the ‘Data and Rules’ tab on the ribbon, in the ‘External Data Sources’ group.
When we select the SQL feature, you’ll see the ‘SQL Database Queries’ dialog box. This uses the same layout as SIMUL8’s other data connectivity features to ensure a consistent, seamless experience no matter where you are pulling in data from.
To create a new connection, click the ‘New’ button on the dialog box.
- Firstly, we want to define the server location and the port.
- Then, we define the database name that we want to connect to, in this case, ‘orders’.
- You can also enter a username and password if the database is protected for security.
- By clicking the ‘Test Connection’ button, you can check that the details you entered are correct. If successful, the ‘Database Table’ dropdown box will be populated with a list of tables that you can connect to. For our example, we’re interested in the ‘Products’ table.
- In the ‘Spreadsheet Variable’ dropdown menu, we want to define an Internal Spreadsheet in SIMUL8 where we will import our data from the database. Here, we’ve set up an Internal Spreadsheet named ‘ssRealTime’.
- Like other data connections in SIMUL8, you can also define the data flow. As well as importing data, you can also export data and results back to a database.
By selecting OK, our connection has now been set up! To import the data, highlight the connection and select the ‘Import/Export’ button.
To access the imported data, select ‘Spreadsheets’ from the ribbon (under Data & Rules) and choose the ‘ssRealTime’ spreadsheet from the dropdown. As we scroll down the Internal Spreadsheet, we can see all our data has been seamlessly imported from our ERP system.
Where SQL connections really come in to their own is through the ability to quickly and easily filter the data that you’re importing into SIMUL8. The goal of our example simulation is to be able to run the simulation daily, based on a daily schedule that we’re pulling from our ERP system. We don’t want to pull in all the detailed information from the last week or month, just today’s expected orders.
So how do we do this? We’ll re-open the SQL Database dialog but this time, instead of selecting ‘Import all’, we’re going to choose ‘Custom’. You’ll see a new ‘SQL Query’ textbox. This is where we can write our query to filter the results. Don’t worry if you’re not familiar with SQL, as part of the SIMUL8 2020 release, we’ve also implemented a handy SQL editor so you can simply populate your query.
Our SQL query will look at the Products table of our database and select the specific orders from Day 2. You can also choose to clear any existing data from the spreadsheet before import.
When we go back to our Internal Spreadsheet, we can now see that only today’s orders have been pulled through from the database.
Simple, right? Our enhanced SQL Server Database Connections are an easy but powerful way to effortlessly populate simulations with large, real-time data sets from databases.
We’re excited to see our users utilizing this feature to create digital twins of their processes to analyze current performance, test how the system will cope under different scenarios and understand future performance.
This new feature, along with other additions including Google Sheets and Process Mining integrations, are now available for all new license purchases of SIMUL8 Basic, Professional or Team. Our Annual Maintenance customers have already received an email invitation giving them first access to SIMUL8 2020.