2 Replies Latest reply on Mar 8, 2018 9:18 AM by Paurav Joshi

    Get data of EventFrame Attributes from OLEDB Enterprise

    Paurav Joshi

      Hi Team,

       

      We are trying to get attribute values of eventframe using SQL commander, seems straightforward but got stuck.

       

      There are 14 attributes of EF and among them, 13 are PI tags and 1 is AF attribute. Out of 13 PI tags, 11 has been configured as below with relative time -1s, one with +1s and left one is @ Start Time in TimeRange:

       

      How should we create a query in SQL commander that will pull out EF with all attribute values?

       

      I have tried as follows and yet to find success:

       

      Try 1:

      SELECT ef.Name,ef.StartTime,efa.Name,ea.Value,ea.Time,ef.endTime
      FROM 
      (
      -- Get EventFrame
      SELECT *
      FROM [TEST].[EventFrame].[EventFrame] 
      WHERE Name = '<eventframe_name>'
      ) ef
      -- Get Attributes of EF by comapring ID
      INNER JOIN [TEST].[EventFrame].[EventFrameAttribute] efa ON efa.EventFrameID = ef.ID
      INNER JOIN [TEST].[Data].[EventFrameArchive] ea ON ea.EventFrameAttributeID = efa.ID
      WHERE ea.Time BETWEEN ef.StartTime-time('10s') AND ef.EndTime
      OPTION (FORCE ORDER, EMBED ERRORS)
      
      
      

       

      O/P 1:

      2 attributes of EF - an AF attribute and PI Tag @ StartTime

      Ideally it should give all 14 attributes values.

       

      Try 2:

      used modified version of Querying event frame related AF attribute historical data with time offset

      SELECT ea.Name attribute, d.Value value, d.Time time
                  FROM [TEST].[EventFrame].[EventFrame] ef
                  INNER JOIN [TEST].[Asset].[Element] el ON el.ID = ef.PrimaryReferencedElementID
                  INNER JOIN [TEST].[Asset].[ElementAttribute] ea ON ea.ElementID = el.ID
                  INNER JOIN [TEST].[Data].[Archive] d ON d.ElementAttributeID = ea.ID
                  WHERE ef.Name ='<EventFrame_Name>' AND 
                  d.Time BETWEEN ef.StartTime-time('1s') and ef.StartTime+time('1s') 
      ORDER BY ea.Name
                  OPTION (FORCE ORDER, EMBED ERRORS)
      

       

      O/P 2:

      Giving partly element attributes which are in EF and which are not both.

       

      Roger Palmen seems you have worked lot on EF in 2016-17. If you can guide where something is wrong will be helpful .

       

      Thanks,

      Paurav Joshi

        • Re: Get data of EventFrame Attributes from OLEDB Enterprise
          vkaufmann

          The easiest way is to create an EventFrame Snapshot Transpose function. It's not clear if you're looking for only the reported value on the EF or all the archive data that was stored during the duration of the event frame. This example accomplishes the former.

           

          Once this is created, you can either leverage the function itself or use the corresponding virtual data table it creates. You can find this feature documented here.

           

          This is the default query associated with the transpose function.

           

          SELECT ef.Name EventFrame, ts.*
          FROM
          (
              -- Get first 100 event frames derived from Value template
              SELECT TOP 100 *
              FROM [More Event Frames].[EventFrame].[EventFrame]
              WHERE EventFrameTemplateID IN
              (
                  SELECT ID
                  FROM [More Event Frames].[EventFrame].[EventFrameTemplate]
                  WHERE Name = N'Value'
              )
          ) ef
          INNER JOIN [More Event Frames].[EventFrame].[EventFrameHierarchy] efh
              ON efh.ID = ef.ID
          CROSS APPLY [More Event Frames].[DataT].[TransposeEventFrameSnapshot_Value]
          (
              ef.ID
          ) ts
          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
          

           

          The equivalent query using the aforementioned data table.

           

          SELECT efh.Path + efh.Name EventFrame, ts.*
          FROM
          (
              -- Get first 100 event frames derived from Value template
              SELECT TOP 100 *
              FROM [More Event Frames].[EventFrame].[EventFrame]
              WHERE EventFrameTemplateID IN
              (
                  SELECT ID
                  FROM [More Event Frames].[EventFrame].[EventFrameTemplate]
                  WHERE Name = N'Value'
              )
          ) ef
          INNER JOIN [More Event Frames].[EventFrame].[EventFrameHierarchy] efh
              ON efh.ID = ef.ID
          INNER JOIN [More Event Frames].[DataT].[ft_TransposeEventFrameSnapshot_Value] ts
              ON ts.EventFrameID = ef.ID
          OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)
          

           

          The output will look similar to:

           

           

          Each column in this query will correspond to the name of the attribute associated with your event frame.

           

          --Vince

          4 of 4 people found this helpful