# Tag historian Vs transaction groups when storing data for OEE system **URL:** \ **Category:** General Discussion\ **Tags:** perspective, ignition81, database\ **Created:** [August 9, 2024, 7:31pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445 "2024-08-09T19:31:06Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![KeepItCurrent](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/keepitcurrent/32/66238_2.png) [@KeepItCurrent](https://forum.inductiveautomation.com/u/KeepItCurrent)\ **Post date:** [August 9, 2024, 7:31pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/1 "2024-08-09T19:31:06Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 10, 2024, 12:44pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/2 "2024-08-10T12:44:11Z")
Start here: > [@SQL Tall/Long vs Wide Table Structure](https://forum.inductiveautomation.com/t/sql-tall-long-vs-wide-table-structure/26231/4): > > I generally make tables wide enough to permit related values to share a row in a table with a single timestamp column. This is the classic transaction group. If multiple entities share that specific structure, I include a column to indicate which entity to minimize the proliferation of tables. That would be the block transaction group, though I don’t usually use it – I prefer to trigger individual rows as needed. Both of these approaches are extremely friendly to external data analysis, but … More discussions: [https://forum.inductiveautomation.com/search?q=db%20tall%20wide%20order%3Alatest](https://forum.inductiveautomation.com/search?q=db%20tall%20wide%20order%3Alatest) 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.) ---
**Author:** ![KeepItCurrent](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/keepitcurrent/32/66238_2.png) [@KeepItCurrent](https://forum.inductiveautomation.com/u/KeepItCurrent)\ **Post date:** [August 11, 2024, 12:26pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/3 "2024-08-11T12:26:51Z")
Thank you for your response, @pturmel 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? > [@pturmel](#): > > But don't do SQL insert/update calls directly from a tag's `valueChange` event--use the Store and Forward versions.) 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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 11, 2024, 1:23pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/4 "2024-08-11T13:23:29Z")
> [@KeepItCurrent](#): > > read further Start here: > [@Tag Change Scripts stop working](https://forum.inductiveautomation.com/t/tag-change-scripts-stop-working/84819): > > Every once in a while, we have a situation where basically all of our tag change scripts stop processing. I finally saw this error in the Console of the designer Diagnostics. It's almost as if the Basic Execution Engine has run out of threads. WARN Common.BasicExecutionEngine - Execution rejected... running in calling thread [AWT-EventQueue-0] immediately. [com.inductiveautomation.ignition.designer.tags.tree.TagTreeModelImpl$BatchOp$$Lambda$949/0x0000016597b1a668@d72dc75] Any ideas where I can… For a detailed discussion of techniques for the last bit of advice in that topic, see this: > [@Java concurrent queue for tag change scripts](https://forum.inductiveautomation.com/t/java-concurrent-queue-for-tag-change-scripts/82044): > > Continuing the discussion from [[SOLVED] Are tag Value Changed scripts running asynchronously?](https://forum.inductiveautomation.com/t/solved-are-tag-value-changed-scripts-running-asynchronously/68704/5): We've got code in our standard library that I need to log a value change on a tag and then associate various data points with that value change. The value is located in a UDT, and because this is part of our "standard" library we use for our customers, the number of tags is variable and quite possibly numerous so having a gateway tag event script isn't feasible for this solution. I was reading up on … > [@KeepItCurrent](#): > > perhaps run a duration calculation within the script 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. ---
**Author:** ![KeepItCurrent](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/keepitcurrent/32/66238_2.png) [@KeepItCurrent](https://forum.inductiveautomation.com/u/KeepItCurrent)\ **Post date:** [August 14, 2024, 8:58pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/5 "2024-08-14T20:58:28Z")
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: ```python 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)? ```python 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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 14, 2024, 9:09pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/6 "2024-08-14T21:09:54Z")
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. ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [August 14, 2024, 9:14pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/7 "2024-08-14T21:14:26Z")
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 ---
**Author:** ![KeepItCurrent](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/keepitcurrent/32/66238_2.png) [@KeepItCurrent](https://forum.inductiveautomation.com/u/KeepItCurrent)\ **Post date:** [August 14, 2024, 9:40pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/8 "2024-08-14T21:40:33Z")
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! ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [August 14, 2024, 9:58pm UTC](https://forum.inductiveautomation.com/t/tag-historian-vs-transaction-groups-when-storing-data-for-oee-system/94445/9 "2024-08-14T21:58:33Z")
1. 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. 2. 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. 3. We stayed with stock. I'm always nervous about using non-stock in case it breaks with upgrade. In retrospect, we used many script transforms of which many could probably have been done much more efficiently as expressions with [Automation Professionals' Integration Toolkit Module](https://forum.inductiveautomation.com/t/automation-professionals-integration-toolkit-module/74934).