We are currently in talks of the best way to revamp our system (will require a phonecall to tech support as it’s a bit too complex/lengthy to describe here). This system was currently in place when I started working here, and we migrated from FPMI/FSQL since then, so a lot of our methodologies were already established before I got here.
I am currently reading through the documentation in-depth to see if there’s any neat features/improvements that can be made when we’re in the process of revamping. One of the things that I came across was the SQLTags historian option.
Currently, to store history on items, we create a new transaction group, and drag the tags we want to store history for into it from the OPC browser and we’re good to go. I read through the SQLTags historian/transaction group documentation a few times, and I see how they both work, but I can’t figure out in which situation it would be best to use one over the other. What are the advantages/disadvantages of each?
That is a good question. There are significant differences between SQLTags historian and transaction groups.
SQLTags Historian Pros
Data is only logged when the value changes, by exception according to a deadbad
Data is compressed using a built-in compression algorithm. An average we see around 54% compression.
Data is partitioned into separate tables by month to keep the size of the tables smaller for faster retrieval.
Minimal database maintenance
Fast retrieval times for large amounts of data
Easy to graph and view in tables
SQLTags Historian Cons
Not in control of the database schema. The data is in an open format but Ignition decides the schema.
Not very easy to query yourself because of the partitions and compression.
Have to use Ignition “Tag History” binding or scripting function to retrieve data.
Transaction Group Pros
You are in control of the database schema. You can put data into any format that you choose.
Easy to query yourself.
Can use the SQL language for more powerful analysis (of course you have to know the SQL language)
Has the ability to delete records older than a certain period of time
Groups can log data based on a schedule and by exception
Easy to graph and view in tables
Can use more components such as bar charts and pie charts since you are in control of querying the data
Transaction Groups Cons
Data is stored in a single table so you have to make sure the table doesn’t grow forever or performance will be slow
You don’t get compression or data partitioning
Bigger database size
You can only use around 100 tags per group
You have to know more about databases and SQL
As you can see transaction groups are used when the user wants to control the database and query the data any way they want. These people usually know more about SQL. The historian is great for beginner users since they don’t have to know much about SQL. However they are limited in how they can retrieve the data. We are constantly adding new features to our historian to make it more powerful in retrieving data.
You have to way the pros and cons to see which one really fits your needs. Hopefully this helps out.
Thank you Travis, that sums it up pretty nicely. I hadn’t considered that SQL queries would be more difficult since things aren’t named by tag, so it looks like continuing to use Transaction Groups will be the way to go for us.
We have a number of PHP scripts which query the transaction group tables for things such as the duration that our generators were running, capacity analysis, etc., so that would be a bit more difficult to maintain for us otherwise.
You might want to consider using both. I use the SLQTag Historian just for trends and then use Transaction Groups for special data (counters, runtimes, 24hr running averages).
I love the historian as many features and functions are built in for reporting on them.
I also use the transaction groups when I need event driven logic and special read/write functions based events and values of multiple items. But I have trouble using built in functions
It must be noted that I am still relatively new to this software and I know there is a lot I have to learn. My observation is that both are very useful. Use both.