As previously discussed, the Coral8 Read from Database Adapter is best suited for replay of historical records, or for testing algorithms against existing data.
For this article, I will create a program to monitor a measurement stream and raise alarms based on a statistical index. The adapter will be used to read existing data from one of my databases and test the program. I have some real-life measurements generated during assembly of car parts. Among other columns, the table of measurements includes:
- Record Time — time-stamp;
- Serial Number — unique part id;
- Piston Press Force — a measurement.
Here is a SQL snippet:
CREATE TABLE dbo.PartData(
RecordTime datetime
,SerialNumber varchar(13)
,PistonPressForce decimal(8,3)
-- more columns here
);
This will do for the example, now the to-do list:
- Create an input stream.
- Attach the read-database adapter to the stream.
- Test the connection to the DB.
- Create a stream for statistical data (average, sigma, Cpk).
- Create a query to calculate statistics over a window of data rows.
- Test the statistics.
- Create an output stream for alarm messages.
- Create a query to raise alarm messages.
- Test alarms.
1. The Input Stream
The schema of the input stream matches database columns. Note the column-type mappings between SQL Server and Coral8. Stream fields have same names as database columns — this is necessary for the adapter type.
-- Input stream
CREATE INPUT STREAM StreamIn
SCHEMA (
RecordTime TIMESTAMP
,SerialNumber STRING
,PistonPressForce FLOAT
);
2. The Adapter
As in the previous post, I have used point-and-click to insert the adapter; check out the adapter form.
-- Read from DB adapter
ATTACH INPUT ADAPTER ReadFromDB
TYPE ReadFromDatabaseAdapterType
TO STREAM StreamIn
PROPERTIES
DBNAME = "RCI4122_1"
,TABLE = "PartData"
,TIMESTAMPCOLUMN = "RecordTime"
,RATE = "1"
,WHERECLAUSE =
"PistonPressForce > 0"
,TIMESTAMPCOLUMNINITVALUE =
"2008-01-01"
;
The adapter parameters:
Field |
Comment |
DB Connection Name |
Name of the database connection as specified in the coral8-services.xml file. For more details see the previous post. |
Table or View |
Name of the database table. |
Where Clause |
The expression in the WHERE clause of the query. |
Loop Count |
How many times to loop through the data. |
Rate |
Number of reads per second. |
Timestamp column |
Name of the time-stamp column in the database. |
Timestamp column initial value |
Initial value for the time-stamp. In the example above, the adapter reads all records since New Year 2008. |
The adapter example is an equivalent of running this SQL query:
USE RCI4122_1;
SELECT RecordTime, SerialNumber, PistonPressForce
FROM PartData
WHERE RecordTime > '2008-01-01'
AND PistonPressForce > 0
ORDER BY RecordTime;
3. Test the Connection to the DB
Looks OK.
4. Stream for Statistical Data
I would like to calculate the following statistics for the measurement:
- average,
- sigma (standard deviation) ,
- Cpk — process capability index — is a distance from the average to the nearest specification limit measured in three-sigma units.
The stream definition:
-- Local stream for stats
CREATE LOCAL STREAM StreamStats
SCHEMA (
RecordTime TIMESTAMP
,p_Avg FLOAT
,p_Std FLOAT
,p_CpkUp FLOAT
,p_CpkLo FLOAT
);
The Cpk is broken into the p_CpkUp
and the p_CpkLo
. The p_CpkUp
is the distance from the average to the upper limit, and the p_CpkLo
is the distance to the lower limit; distances are in three-sigma units.
5. Query to Calculate Statistics
Process limits could be read from a database or a file, but using pre-defined variables is simpler.
-- Lower and Upper limits
CREATE VARIABLE FLOAT p_LSL = 2.485;
CREATE VARIABLE FLOAT p_USL = 2.515;
The query creates a sliding window of last 35 rows and inserts calculated statistics into StreamStats. The 35 is frequently used as a minimum sample size for this type of statistics to make sense. The query updates after each new row of data (new part) and calculates statistics over the last 35 parts.
-- Calculate stats
INSERT INTO StreamStats
SELECT
max(StreamIn.RecordTime)
,avg(StreamIn.PistonPressForce)
,STDDEVIATION(StreamIn.PistonPressForce)
,(p_USL - avg(StreamIn.PistonPressForce))/
(3 * STDDEVIATION(StreamIn.PistonPressForce))
,(avg(StreamIn.PistonPressForce) – p_LSL)/
(3 * STDDEVIATION(StreamIn.PistonPressForce))
FROM StreamIn
KEEP 35 ROWS
;
6. Test the Statistics
This works nice, now I will raise an alarm when the Cpk drops below 1.33 — when the distance between the average and a limit is less than four sigma.
7. Output Stream for Alarm Messages
The output stream has: an alarm message, a time-stamp and the value which raised the alarm.
-- Output stream for Cpk alarm
CREATE OUTPUT STREAM StreamOut
SCHEMA (
RecordTime TIMESTAMP
,p_Cpk FLOAT
,AlmMsg STRING
);
8. Query to Raise Alarms
Selects all rows from StreamStats with the Cpk below 1.33 and inserts the time-stamp, the smaller Cpk value and the alarm message “Low Cpk” into StreamOut.
-- Monitor Cpk and raise alarm
INSERT INTO StreamOut
SELECT RecordTime, min(p_CpkUp, p_CpkLo), 'Low Cpk'
FROM StreamStats
WHERE p_CpkUp < 1.33
OR p_CpkLo < 1.33
;
9. Test Alarms
The final test shows alarm messages in the output stream, generated when the Cpk drops below 1.33.
The next step would be to attach an output adapter to this stream to log alarms and send notifications. The code still needs some optimization; once that is done the input adapter can be replaced by a live-measurement feed to generate alarms in real-time.