Transaction Groups

Ignition
1

Hi,
I’m evaluating Ignition SQL Bridge at the moment with a view to replacing a Wonderware Historian

A simplified version of the application is as follows:

Database: MSSQL Server 2008

2 Tables: tblBatchData and tblSeqTemperature

tblBatchData: PK/BatchNumber/StartDateTime/EndDateTime
tblSeqTemperature: PK/BatchPK/SeqNumber/Temp1Min/Temp1Max/Temp1Avg/Temp2Min/Temp2Max/Temp2Avg.

When a batch starts I want to log Start Date/Time; Batch Number; + other relevant data to tblBatchData.
As the batch goes through various sequences I need to log the Min/Max/Average Temperature during each sequence to tblSeqTemperature. (Along with Sequence Start/End date/time and the primary key of the batch from tblBatchData.

The temperatures are logged using SQL Bridge and I’m forcing the Historical scanclass to log every 5 cycles (Scan = 1 second).

I want to do the following:
Batch Start: Log Batch data
Seq End:
1-Get Batch PK.
2-If Batch does not exist (in tblBatchData) (for whatever reason), create batch and return the PK.
3-Log BatchPK and Seq Start/End DateTime to tblSeqTemperature.
4-Using Seq Start/End DateTime, retrieve Min/Max/Avg of each temperature from the history tables.
5-Log Temperature Data to tblSeqTemperature.

I hope I’ve explained this OK.
Is this possible with SQL Bridge?
What’s the best method to implement this.

Thanks for any help,
Tim

2

Anybody?

3

Hi,

Honestly, you might find this easier to do with tag event scripts than with transaction groups. If you were to do it with transaction groups, I can’t really think of a way that would be compatible with store and forward, since the groups would likely need to perform select queries to get the current batch number. The main suggestion I might have would be to store the data for a sequence also through a transaction group, and not tag history, in order to make it a bit easier to snapshot.

Here’s what I mean:

  • A “sequence_history” group that logs SequenceNumber, Temp1, Temp2, etc., “insert” mode, 5 second rate.
  • A tag change event on batch number that does:
    – system.db.runUpdateQuery(“update tblBatchData set enddatetime=current_timestamp where enddatetime is null”)
    – system.db.runPrepUpdate(“insert into tblBatchData(BatchNumber, StartDateTime) values(?, current_timestamp)”, newValue.value)
  • A tag change event on sequence number that more or less does the following:
    – Select the max batch id with null end time.
    – Run something like:
batchId = int(system.db.runScalarQuery("SELECT BatchNumber FROM tblBatchData WHERE EndDateTime IS null"))
seqId = int(newValue.value)-1
system.db.runUpdateQuery("INSERT INTO tblSeqTemperature(BatchPK, SeqNumber, Temp1Min, Temp1Max, Temp1Avg...) SELECT %d, %d, min(Temp1), max(Temp1), Avg(Temp1) FROM sequence_history WHERE sequence_id=%d GROUP BY sequence_id" % (batchId, seqId, seqId))
system.db.runPrepUpdate("DELETE FROM sequence_history WHERE sequence_id=?", seqId)

You could probably use a transaction group, with a bunch of query tags, but I think it would be more work than setting up the scripts I’ve outlined above.

Good luck,

4

Hi Colby,
Thanks for the response.

I think I understand your suggestion. I’m going to try a few scripts to evaluate.
I still need to solve the problem of non-existing batch data.
I assume using the tag change script to check and create would be more efficient.

Re Data logging:
The main reason I’m using the standard history to log the temperatures is that I also need to query these for other reports that may not be related to an actual sequence.
(Trends/comparing of batches/etc.)

Another requirement is to query the tables from an external application (mainly ssrs).
A critical part of this is the ability to Query data across the Ignition partitions.

Is there any documentation detailing this procedure?

Also, regarding the t_stamp in the history tables: I understand that this is a Unix format?
I’m using DATEADD(s,t_stamp/1000,‘1970-01-01 00:00:00’) to convert to MSSQL.
How do I incorporate a timezone with Daylight savings?
Or is there some setting in Ignition to set the timestamp to use daylight savings?

Thanks again,
Tim

5

[quote]Re Data logging:
The main reason I’m using the standard history to log the temperatures is that I also need to query these for other reports that may not be related to an actual sequence.
(Trends/comparing of batches/etc.)

Another requirement is to query the tables from an external application (mainly ssrs).
A critical part of this is the ability to Query data across the Ignition partitions.[/quote]

I just want to clarify, by “standard history”, you mean transaction groups, right? Because to me, these sound like exactly the reasons to not use tag history. The tag history system is supposed to make it easy to store and use tag data inside of Ignition, but it’s not great for sharing data with other systems. Storing data with transaction groups means you have to query it with DB Pens (on the Easy Chart) or use the Standard Chart for charting purposes, but otherwise makes it easier to share data and do some types of analysis… especially when the data is related and part of batch type system.

Regards,

6

Err… Actually, no.
I mean tag history.

I don’t understand the difficulty with querying the tables from an external application.
I’ve had no problems setting up views and getting the data, other than running queries across partitions.

My current solution to this is to recreate the tables in SQLServer myself with partitions and turn-off partitioning in Ignition.

Maybe I’m underestimating the transaction groups.
The complete system will have approx. 800 analog I/O to log.
It seems (to me) simpler to enable tag history for the tags.

Am I wrong with this approach?
And/or do you have a stored procedure I can use for external querying of Ignition created partitions?

Thanks,
Tim

7

We are interested in the same thing. Like you, we have way too many tags to use transaction groups to store the data, plus we are already storing it in the sqltags history, so it doesnt make sense to duplicate the data in the database.

We havent had the time to figure out how to query across the partitions yet, but we will eventually. But of course it would be very nice of the IA team to create something for us and the many others that have asked for this feature. :thumb_left: hinthint

8

Hi,

Don’t get me wrong, I mean, the data is in the DB, so if you can query it satisfactorily, then go for it. It’s just that it is a bit more difficult, both because you have to join the sqlth_te table in (there can be multiple ids for a single path, so you have to join), and because you have to go across multiple partitions (unless you turn partitioning off, which you probably should in your case).

It also becomes difficult to do any kind of relational analysis, like “what was the average value during this batch” (probably why you’re trying to calculate it once, at the end… beside performance, of course).

We don’t currently have stored procedures, though they’ve been requested (heh, diat posted while I was writing). I think if you simply turn off partitioning you’ll probably have a much easier time. If you’re using sql server or mysql, the clustered indexes created by the system make our default partitioning fairly unnecessary.

Regards,

9

OK, I have to ask, How does Ignition query across the partitions? 8)

Also, why do you think it will be difficult to do relational analysis on the tables?

Tim

10

Well, we just query each table. We’re going through the raw data anyhow in order to perform the aggregation/interpolation dictated by the query parameters anyhow, so it’s not really a big deal.

What I mean by “harder” really depends on what you’re trying to do, I suppose, and how the data could be stored. Let’s say you have a variety of values that are relevant to a particular step in a batch, or a certain part. In a transaction group, you could just stored them all together, along with additional info columns for the step number or part number. Then you could just directly query on that. In tag history… you’d have to join the _te table to the data table in order to look up by tag name, and then you have to reference some other table for the start and end time of the step, etc. Not impossible, just more difficult. But really, there are usually a variety of factors that help decide which is better. Or store it in both. You might find that you want the values for archived records for a long time in tag history, but you only need the table-style analysis for a few months. You can just set up the group to prune automatically.

Regards,