Hi, everyone!
You must have noticed that in the latest version of AnyLogic, AnyLogic 7.2, all models obtained a new object at the project tree — the Database object. What does this mean for developers? The built-in database feature helps to build the models that require a certain amount of external data for initialization and work. There are four primary benefits:
- It removes extra connectivity layer between Excel/external DB and model. The database is now available in all editions, including free AnyLogic Personal Learning Edition.
- It is a useful instrument for visualization of agent type hierarchy and development of the data model.
- It may act as an information dealer for agents, simplifying the algorithms, encapsulating the data and serving as a unified data source for parametrized variables.
- At last, it stores the simulation statistics, simplifying the data post-processing and export.
Each of these advantages has many use cases, let's see each one in details. Also, at the end of this post you may find some tips for work with the database, and a brief description of further enhancements.
Convenient access to database and one-click automatic import of data
Import of initial data to the built-in database is an easy task. The filling process is clear and simple; no need for coding. The database allows to import data from Excel file, Access or SQL database — just click "Create or import a table..." text at database properties or the right-click context menu. Also, data can be simply copy-pasted from Excel to the table (and vice-versa).
If the "Update data..." checkbox is selected, then all changes to the source will be applied to the database.
Before starting to explore the database capabilities, let's look how we may interact with the database from the model canvas. So, the database may be accessed by SQL commands or Query DSL Java code. We provide an intuitive interface for most common use cases, so the modeler dows not have to write SQL queries. E.g., if you need to feed a population from a database or define arrival table at Source block, just use the graphical interface of object properties. Most objects support the graphical interface, from usual parameter to flowchart blocks. Here is the example for source block that allows to use a database table as arrival table.
Also, there is a possibility to write the query code. For users, which do not know SQL, we provide a Query Constructor (a wizard that generates the required code depending on user input). It allows to get the specified value, import the table column as a list, iterate over the column elements, etc.
The best way to start learning built-in database is to look at How-To models. They are available in AnyLogic example models: Help – Example Models – How-To models – Database Connectivity.
Advantages of new way to load data into the model
The Excel file connectivity tool supports only code mode, forcing the user to learn Java, painstakingly match the row/column indexes, and rewrite the code following a change to an Excel table. It is a tough task, especially when the file should feed the population.
Built-in database dramatically simplifies this process, allowing to remove completely the dozens or even hundreds of lines of code.
Now, the population may be initialized in two clicks, through mapping the column name with the parameter name. Sometimes one parameter may represent several characteristics, e.g., agent name matches with the agent location (say, a city). In this case, you can add the GIS location name parameter as a second parameter, map it with the same column, and an agent will be located at the respective city automatically! Search for the name at the GIS map, and the most relevant result will be applied.
Create agent types from tables
When you develop an agent-based model, firstly you plan how to model its actors — agents, and structure of their interaction. Enhance visualization of this process by using the database tables. Each table may represent an agent type. Once all tables are created, right-click at the table, select new agent type, and the wizard will display the column names as agent type parameter names, and the table name as agent type name.
Parametrize everything
Most objects support a database model — they will be initialized using the query. Click the blue sign of equality for enabling the database reference mode. Here you may specify the value using the graphical interface: select the table, the value column and write the simple boolean expression that matches the condition.
Experienced users may write the query code directly. Sometimes it is not necessary to create a parameter for a particular value, e.g. if this parameter is final. It can be a statechart transition timeout. In this case, you may ask the database for value.
This rule is also applicable for final collections that accept the entire column as a list.
Modify the database at a model run through with the INSERT and UPDATE queries. Sometimes it is convenient to organize a custom output tables, or update the current data at a model run.
Process automatically collected data, customize the log and export it automatically
When logging is turned on, built-in database saves almost every action in the model: sent messages, movement stats, covered distance, parameters, resource utilization, statechart transitions and other stuff. The log consists of two parts: tables with raw data and views. Copy views to the main part of the database, and then exported to Excel file, just press "Create an editable copy of this view" at log view properties.
The database view is the representation of the query that builds a summary table using the database or log tables. They are editable, so you may adjust the query in "View definition" code box and export the required table.
If you want to collect only specific views, e.g., movement, then ignore the other views, and the logger will consider only the active views. This trick allows to increase the performance of large models.
There is a unique log view that collects a user-specified input: trace_log view. You may call a traceToDB(...) function at any place of the model and collect custom data to the log.
Specify the views and tables for export in the respective wizard, similar to import wizard. Specify the file, the tables and optionally enable autoexport.
Hints and features
Here are some tips for work with database tables:
- Tables support ascending/descending sorting and filters, similarly to Excel functionality. Just click the small arrow under the column header;
- Group the tables, structuring the database tree (e.g., input-output tables, or grouping depending on subject area, etc.);
- Tables have Excel-like functionality: double-click at column board to resize. Select the entire table by pressing Ctrl+A (and then, e.g., copy-pasted by Ctrl+C — Ctrl+V command);
- Table cells support multi-editable mode — select several cells of the same column, and then press F2. Depending on column type, you will be able to set the value to all columns, increase or decrease the current value by certain value, etc;
- The dependent columns show the drop-down list of the possible variants from the parent table column. The dependency between tables established by a foreign key that consists of the table name and column name. The parent column should have "unique" checkbox selected.
The foreign key is also considered by database views, allowing you to build a summary table.
Data types and advanced usage
The database can store String, numbers (int or double), Boolean, Date, Options list and Code types. The Code type allows to store the expression (e.g., triangular(1, 2, 3)), and then use it at the "delay time" of the Delay block.
Sometimes you need to read an object/field name from the database (e.g., color of the shape). In that case you may use the function executeExpession(Class<T> returnType, String code). This function takes the Sting as a code and tries to return the result of the specified class. For example, if you store the color as "blue" string in the database table, then this function may turn it in the reference to blue color.
The database is stored in "database" folder, nested in the model folder. To share the model, include the folder in the archive case. The external tools may connect to database as well, e.g., for visualization of table connections.
We are continuously enhancing the database functionality and usability. The next AnyLogic build will include support of undo-redo operations, automatic table resizes, performance and stability improvements.
Thank you for reading this post, I hope it will be helpful. Feel free to leave your feedback on LinkedIn, and don't forget to share it with your colleagues by clicking on one of the buttons below.
The live demonstration of database features and their usage in the simulation you may find at webinar devoted to the release of AnyLogic 7.2.
Happy developing!
