Discrete processes : OPC / historians or OPC / SQL database?

Hello Historians and databases Gurus,
We are looking for solutions to upgrade our current Labview-based application that takes data from PLCs through OPC servers and store them into a home-designed binary file structures, locally on a PC.

These are our specifications:

  • All our processes are parts driven (discrete). We are not dealing with time-based processes
  • One part is produced every 1min
  • The data acquisition is triggered by the polling of a ‘trigger’ (counter) and when this counter increments the application get all related data. In other words, nothing happen during 1min (just the trigger scrutiny) and all data transfer is once per minute.
  • Per cycle we are taking about 15.000 tags (16 bits words) per machine
  • Per plant we have roughly 10 machines, all with a cycle time of 1 min. One PC today is connected to 1 or 2 machines. So 5 PCs per plant roughly.
  • Emphasis given on the visualization of the data close to the machine to help the operator in his process adjustments. Here the operator needs to visualize all parameters for a specific machine cycle, overlay profiles (a profile is a serie of tags for a defined cycle), graph parameters against cycles or against time (but even against time the different points of the graph mean a specific part produced)
  • We do not really have time constraints as to the time it takes to fetch and write the data into the database. It can take 10s, we do not really care.
  • Open database so that people can make cross-machines analysis of data (data mining)
  • Cost-effective solution

An internal discussion lead to considering that instead of writing into a local home-made Database we could simply write into a centrally located SQL database (located on a plant’s server).

I know that Historians could also be a way to go but do not understand the advantages that we would have especially considering the specs given here-above.

What solutions would you recommend to implement or to study?

Thank you very much to share some thoughts with me

How do you visualize the state of 15,000 tags? Can you provide a screenshot of what operators look at when comparing machines or cycles? Does the data tend to be similar on a given machine from one cycle to the next?

Visualization windows: the Labview application that we wrote provides several ways to visualize the data:

For the local user close to the machine under supervision, operators mainly use the 3 following windows of the application:

Window1/ All process data values for a machine (can be thousands of parameters) displayed for a specific cycle number selected. Displayed in a kind of two-columns table. The selection is either a cycle number or date/time (in this case the application fetches the cycle closest to the date/time requested)

Window 2/ Some parameters are what we call profiles ie. collection of consecutive tags. Those must be displayed as curves for a selected cycle. For example after each cycle we collect pressure profiles, each profile being 500 consecutive tags.

Window 3/ 5-graphs, each one able to display a parameter against cycle number or time. Even when time is selected as X-axis, each point will represent the value of the parameter for a part produced.

The application also gives the possibility to set limits on parameters (different limits for different products). Those limits are displayed in the graphs of Window 2 and in the Window 3 interface as well.

For more advanced users performing off-line analysis they use the same windows and also an interface that allows extraction of parameters into CSV files for further data mining.

Thanks again for your thoughts.

Honestly, your description sounds like the classic use case for the SQL Bridge module. Since you’re just collecting data, you could get by with the cheaper limited edition, which allows you to use the “historical group”, but given the number of tags, and your description of their use, the full edition with the “block group” might be necessary.

The historical group maps tags to columns in a table. On the trigger, it will insert a row. You can have any number of these groups, going to any number of tables, but databases only allow so many columns. If you can break up the data in a way that makes sense, this might be an option. You could have multiple groups insert rows into the same table, perhaps with a static id column. Or, you could potentially do something in the plc to loop through the data and store it one row at a time.

However, since it seems that many of your data points are elements of arrays (that is, multiple samples of one conceptual point), the block group might work better. It maps multiple tags into rows under one column (and of course, you can define multiple columns in a group). When this group is triggered, it will write multiple rows to the database at once.

Just some thoughts, don’t know if it gets you much further…


Hello again
Before analyzing the details of a choice I am trying to understand if Historians are giving me benefits into my picture ; the fact that reading users’ experiences and vendors advertisements, Historians are meant for:

  • time-based recording (not the case in our processes, we use the Part as the reference)
  • huge data throughput to database (not our case, we log data once per cycle, lets say every minute)
  • Big volume of data: that is maybe (not sure) a pro for historian but I am not even sure. A typical plant includes 30 machines ranging from 100 to 10.000 tags to collect once per minute. It does not matter if the collection phase itself take a few seconds. Accoding to this: inductiveautomation.com/news/item/218 it is not clear that Historians bring an advantage…

I do not know if there are integrated tools (SQL analysis) that exist if we get rid of the historians and we go towards the SQL-only database.

Thanks again

You can actually do what you’re looking for with Ignition. Ignition has a built in Real-Time Historian, but as Colby pointed out, for your process you would be looking at using Transaction Groups. You can set up Transaction Groups to record data however you want. You can use a Transaction Group to record data based on time or a trigger. You set up the Transaction Group to record the data in whatever format you want. Ignition also has the built in tools to perform the SQL analysis, queries, trends, charts, tables. You can even use both, Ignition’s built in SQLHistorian and Transaction Groups, use the SQLHistorian data for troubleshooting, use Transaction Groups for your process data.

Aside from being able to log data in your custom format, having the openness of a SQL database, Ignition would probably lower your cost in administration and capital investment. You could set up Ignition on a central server logging all your plant’s data, you would have one machine to administer, one system to develop. Clients are web-launched through Ignition, there’s no additional licensing for clients, no software to set up and administer on client machines.

Download a free trial version of Ignition and install MySQL (also free) and play around with the Transaction Groups.

More discussion of this on control.com if anyone cares to chime in.


Hello again,
I ran the Ignition demo and found it interesting. Nevertheless there are some topics that are critical in my application and that I do not see covered (either the product does not cover it or the demo):

  • Is it possible to set limits on parameters? I am not talking about SPC limits but rather CPC (continuous process control) ie. fixed limits (maybe 2 different sets per parameter: warning and in alarm). Limits are not displayed in the charts that the demo shows
  • Parameter selection: the number of parameters displayed in the demo is very limited hence the selection process is merely to tick a checkbox. In my case a machine can be populated by up to 20.000 parameters that we want to split the selection in subgroups in which we can have up to 1000 parameters sometime. I do not know what the capabilities are in Ignition to do that: subgrouping parameters and how the selection of parameters would work in this case
  • Profile parameters: we have different categories of parameters: setpoints (that the operators can change on the HMI) and measurements. We also have setpoints profiles and measurements profiles. Profiles are a serie of tags, for instance a Pressure profile captured at the end of a cycle is represented by 300 consecutive tag. So there should be a means to define this Profile object and also a way (a dedicated interface) to display such profiles.

Thanks for the precisions

If I'm understanding your use of 'parameters' and 'limits' right, this should all be possible.

Every SQLTag has limits that are off by default but can be turned on in the tag editor. This will restrict user input to the range you want. Also, alarms can be set for various ranges on each tag to alert you if a tag is going out of range.

I think you are talking about looking at a chart. You can dynamically change which tags are showing on a chart's pen list by binding the underlying datasets or using scripting to modify/create them.

You can use SQLTags to set up whatever profiles you want. They can be grouped to represent any of your machines/areas/etc.

It sounds like you have not played with the designer much, we have videos online or you can request a demo from one of our sales people to show you some of the common uses.