Home / Resources / How To: Choose the right type of group in FactorySQL

How To: Choose the right type of group in FactorySQL

Most users are very familiar with FactorySQL's Standard Group. It is by far the most common type of group used in FactorySQL projects. However, they may not be aware of the other types of groups available, or the purposes that they serve. This quick overview of the groups will help arm the user with a better understanding of when to use each type of group.

Standard Group

The standard group is called such because it was the first type of group in FactorySQL. It's flexible, highly configurable, and accomplishes much of what users want FactorySQL to do.

Data format: Data is stored and read from the database on a row basis. Each item in the group maps to a different column in the same row of the database table.

Special characteristics: Individual items can have their own mode, meaning they can be db-to-opc, bi-directional, etc.. Additionally, Action Items can write their values to OPC items, allowing you to perform calculations, database lookups, etc. and write the results back to the PLC.

When to use: When you want to map values to columns, and need fine grain control over item mode, row selection, and triggering.

Historical Group

The historical group is a simplified, history only version of the standard group. It is straight forward and easier for new users to configure. It always uses buffered data writing to the database.

Data Format: Like the standard group, each item is mapped to a column in the table. A new row is inserted for each execution.

Special characteristics: Buffered data writing is always used. This feature is available on standard groups, but must be turned on from the Advanced tab (advanced settings must be turned on in "frontend settings"). Buffered writing has several benefits, first and foremost that no data is lost when switching over to the data cache if the data connection goes down. Also, when using a trigger to log "burst data", the information can be recorded at a much quicker rate than would be possible without buffered writing.

When to use: When storing historical data either on a timer or a trigger.

Block Group

The block group is similar to the standard group in terms of features available, but is completely different in terms of data format. Instead of mapping each item to column, many items can be placed under a single column, and will be written as different rows in the table.

Data format: The block group defines one or more columns. Each column can have any number of items under it, which will be written as rows in the table. Collectively, the rows and columns are known as a "data block". Additional information, such as the block id and row id may be stored as well.

In terms of configuring the block group, a column is defined as a "block item". Each block item has one or more "segments"- a unit that defines which items it contains. There are "explicit segments", in which each OPC address is listed explicitly, and "range segments", which define OPC addresses by way of a template and a range. The latter segment type allows the user to very quickly define arrays or a group of tags that use a similar naming structure in the plc. A column can have any number of segments- ultimately they will be put together to form one list of addresses. This allows the user to mix and match segment types to get all of they data they want under one column.

Special Characteristics: All data is written to the database inside of an efficient transaction. The cost of additional columns is minimal, therefore by creating block groups with a number of columns you can achieve very high throughput to the database. It is usually advisable to arrange data in a way that makes sense according to how it will be used, but if the data lends itself to this layout, block groups can be extremely efficient.

When to use: Block groups are great for storing array data vertically in the database. They can also be used very effectively when there are many identical objects in the PLC, for example tanks in a tank farm.

Stored Procedure Group

The stored procedure group operates much like the standard group type, however instead of mapping values to and from a database column, they are mapped to parameters of a stored procedure.

Data Format: OPC and Action Items can be mapped to input parameters of a stored procedure. Furthermore, OPC items can be mapped to output parameters and the return value. Additional data, such as the timestamp and overall quality code, can also be mapped. The actual stored procedure will be responsible for handling the data and returning values.

Special characteristics: Stored procedures can sometimes be called from Action Items inside of the other groups, depending on the nature of the procedure. However, only the Stored Procedure group can accommodate output parameters.

When to use: Anytime you need to map OPC and Action Item values to and from stored procedures.
published: 04/30/14