I'm seeking advice on the best design approach for data storage in our upcoming OEE system using Ignition. Previously, we've developed detailed dashboards and troubleshooting systems for individual machines and processes. However, our focus has now shifted to creating standardized data models for all machines across the plant, or at least as standardized as possible given the different processes involved.We'll be developing a standard UDT and creating classess for equipment and for data mapping, which will include typical OEE information from each machine, such as reason codes, reject information, and run states amongst other items.My main question is about the most effective long-term method for data storage. In our past systems, we used a mix of tag history and transaction groups writing to SQL tables. This approach was somewhat ad hoc, decided by engineers on a case-by-case basis, and while it worked for those applications, it lacked proper contextualization.If we follow the ISA-95 model when creating our UDTs, what is the best method for data storage? Should we use Ignition's built-in tag history, which stores data in Ignition’s tables, or should we continue using transaction groups to write data to custom SQL tables designed by engineers?
Does anyone have insights on the pros and cons of both approaches? I realize the post is missing a lot of context and information—apologies for that.
Also, keep in mind that anything that can be done with a transaction group can be done with scripted DB calls. This is particularly helpful when you need something for every instance of a UDT.
(But don't do SQL insert/update calls directly from a tag's valueChange event--use the Store and Forward versions.)
Your reply led me down quite the rabbit hole over the last 24 hours, reading about indexing, binary trees, and table widths. It's fascinating stuff!
To summarize my understanding: it really depends on the specific use case and application.
Is there anywhere I can read further on the following comment?
The primary reason I'm considering using tables instead of a historian is for duration tracking ( aside from adding the structure to the data I think it will provide). I'm thinking of using an on-change script to store the reason code along with the start timestamp. Then, when the reason code changes again, I would store the end timestamp in the same row and perhaps run a duration calculation within the script. This way, each entry in the table would have:
EventID
StartTime
EndTime
Event Duration
But you suggest avoiding this method directly?
Also and aside , using the table method would mean that I wouldn't be able to simply drag and drop into power charts. If I wanted to provide operators with trend data, I would need to configure time series charts instead.
For a detailed discussion of techniques for the last bit of advice in that topic, see this:
I recommend you not do this. Just store the timestamp, event value, and some kind of process/machine/component ID with S&F.
Databases are really good at delivering the actual durations after the fact using LEAD() or LAG(), without having to do anything special to account for system start/stop, scripting restarts, and/or tag restarts.
Thank you @pturmel .This thread has been incredibly helpful.
I understand your point about keeping tag event scripts minimal. When scripts are attached to tags, it's best to use the store-and-forward option for writing to the database (such as using system.db.runPrepUpdate or system.db.runUpdateQuery). If the script processing is expected to exceed 10ms or involves a heavy function call, we should utilize ConcurrentLinkedQueue as you've suggested. There are several considerations to keep in mind when using this approach.
I have one last high-level question related to this thread. I'm still contemplating the general approach for database design. If we're writing to the database from tag scripts and creating a standard long table with indexing on relevant columns, should we create multiple tables for different record types (e.g., SkuTable, FaultTable, WasteTable, CycleTimeTable, etc.) with a layout like:
Machine | Station | Value | Timestamp
Or should we create a single master table for all machine data, with a structure like this, where the record type is defined (e.g., SKU, fault, waste)?
Machine | Station | RecordType | Value | Timestamp
I know this is a basic question, but I'm considering long-term maintenance and the potential challenges as the tables grow and the amount of data increases.I'm also wondering if there are any pros and cons to each approach. Are there any trade-offs when joining tables and doing extensive processing at the SQL level versus using Python scripts in Ignition to manage the data?
Again thank you for your patience here I appreciate this is basic stuff.
For events that can be identified just by an ID, I like tables that have t_stamp, unit_id, and event_id only, where unit_id is unique for station+machine, and event_id means the same thing across all machines.
For any recordings that need values, without correlation to other values, consider just using the tag historian. It'll make the nice tall tables you are after.
For recordings where you need t_stamp, unit_id, and multiple values of various types, use a dedicated table.
On a system I worked on we had one table.
We created a UDT with the following tags:
runStatus
cycleCount1
goodPartCount1
SKU (product code)
FirstOutAlarm2
enabled
We logged each machine with a gateway script running on the hour and every 15 minutes as well as on status change of the "running" tag. The periodic logging meant that we could quickly get the counts for any period in multiples of 15 minutes. This worked for shift, day, week reports, etc.
The runStatus monitoring created an extra record on each transition from run to stop and stop to run. This makes it very easy to plot charts of production counts versus time and to calculate machine uptime and average rates during those uptimes.
firstOutAlarm gave us the ability to generate stop reason frequency tables and associated lost time for each fault. This is a great tool for determining where to spend effort for maximum return.
SKU monitoring allows you to see when products changed. With the addition of another table with columns MachineID, SKU and Rate you can plot where the machine should be versus actual as well as having a second metric for your OEE calculation.
goodPartCount / cycleCount gives you a quality metric (as seen by the machine).
Overall, a ton of information for little effort.
The 'enabled' (boolean) tag allows easy disabling of machines from the logs.
1 A never-resetting counter. 2 The alarm that stopped the machine
Thanks for sharing this setup—sounds like you’ve implemented a pretty efficient system!
I’m curious about one thing: when you log data using the UDT, does it create a new row in the table every time any of the values within the UDT change, or is it only when specific key tags (like runStatus) change?
Also, regarding the charts you mentioned—how exactly do you reference the table data when plotting production counts versus time or calculating machine uptime? Are there specific tools or methods you’ve found particularly effective for generating these visualizations from the data?
May be another topic post but have you stayed with igntion stock charts or used any third party modules ? Looking forward to your thoughts!
New rows are generated on each scheduled record and on each transition of runStatus. (Otherwise we'd create a record for each cycle of the machines running many parts per minute.) firstOutAlarm will only change with a runStatus changing to stop. SKU can only be changed when the machine is stopped.
We used a view or session property to hold the dataset for the machine(s) in question. A script transform on the SQL query to subtract the initial counts from all subsequent counts (so the chart would start at Y = 0). An XY Chart plotted that data and a start and added in a separate plot - basically (0, 0) and and end point based on the average rate expected for the time period. Uptime, etc., can be calculated from the same dataset without another trip to the database.
We stayed with stock. I'm always nervous about using non-stock in case it breaks with upgrade.