We use Epicor as our ERP system here. We want to take tag historian data for various periods and combine it with data in our ERP system. I’m not sure how to go about doing that at all.
Is there a way to have Ignition aggregate and forward data to another database where I can use those aggregates easier than how data is stored in the ignition database?
Am I better off using tag change events to forward data directly to Epicor using an API call of some description, with the data formatted the way I need it already?
Ultimately we are trying to calculate OEE and I need half the data from Ignition and half from Epicor to calculate a final OEE percent by workcenter. Any creative thoughts are appreciated. Prayers also welcome ha ha ha!
You cannot SQL join data from two databases, but a quick reporting check (didn’t test) showed that you can use a subquery from a different db source. Subquery setups can be slow depending on much data is returned. I’m assuming the data you need from Epicor is in a database? You could also run each query separately and run some logic on the returned datasets to combine them. Overall, it is hard to come up with or determine best option without some idea with how your data is setup. Got any dummy data you could post?
I’m not looking to directly join the two together, the format of the data wouldn’t allow it if it were even technically possible to do that. I’m just trying to figure out what the best way to forward the data from Ignition to Epicor tables is or what others have done in the past to combine data from Ignition with other systems.
The real kicker is I need to get near real time reporting on OEE (within the last 12 hours) so building data cubes and exports is a possibility but it will limit how “real-time” I can get.
The data in Epicor that I need is pretty much all in the labor detail. The key fields are below with ResourceID and LaborDateTime being what I need to match in Ignition.
EmployeeID, Resource, LaborDateTime, ReportedHrs, ReportedQty
Epicor knows how many good bags were reported. Ignition knows how many total bags our machine pumped out. Epicor knows how many hours the operator said they were running, Ignition knows exactly how long the machine was running. Epicor knows the speed the machine should have been running, Ignition knows how fast the machine was running on avg.
Bold = Ignition
Italic = Epicor
OEE = (Avg Speed / Expt Speed) * (Up hours / 24 hours) * (Good bags / Total Bags)
24 hours is a variable period that I will need the avg speed from that period, the up hours from that period, total bags from that period, and good bags from that period.
From what I can tell, I would setup tags for each of your OEE variables then put them in an expression tag for the OEE calculation. Your Epicor tags in Ignition would also be expression tags using runScript() to call your API. I have never messed with Epicor, so I’m lost on that side of it.
hmm that spurs an idea. I can push data into Epicor via REST is there somewhere in Ignition that say every 5 minutes, at global level, I could push my aggregated Ignition data into Epicor via REST so I can then just do my joins in Epicor dashboards?.. hmmmm like a gateway script or something? I guess worst case I write a Web Dev that Epicor instead grabs the data every X period…
Yeah you should be able to use a timer gateway script to push your data every 5 min. That would probably be the best place for it.
2 Likes
So I believe the gateway timer script will work well. I am getting my data into Epicor!
I am now trying to figure out how to get the difference in a tag value for the last minute. I am running the script every 60 seconds and want to send to Epicor how many bags were produced in that time period. On the Epicor side that will allow me to sum those rows into a total and will make my calculations much easier in a single query.
I have tried using queryTagHistory with the MinMax and Range aggregates, but I must be misunderstanding their use. I get some goofy results when I try them with different returnSizes.
This is what I am using right now in Script Console for testing.
ds = system.dataset.toPyDataSet(system.tag.queryTagHistory(paths=['[DEFAULT-RT]PLCs/405/Status/Meter'], rangeMinutes=-1, returnSize=2, aggregationMode="MinMax"))
for row in ds:
# Now that we have a single row, we can loop through the columns just like a list.
print(row[1])
If I switch the return size to 1 it doesn’t return 2 values just one.
When I tried range I had a similar result where the return values were not what I was looking for. Ultimately I’m just looking to get if my current meter when the script runs is 10,722,325 and 1 minutes ago it was 10,722,000 I want to send 325 to Epicor.
1 Like
So… actually it looks like queryTagCalculations might give me the values I was looking for, though I still don’t understand how the Variance agg works, the 111 range seems about right.
When I compare a starting and stopping meter value I come very close to be able to sum my Range calculations but I am still 100 off. What might cause that 100 count variance.
The records that got added to this table were done on a fixed rate gateway timer script, using the following dataset
data = {
'Company': "WCI",
'Key1': "PLCLoggedData",
'Key2': res,
'Key3': "",
'Key4': "",
'Key5': str(int(time.time())),
'Date01': datetime.date.today().strftime("%Y-%m-%d"),
'Number01': seconds_since_midnight,
'Number02': system.tag.queryTagCalculations(paths=['[DEFAULT-RT]PLCs/' + res + '/Status/Meter'], calculations=['Range'], rangeMinutes=-1).getValueAt(0,1),
'Number03': system.tag.queryTagCalculations(paths=['[DEFAULT-RT]PLCs/' + res + '/Status/BPM'], calculations=['Average'], rangeMinutes=-1).getValueAt(0,1),
'CheckBox01': system.tag.read('[DEFAULT-RT]PLCs/' + res + '/Status/Machine_Status').value
}
I think that variance might be ok for what we are looking to do, but I would really like to understand better why that might come into play.
Also when there is no change in tags it dumps, in this case, the full meter value vs I would expect to see 0. Is there something I should be doing in my function setup to account for that or do I need to handle that another way?
I’m not sure why your calculations are off, but range and average seem to be the correct aggregations to use. Variance doesn’t make sense to use here, it maybe producing a close value, but not the correct aggregation.
As far as the dump without tag change, you may want to look into using a transaction group instead of a timer script.
Transaction Group Introduction - Transaction Group Introduction (inductiveuniversity.com)
1 Like
I’ll take a peek see at the Trans Groups thanks! I’m still fighting these calculations. What I don’t get is it shows the right starting and ending meter, but the range values between don’t sum the difference, gah! It’s driving me nuts!
We are getting somewhere with this all though! Epicor and PLC data combined 
granted our data collection on labor is crap 
DECLARE @RES AS VARCHAR(8) = '203';
DECLARE @HRS AS INT = 12;
WITH Report AS
(
SELECT
SUM(CASE WHEN ud.Number02 < 9999 THEN Number02 END) AS 'TotalBags',
AVG(CASE WHEN ud.CheckBox01 = 1 THEN ud.Number03 END) AS 'AvgBPM',
AVG(rs.DefaultCpmTarget_c) AS 'ExpBPM',
(SUM(CASE WHEN ud.CheckBox01 = 1 THEN 1.00 ELSE 0.00 END) / 60.00) AS 'RuntimeHr',
(
SELECT SUM(ld.LaborQty * pt.PartsPerContainer)
FROM Erp.LaborDtl ld
JOIN Erp.JobHead jh ON ld.Company = jh.Company AND ld.JobNum = jh.JobNum
JOIN Erp.Part pt ON jh.Company = pt.Company AND jh.PartNum = pt.PartNum
WHERE ud.Company = ld.Company AND ud.Key2 = ld.ResourceID AND ld.LaborType = 'P' AND DATEADD(MINUTE, ld.ClockInMInute, '10/30/1953') > DATEADD(HOUR, (@HRS * -1), GETDATE())
) AS 'GoodProd'
FROM
Ice.UD02 ud
JOIN
dbo.Resource rs ON ud.Company = rs.Company AND ud.Key2 = rs.ResourceID
WHERE
DATEADD(SECOND, ud.Number01, CAST(ud.Date01 AS DATETIME)) > DATEADD(HOUR, (@HRS * -1), GETDATE()) AND
ud.Key1 = 'PLCLoggedData' AND
ud.Key2 = @RES
GROUP BY
ud.Company,
ud.Key1,
ud.Key2
)
SELECT
@RES AS 'Resource',
TotalBags,
AvgBPM,
ExpBPM,
RuntimeHr,
GoodProd,
(AvgBPM / ExpBPM) * 100.00 AS 'SPEED_OEE',
(RuntimeHr / @HRS) * 100.00 AS 'RUNTIME_OEE',
(GoodProd / TotalBags) * 100.00 AS 'QUALITY_OEE',
(AvgBPM / ExpBPM) * (RuntimeHr / @HRS) * (GoodProd / TotalBags) AS 'OEE'
FROM Report;
0 good bags reported in 12962 metered LOL oy
1 Like
