Communications failures and alarm areas

I discovered Inductive Automation’s products last week and I haven’t done much since then but play with them! After 18 years working with SCADA packages I’ve finally found a system that makes sense!

I don’t know if this is exactly the right forum, but I’d like to check whether a couple of areas important to us are possible with Factory PMI/SQL.

During our testing we logged data from the KepWare simulator OPC server into one record in a table to act as a real-time database. We noticed that if the FSQL to OPC server link went down (say FSQL was stopped), FPMI would continue to display the last record as if nothing was wrong, and would allow control commands to be sent and update the SQL database (but obviously not get to the OPC server).

Is there any way of flagging up communications failures and warning the user that this has happened?

The other thing we want to do if possible is keep track of alarms in separate plant areas. Often in previous systems we have put a buttonbar on the screen that has buttons for main areas in the plant. We have then animated the background colour of these buttons to show the alarm status of each area - unacknowledged point(s) in alarm, acknowledged point(s) in alarm, unacknowledged point(s) out of alarm, no point(s) in alarm. In this way the user gets a quick overview of the alarm status of the plant and where any alarms are occurring.

Is there any way of implementing this?

Thanks for the compliment!

Good eye. You actually have happened upon what is one of our architecture’s few weaknesses. Because of the highly de-coupled nature of FactorySQL and FactoryPMI, FactoryPMI doesn’t have much contextual information about the data it is reading and writing. Before I go into some ways to add this contextual information, I just want to mention that this issue is completely resolved with the addition of our much-anticipated SQLTags feature, which will be part of FactoryPMI 3.0 and FactorySQL 4.0.

Ok, so, first thing to know is that in FactorySQL, in the group configuration, you can tell it to store the quality code in the table. FactoryPMI can then check this quality code to know if the quality is good (good=192). This covers cases like losing comunication to the PLC.

Now, for detecting whether or not FactorySQL is running. Make a new group that simply has one action item in it that runs a SQL query like “SELECT CURRENT_TIMESTAMP”, and stores it to a database column named FSQL_Tstamp. Have this group update the first row of a table, say every 5 seconds or so (lets call the table FSQLStatus. Now a query like this in FactoryPMI will return 0 or 1 indicating whether or not FactorySQL is running: (this example is using MySQL syntax. Your syntax may vary slightly if you are using a different database)


This query will return 1 if the timestamp is less than 20 seconds old, and 0 otherwise. You can play with the timing here if you want a faster feedback. Like I said before, we know this isn’t the most intuitive thing in the world, but FactoryPMI 3.0 will fix that (due out in a few months)

Now, for your second question about alarms: Yes, this a fairly common task. You just need to make sure that your FactorySQL project’s groups are separated into folders that correspond with your plant areas. This way you can run SQL queries on the alarm log table that can give you this information. Typically when you want to color something like a button background dynamically in FactoryPMI, you want to boil your information down to a code, something like: 0=No alarms, 1=Unacked In Alm, 2=Ack In Alm, 3=Unack out of Alm

In this case we are going to run 3 queries(per plant area) that will tell us the following:
Are there any unacked alarms?
Are there any acked alarms?
Are there any unacked cleared alarms?

The queries would look something like this:

SELECT COUNT(*)>0 FROM alarm_log WHERE clear_time IS NULL AND ack_time IS NULL AND group_folder LIKE 'EastArea%'
SELECT COUNT(*)>0 FROM alarm_log WHERE clear_time IS NULL AND ack_time IS NOT NULL AND group_folder LIKE 'EastArea%'
SELECT COUNT(*)>0 FROM alarm_log WHERE clear_time IS NOT NULL AND ack_time IS NULL AND group_folder LIKE 'EastArea%'

Now, the best way to put this all together is to make a button with the following dynamic properties:
[li] AreaName (String)[/li]
[li] UnAcked (Integer)[/li]
[li] Acked (Integer)[/li]
[li] ClrUnAcked (Integer)[/li]
[li] State (Integer)[/li][/ul]

Now, bind the UnAcked, Acked, and ClrUnAcked to queries like the ones above, except replace “EastArea” with the value of the AreaName property (using the property binding button in the SQL Query binding window)

Now bind the State integer property to an expression like:

binEnum({Root Container.MyButton.UnAcked}, {Root Container.MyButton.Acked}, {Root Container.MyButton.ClrUnAcked})

(You can look up what the binEnum expression does in the manual under:
Technical Reference / Expression Langauge / Logic Functions)

Now your button’s State property has it’s zone’s alarm state encoded like I mentioned at the beginning, and you can directly bind it’s background color to this state, and set up your color mapping. Then you just copy this button for your other areas and only have to modify their AreaName property.

I know that sounded like a lot, but its really not too bad. Give us a call if you’d like us to walk you through it.

Thanks for your interest,

Hi Carl,

Thanks for the quick reply. I’ll try out your suggestions and post back how I get on - it’s heading for midnight so it’ll have to wait for Monday.

I’m assuming with your comms failure detection that it would be easiest to do this for one object which displays an appropriate banner across the screen if comms have failed. Attaching this functionality to every object on the screen (to display an ‘unknown’ value) may be too much work. I may also have to put a block on writes to the database if comms have failed, as otherwise the user could send a number of commands which could all possibly be actioned when comms were restored.

Regarding the alarm groups - any easy way of making them hierarchical? (Don’t you just love users - give 'em what they want and they just ask for more!)

Finally - do you have any more information on SQLTags? I’ve seen it mentioned several times in the forums.

Thanks for your help.


Yep, thats right.

This is what the “OPC Wins” means in FactorySQL’s “Bi-directional, OPC Wins” mode. When comm is restored or FactorySQL is started up, the OPC side will “win” any contention, so any writes that happened while the device was down won’t be issued.

Regarding the alarm groups - any easy way of making them hierarchical?[/quote]
I don’t think I’m following - what is making it hard currently? Groups can easily be put into folders, thus creating the hierarchy.

Sorry nope, we’re too busy creating it! Essentially what it is is a way for us to store a traditional tag tree inside of your database. This effectively accomplishes 3 things:

FactoryPMI can now know all sorts of metadata about its values (data quality, engineering units, documentation, etc, etc)

The FactoryPMI designer can browse your tag structure, which can mimic your PLC’s data structure, directly. This makes for much easier designing.

Most design work can be done from FactoryPMI - decreasing the need to jump back and forth between FactorySQL and FactoryPMI.

Hope this helps,

I’ve gone through your post and tried out your suggested solutions to the problems of indicating a comms failure and grouping alarms.

I was able to display a message when a comms failure occurred - however, I had to change your query to get it to work. The TIMESTAMPDIFF function in MySQL expects the later time to be the second parameter. Also as it was supposed to display when true, I transposed the 0 and 1. The working query was:

This works, although it would still be nice to check for bad data quality for each point on that screen - this may have to wait for SQLTags. The ‘OPC Wins’ mode also worked exactly as advertised.

I got the alarm grouping working quite successfully. At first I thought we would have a problem when multiple alarms occurred in a group, but I found that the binEnum function prioritises the alarms by virtue of selecting the first true value from the list - very clever! I attached a new ‘AlarmArea’ Dynamic Property to the alarm table and added this property to the table’s Where clause. I could then display alarms from the groups I selected, including alarms occurring in a main area and all sub-areas. One thing I did notice with this was that an alarm area such as ‘\Real-time data\Area 2’ has to be written as ‘_Real-time data_Area 2’, as MySQL didn’t like the slashes.

The more I use the software, the more impressed I am by it. I’ve always liked being able to see exactly what is going on and this software feels very open. The one thing I haven’t got a clear picture of yet is system performance. Have you carried out any performance testing which you can release showing how many points and how many clients a typical system can handle?

Sounds like you’re moving right along. We do strive to have an architecture that is as open as possible.

As for your performance, the answer is somewhat complicated. First of all, any answer of course is tied to a specific hardware configuration - more hardware = more capacity. Second, FactoryPMI supports load-balanced clustering, which means the number of concurrent clients can expand practically infinitely. The real bottleneck then becomes the database, which brings us to the fact that modern databases are also expandable practically infinitely (think about the databases that power, eBay, etc). In short - our software falls in line with modern IT software architecture, and scales similarly.

Now, in practical terms, throwing all 4 pieces (OPC, FactorySQL, Database, FactoryPMI) on one server in the $3000-$7000 range, expect to run 10-30 concurrent clients, and somewhere in the 3k-15k point range. (these are large ranges, and depend greatly on parameters of the actual project’s design - how many queries per second does each screen run, what are your group’s sample rates, etc).

FYI - SQLTags increases our scalability significantly, and will bring us into the 100k-500k tag range. We will also publish some actual benchmarks after the SQLTags release.

Phew, that was a mouthful.

To address your system performance question, the best answer (as of now) is - you’re not going to be satisfied with my answer. Formal benchmarks will be released with SQLTags. I will describe why our architecture works and scales well.

I’ve seen a range of system performance that depends on configuration and hardware. Our typical performance ceiling is typically caused by too much traffic on the SQL database and indicated by high CPU utilization. Here are some factors that affect performance:

-FactorySQL provides a single point of communication to the PLC. This is very efficient on the PC<->PLC path compared to setups where each client communicates with the PLC directly. The use of FactorySQL block data groups considerably increases performance.
-FactorySQL polls all database values on DB->OPC or bi-directional groups. Database load increases with tag count and update rate, but not clients. This can lead to bottlenecking with lots of bidirectional tags at a rapid update rate (when not using block data groups). SQLTags will make this insignificant with something like a dirty bit that will allow FSQL to check a fixed number of values rapidly.
-The FactoryPMI Gateway is pretty light. It’s a trimmed down web server whose main job is receiving SQL queries, running them against the database, and streaming (often compressing) the text based response to the runtime clients.
-FactoryPMI clients do all the 2D/multimedia rendering themselves using Java. This performs much better than a system that uses terminal services or any other remoting system, which is heavy on server processing power and network bandwidth. As of now, each client runs its own queries against the database. While this scales much better than each client addressing the PLC directly, it will be improved upon with SQLTags where the Gateway runs/aggregates all the queries and multiple clients with the same window open just means more network traffic.
-Project design tends to be the most significant factor. Things like putting indexes on your timestamp columns in the database will help you out. Suppose an FPMI overview screen has hundreds of little status objects that need to be updated several times a second. Often you can write a single query that brings in all the data as a dataSet, then bind the objects to that dataSet instead of having a separate SQL query for each object. Other such things can greatly speed up or slow down your project. Again, SQLTags will achieve much of this for you.

One of the coolest features of our architecture is the scale out model, which works like servers on the Internet. The idea is that instead of necessarily upgrading, you can add hardware. This tends to be a much more economical approach.

A project might begin with a single computer running FactorySQL, FactoryPMI, and the SQL database. At 20k points and 5 concurrent clients, you might decide that you need better performance and possibly redundancy. That would be a good time to separate the SQL database to a dedicated computer. Suppose users frequently pull up reports and graphs with lots of data (>100k data points). You might then add another SQL database on a separate computer just for historical data, seamlessly to the end user. As you add data points and users you’ll want to separate FSQL and FPMI to their own computers. At some point you could add several computers to the FPMI Gateway cluster, providing client load balancing and redundancy. FSQL projects can be split across nodes and fault tolerance is provided via backup servers. In this way projects can be scaled to a very good size. I would recommend that you try taxing your system in your office. Computers can run multiple instances of FactoryPMI clients to add load.

It gets tricky in the large project range (100k-1M+ tags). There you really want to carefully consider your PLC/SQL database layout and FSQL/PMI communication techniques in the design phase. It becomes important to use better equipment, separate functions with computers, and consider SQL database clusters, etc.

Expect quantitative benchmarks with SQLTags by October.

Enough guys, enough. I get the message - SQLTags will solve all my automation problems, make the tea and put the cat out at night :laughing:

Some final quick questions for you:

  1. Have you ever worked on a system that allowed data entry via barcode reader on one of your View nodes?

  2. Would using an in-memory database table help performance on a real-time tags table?

  3. Are you selling shares in IA?

I always find working on a real-life example helps me learn fastest, so I’m converting a system where we used iFIX (and lots of VBA code) to front a MySQL database. I’ve never used Jython (or Python) before and I’m finding it makes for very compact code.

We’re considering proposing to one of our customers that a planned upgrade to an iFIX system should become a move to FSQL and FPMI. I’ll maybe email you with some details and see whether you think it is a valid proposition (and whether I should wait for SQLTags to come along :wink: ).

Thanks again for your responses.


It puts mints on your pillow too! Quite remarkable :wink:

The only problem with the forum is we don’t know when someone else is already writing a response to a question! Sorry for the information overload there…

  1. Yes. We have seen both key-board emulation barcode readers (no customization needed) and barcode readers that send out some sort of UDP packet over the network. We have a little FactorySQL plugin that can harvest the latter if you need it.

  2. Yes. Theoretically, most standard databases would keep this sort of table in memory by default anyhow. MySQL has an interesting memory engine, but unfortunately it is a little hard to use, because your table definitions get lost across restarts.

  3. No, sorry, it is privately held :slight_smile:

We get a lot of business from conversions like the one you mention. Feel free to email us details, we’d be happy to offer our advice.