11 Replies Latest reply on Mar 17, 2016 3:32 PM by Roger Palmen

    PI OLEDB Event Frame Snapshot Table Question

    vsinyuk

      Hello,

       

      I have Event Frames created with multiple attributes, and I am using PI OLEDB to query values from the Event Frames by joining on the EventFrameSnapshot table. The attributes on the Event Frames are formulas that point to the original attributes on their respective elements.My question is about what sort of snapshot values are stored in this table. Does the EventFrameSnapshot table store the current snapshot values from the original attributes that the Event Frame attributes are point to or does the EventFrameSnapshot table store the values of the Event Frame Attributes when the Event Frame was triggered? What I am noticing is that it looks like the EventFrameSnapshot table stores the current snapshot values for open Event Frames but for closed Event Frames it seems like it stores the value when the Event Frame was closed and not the current value in AF. I was wondering if I can get some clarification on this?

       

      If indeed the EventFrameSnapshot table does not offer current values when the Event Frame has closed, what would be the best way to calculate an event-frame formula attribute against current, live values with PI-OLEDB?

       

      Thank you.

       

       

       

      -Vitaliy Sinyuk

        • Re: PI OLEDB Event Frame Snapshot Table Question
          bshang

          Does the EventFrameSnapshot table store the current snapshot values from the original attributes that the Event Frame attributes are point to or does the EventFrameSnapshot table store the values of the Event Frame Attributes when the Event Frame was triggered?

           

          The value returned for an Event Frame attribute will "by default" be at the end time of the Event Frame.

          More details are here

          https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v5/GUID-C17CF5B6-8146-4E1D-A620-528575C16042

           

          If the attribute belongs to an AFElement, you can query via the AFElement (Asset.Data.Snapshot) in OLEDB instead of AFEventFrame. Semantically, in AF, it does not make sense to ask for the current value of an attribute via a closed event frame.

          • Re: PI OLEDB Event Frame Snapshot Table Question
            Roger Palmen

            I agree that this behaviour is not always what we need. See this discussion for some more issues arising from this: How does EventFrame value capture work?

            If you need data from the primary referenced element, irrespective of the value capture, you need to get data from the primary referenced element's attributes. So not a join from EventFrame to EventFrameAttributes, but from EventFrame to Element to ElementAttributes.

              • Re: PI OLEDB Event Frame Snapshot Table Question
                vsinyuk

                Hey Roger,

                Thank you for the suggestions. I your suggestion of doing a join from EventFrame to Element to ElementAttributes, but it seems to be querying way to much data and times out or occasionally throws an out of memory message. To give you a bigger picture of what we have is about individual 195 event frame templates (inherited) with about 16 attributes each and about 209 parent elements with at least 3 child elements each. I am doing an internal transpose in my query using case statements to be able to produce the correct columns with their respective values from the EventFrameSnapshot table. At this point there is only one column that needs to be dynamic (meaning shows us the current value at the time of rendering an ssrs report that loads up the event frames). However from the Elements level these attributes all have different names (about 160 unique ones).  My intention was to try to substitute the EventFrameAttributes for those particular attributes with the ones from the Element.

                  • Re: PI OLEDB Event Frame Snapshot Table Question
                    Roger Palmen

                    In theory this should not amount to much more data than querying EventFrame attributes, but OLEDB can be quite a headache when it comes to performance. The PI Integrator for BI should improve on this, but not sure if that is an option for you to use.

                    Any way, this is the only way i know of to work around the value capture within OLEDB solely. Of course you could / should move outside OLEDB to assemble the dataset you need. I typically use simpler queries on OLEDB, and use SQLserver stored procedures to assemble the more complex datasets from those.

                      • Re: PI OLEDB Event Frame Snapshot Table Question
                        vsinyuk

                        Hey Roger,

                        In reading the White Paper on PI OLEDB Enterprise SQL Optimization, OSI recommends joining on the ElementTemplateAttribute Table. However when I combine this with the Snapshot table and try to bring back the snapshot value in the select statement,  instead of giving me the value for the specific attribute for each element, what happens is that it gives me an all attribute values for each element in my database for each individual element. So I have 209 elements in my database, it bring back 209 instances of a specific attribute instead of 1 per element. I'm wondering if there is a way to only get the current snapshot value of each unique attribute in each unique element. I tried putting a distinct into the select statement, but then it bring me back each type of value that the attribute has had instead of the current one. This is what my query looks like.

                         

                        Select Distinct(el.Name) As Element, eta.Name as Attribute, s.ValueStr as Status

                         

                         

                        From [GasMeasurement].[Asset].[ElementTemplate] et

                         

                        Inner join [GasMeasurement].[Asset].[ElementTemplateAttribute] eta on eta.ElementTemplateID = et.ID

                         

                        inner join [GasMeasurement].[Asset].[Element] el on el.ElementTemplateID = et.ID

                         

                        inner join [GasMeasurement].[Data].[Snapshot] s on s.ElementTemplateAttributeID = eta.ID

                         

                         

                         

                        Where et.Name = 'Location_Base' And eta.Name Like 'MethaneResponseFactorStatus'

                         

                         

                        And this is a result for one of my elements: As you can see instead of giving me a single current value it gives me four different values (because of the distinct, otherwise it would give me 209 of them) because at one point that attribute has been one of these 4 values.

                         

                        ElementAttributeAttribute
                        D357EMethaneResponseFactorStatusN/A
                        D357EMethaneResponseFactorStatusGood
                        D357EMethaneResponseFactorStatusBad Inputs
                        D357EMethaneResponseFactorStatusAlarm
                          • Re: PI OLEDB Event Frame Snapshot Table Question
                            Roger Palmen

                            Your query returns a row for each combination of ElementTemplate, ElementTemplateAttribute and Element. Maybe the performance is better, but this is not what you need... Drop the Element table from the query and you see you do get results where you expect none. Just join Element(-hierarchy) to Attribute to Snapshot.

                             

                            I also don't understand the query in the whitepaper, nor the use of the ElementTemplateAttributeID in the snapshot table as there is no way to link back to an element from neither the snapshot nor the template tables. If you override the attribute in the element, you're into trouble. If you use inheritance, and/or override in inheritance, you're into more trouble...

                            Or if i missed something here, further explanation much welcome!

                             

                            PS: My teacher always told me that the use of a distinct would almost guarantee a fail on my exam in failing to build a better query...

                    • Re: PI OLEDB Event Frame Snapshot Table Question
                      asorokina

                      Hi Vitaliy,

                       

                      Can you tell us more about your use case and what you're trying to accomplish? Why would you like the current values of the EF attributes? And what led you to use the EF for that, versus querying the element?

                        • Re: PI OLEDB Event Frame Snapshot Table Question
                          vsinyuk

                          Hi Anna,

                           

                          Basically I am trying to load up these event frames (with the attributes) into an ssrs report which is supposed to act as a summary of sorts instead of using notifications. One of the attributes on the event frame template is a status attribute that points to an attribute on the element level. When the users load up the report with the event frames that triggered during a specific time, they would like to see the current status value of the attribute and not the status value the attribute had when the event frame closed. So the dilemma is trying to figure out an efficient way to be able to query data from an event frame and from the element (to get the current value of the status attribute when the user loads the report).

                        • Re: PI OLEDB Event Frame Snapshot Table Question
                          JimGavigan

                          I am now wondering more of what really is the difference between the difference between all of the transpose functions I have created in OLEDB Ent as well. I have been using the event frame snapshot one as well. I have several use cases, but one is below.

                           

                          I am trying to understand what a motor overcurrent event looks like. So, I am bringing these values from the transpose function and using the one called "eventframesnapshot." I might want to know:

                           

                          Maximum current over the EF

                          Average current over the EF

                          Other machine or process parameters over the EF (max/min/average, etc.)

                           

                          I just looked at one EF (slightly different use case, but really just messing around) from my test environment and looked at the predefined queries for all 4 data transpose functions and the answers were all similar, but some are slightly different. Which one do I believe?

                           

                          Here they all are:

                           

                          Archive.PNG

                          Discrete.PNG

                          Interpolate Range.PNG

                          Snapshot.PNG

                           

                          I would like to better understand each of these as well. I have had some issues trying to get reasonable data from some of the EF's. I was looking at a tank fill event that had a VFD driven screw feeder. I wanted to try to get the two speeds that they used into a table (a "fast" and a "slow" speed  - slow speed would generally run until just before the EF closed) and never could quite get it to give me reasonable data. Fortunately, you could easily get it from the Coresight link I built in the table and see the values there, but would have been nice to get it into the table.

                           

                          I would say in general, good descriptions of what the different calculation modes are on data references of event frames would be helpful as well as I really haven't found anything that clearly explains (even with pictures/examples) of each of these:

                           

                           

                          I have looked for this documentation and really haven't found it, so if I am missing something, please let me know. Vitaly - is this what you are after as well?

                          1 of 1 people found this helpful