How can I easily create event frames on a minute and hour interval. Meaning the close of one event is the start of another event. In other words the data gets rolled up into buckets of a minute and an hour.
Do you mean your eventframes have a duration of 01:01:00 (HH:MM:SS)? Or do you need tw eventframes: one minute and one hour in duration?
In any case, this thread provides an excellent approach for that: Run Event Frame for only 24 hours
BUT i do want to question why you would need 1 minute eventframes. That produces quite some EventFrames, so curious how you intend to use those. There are other ways to rollup data than using EventFrames, and this gives me the sense that EventFrames might not be the way to go.
So I am a bit new to Asset Framework (Event Frames). We have a customer that requested that the data needs to be rolled up into minute buckets as well as to hour buckets (Two Event Frames). This needs to be done for about 2000 tags. This data then needs to be send to a MSSQL database table on a minute interval where some 3rd party system will access the data and do some calculations. I though event frames would be the easiest way to do it, because then you don’t need to do any calculation on the SQL side. If there is another way that you would recommend, please let me know what you would suggest? Thanks!
Jaco Rheeders (Pr.Eng)
Senior Project Engineer
Systems and Solutions Business – South Africa
Rockwell Automation<http://www.rockwellautomation.com/> | 369 Pretoria Avenue, Ferndale, Randburg
M: firstname.lastname@example.org<mailto:email@example.com> | T: +27 (0)11 998 1000 | M: +27 (0)83 444 8783 |
Connect with us on
<http://www.rockwellautomation.com/en_ZA/news/blog/overview.page?> <https://www.linkedin.com/company/rockwell-automation> <https://twitter.com/ROKAutomationZA> <https://www.facebook.com/ROKAutomation> <https://plus.google.com/+RockwellautomationInc> <https://www.youtube.com/ROKAutomation>
SIGN UP to our monthly newsletter to get the latest news
on industry trends & technologies, products, services and
events from us and our partners.
Subscribe<https://map.rockwellautomation.com/subscribe?reg=EMEA&lang=en> | Latest issues<http://www.rockwellautomation.com/en_ZA/news-innovation/newsletters-magazines/overview.page?>
EventFrames are typically used for, well, Events. Like a downtime. This rollup you're looking for is probably a 1-minute and 1-hour time-weighted average value of a set of PI Points, right?
It depends on how you want to get the data out to the MSSQL table. The PI Integrator for BA is built to deliver exactly that sort of data, directly into MSSQL, so if that is available in your environment, than that's the way to go. But that's a separately licensed piece of software.
Also separately licensed, but more often covered in a typical suite is the PI System Access license, that includes the use of PI OLEDB Enterprise / PI ODBC. With ODBC / OLEDB you can request interpolated data at a specified interval.
The PI Integrator for BA, can we use it with our PI system. Meaning I am using the Rockwell Historian which is OSI PI (Rockwell is a business partner with OSI PI). Can we purchase the PI Integrator for BA and will it work with the Rockwell version where OSI PI has been embedded within our Rockwell platform?
I agree with Roger Palmen. I think using the PI Integrator or PI OLEDB Enterprise makes more sense. Another alternative is to simply configure expression analyses that output to PI Points and then use the PI Interface for RDBMS to push this data to SQL.
2000 events every minute is 1 051 200 000 events per year. Currently AF scales to approximately 100 000 000 event frames. Of course this is an approximation as it depends on how much data is contained in each event frame.
I did consider the other option of writing the data to a PI point and then via RDBMS to MSSQL. But am I correct in saying that will take up another tag count license? Because I prefer using standard product features then going with a custom MSSQL solution via OLEDB, but if I don’t have any other option then it is what it is.
There are plenty of options to get this done but it all comes down the option that best suits you with the resources you have in hand. While, using PI Integrator for BA will require additional license, it will provides you most easiest way for integration, since all configuration is done via User Interface. You might have to organize your assets in Asset Framework to make maximum use of this.
Also, storing results to PI Points and then using PI RDBMS Interface will cost you additional PI Points (4000 for 2000 tags).
So if you want to avoid both the above and if you are good at SQL, the PI OLEDB Enterprise is the way to go. You can use AF Attributes to store the results and query this using SQL queries and store the data in SQL Database. We have similar setup, but we query data once a day, to get aggregated values (calculated using PI Analytics) to SQL Database. Below link explains how to setup a Linked Server for this purpose.
KB01143 - How to configure a linked server with PI OLEDB or PI OLEDB Enterprise
Sample query that does the job. "PIAF" is the name of my Linked Server.
DECLARE @timestamp DATETIME -- input Date Time
DECLARE @sql VARCHAR(MAX) = '
CAST('''+CONVERT(NVARCHAR, @timestamp, 121)+''' AS DateTime) AS [ValueTimestamp]
, e.Name AS [Equipment]
, ea.Name AS [Attribute]
, id.Value AS [Value]
INNER JOIN [TestAFDB].[Asset].[Element] e ON et.ID = e.ElementTemplateID
INNER JOIN [TestAFDB].[Asset].[ElementHierarchy] eh ON e.ID = eh.ElementID
INNER JOIN [TestAFDB].[Asset].[Element] eParent ON eParent.ID = eh.ParentElementID
INNER JOIN [TestAFDB].[Asset].[ElementAttribute] ea on e.id = ea.ElementID
INNER JOIN [TestAFDB].[Data].[ft_InterpolateDiscrete] id on id.ElementAttributeID = ea.id
eh.Path = ''\Site\Facilities\Equipment\''
AND ea.name IN (''Daily Average'',''Site'',''Location'')
AND id.time = DATE('''+CONVERT(NVARCHAR, @timestamp, 121)+''')
OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
DECLARE @result Table(ValueTimestamp datetime, Equipment varchar(250),[Attribute] varchar(500), [Value] VARCHAR(250))
INSERT INTO @result
EXEC(@sql) AT PIAF
INSERT INTO <ThirdPartyDBTable>
,CASE WHEN [Location]='No Data' THEN NULL ELSE [Location] END AS [Location]
,CASE WHEN [Site] = 'No Data' THEN NULL ELSE [Site] END AS [Site]
,TRY_CONVERT(Float, [Daily Average]) as [DailyAverage]
PIVOT (Max(Value) FOR Attribute IN([Location],[Site],[Daily Average])) piv
Yes it would increase your tag count. To avoid a custom scripted solution, the PI Integrator for BA is your best option.
The Integrator requires PI Data Archive 2010 and AF Server 2015 or later (ref:https://techsupport.osisoft.com/Downloads/File/2626afc1-39e7-41d7-9e73-f821d8b14659 )
I would assume that it works with the PI Data Archive sold by Rockwell, but please double check with them if possible. I know their licensing mechanism is different than ours.
Yes, i think it might technically be no problem, but licensing terms can be quite different for OEM versions of PI. So be sure to check. But in my experience, OSIsoft is always willing to help, so there's always a way to find a solution.
I think if your queries are limited to just a few, then using OLEDB Enterprise is sufficient. It's a bit of a technical solution, but perfectly doable. I would create a view in PI OLEDB Enterprise, and then setup a Datapump in SSIS to pull the data into a table, scheduled to run every x minutes. So it's a bit of handywork within SQLserver, but nothing special.
If you expect more of these questions of the next year, then the integrator will make life much easier to build and maintain these data dumps. At that point the license cost of the integrator will pay off in a much easier implementation costs.
So which one suits you best... it depends!
Retrieving data ...