2 Replies Latest reply on Nov 20, 2018 8:34 PM by DCell

    OLEDB Enterprise Transpose Query for Step Type Points

    DCell

      I'm looking for some guidance on how best to perform a certain type of query via OLEDB Enterprise.

      I'm trying to pull multiple attributes for an asset out of AF for a daily shift report.  The values shift totals/averages and are totalized over a 12 hour period and updated once every 12 hours in the PLC.  These values show up in other reports, so I don't want to totalize or average in the historian to keep numbers consistent.

      I want to make sure I catch every single change in the tags exactly (frequency is low anyways), so I've set the compdev, compdevpercent, exdev, exdevpercent to 0 in the point setup.  I've also changed step=1 for these tags in the point setup since interpolation isn't really relevant between shifts for a tag called "Previous Shift Value X".  My understanding is that this causes the points to hold their last value until a new on comes in (i.e. avoid interpolation).

      My shift changes at roughly 6:00am/pm in the PLC, but this isn't an exact time as the PLC is not perfectly synchronized.  I get a new archive value at around 6:02am/pm, so I've set my query to occur at 6:05am/pm to ensure I check the results after they are logged by the historian.

      If I use the Transpose Interpolate Discrete function to pull data at 6:05am/pm the values are interpolated between shifts, so sampling at 6:05, vs 6:10, vs 7:05 changes my results (even though the last value in the archive hasn't changed).  That make sense since it is interpolating, but I was hoping the "Step=1" in my tag configuration would cause it to use the previous archive value.  Here is some code that shows the behavior, executed from SSMS via linked server:

       

      DECLARE @ReportDate DATETIME = dateadd("day", -6, CAST(GETDATE() AS DATE))
      DECLARE @Time1Varchar varchar(50) = convert(varchar, dateadd("minute", 365, @ReportDate), 20)
      DECLARE @Time2Varchar varchar(50) = convert(varchar, dateadd("minute", 365+60, @ReportDate), 20)
      EXEC('
           SELECT eh.Path + eh.Name Element, tid.*
           FROM [AfDatabase].[Asset].[ElementTemplate] et
           INNER JOIN [AfDatabase].[Asset].[Element] e
                ON et.ID = e.ElementTemplateID
           INNER JOIN [AfDatabase].[Asset].[ElementHierarchy] eh
                ON e.ID = eh.ElementID
           INNER JOIN [AfDatabase].[DataT].[ft_TransposeInterpolateDiscrete_Equip - MyEquip] tid
                ON eh.ElementID = tid.ElementID
           WHERE et.Name = N''Equip - MyEquip''
                AND tid.[Time] IN (?, ?)
                ORDER BY eh.Name, tid.[Time]
           OPTION (FORCE ORDER, IGNORE ERRORS, EMBED ERRORS)',@Time1Varchar, @Time2Varchar) AT [LINKEDPI]
      
      --end
      

       

      Data comes out looking like this (interpolating and giving different values at the different times), rather than holding the last archive value until the next one comes in.

       

                                                                                                                                                                                                                                                                                                                                                                                             

      ElementTimePrevious Shift - Value1Previous Shift - Value2Previous Shift - Value3Previous Shift - Value4Previous Shift - Value5Previous Shift - Value6Previous Shift - Value7
      Asset012018-10-19 6:050.7746742373020.8100103140.7676582930.59467613757.012584690.947713256
      Asset012018-10-19 7:050.7832209473050.8091657760.7652820350.59915405556.836227420.945743322
      Asset022018-10-19 6:050.7644493582980.7516489030.7278220650.55633765554.054088590.968274176
      Asset022018-10-19 7:050.7738506793020.7462138530.7168756130.55358481453.241168980.959998667
      Asset032018-10-19 6:050.7756046653140.6518461110.6261572240.48565298346.502330780.960592091
      Asset032018-10-19 7:050.783629063170.6509334440.6268743280.49130234146.555759430.963078737
      Asset042018-10-19 6:050.76955393000.6618278620.6498850580.50011795848.264881130.981954634
      Asset042018-10-19 7:050.7783142923040.6616617440.648936450.50498205448.194595340.980764329
      Asset052018-10-19 6:050.7925111653050.6467707160.640007675047.531166080.989544153
      Asset052018-10-19 7:050.800735953080.6418197150.635241151047.177341460.989769936
      Asset062018-10-19 6:050.8236240743170.7203411460.6968361140.57391029651.752017970.967370987
      Asset062018-10-19 7:050.8303337693200.71290350.6898407340.57226449351.232650760.967686951
      Asset072018-10-19 6:050.7879730463190.6178611520.6103066210.48090365545.325630190.98777312
      Asset072018-10-19 7:050.7966147073230.6175272460.6099029180.48581796945.295734410.987652779
      Asset082018-10-19 6:050.8472626213430.5670016410.5446236730.46138545940.448066710.960464478
      Asset082018-10-19 7:050.8567273023470.558804810.5317301150.45416045239.490436550.949764669
      Asset092018-10-19 6:050.7436044813010.5576988460.5439554450.40452355140.397304530.975350678
      Asset092018-10-19 7:050.7543036943050.564726830.5515432950.41695401140.961036680.976493359
      Asset102018-10-19 6:050.7755666973140.6489843130.628469050.48740243946.67397690.96838671
      Asset102018-10-19 7:050.7825623153170.6486322880.6228815320.48699927346.259090420.960249722

       

      If I instead use the Transpose Archive Function to pull data between 5:55 and 6:05, I get multiple rows for a single piece of equipment. This is due to some of the timestamps on the tag values coming in slightly out of sync.  For example, I'm getting multiple rows when some of the attributes on the same asset have timestamp differences of 4ms.  Row 1 will have values for half the attributes and row 2 will have values for the other half.

       

      What is the best way to handle this issue?  Can I force Transpose Interpolate Discrete to pull the last archive value?  Or is there a good way to merge the multiple rows that come out of the Transpose Archive query?

       

      Thanks.

        • Re: OLEDB Enterprise Transpose Query for Step Type Points
          vkaufmann

          Hi David,

           

          I would recommend you set your step attribute on these underlying PI tags (assuming they are all PI tags) to 1. This will prevent any interpolation when reading the values from the archive but is global for any client requesting this data.

           

          https://livelibrary.osisoft.com/LiveLibrary/content/en/server-v11/GUID-E76B67EA-910A-4304-959B-962681848DD6

           

          --Vince

            • Re: OLEDB Enterprise Transpose Query for Step Type Points
              DCell

              Thanks Vince,

               

              This was helpful.  I thought I had changed all of the step settings on the pi points to step=1, but after reading this I checked the tags and the setting somehow did not take when I imported from PI Builder.  Updating these to step = 1 solved my problem and made the transpose interpolate discrete functions select the most recent previous value.

               

              I also recently learned of another way to handle this while looking through some documentation, though the step=1 is a better option in my opinion as it sets the behavior at the source.  In the PI Point Data Reference Settings Window in AF, setting the value retrieval by time method to "At or Before" also has the same effect.  I had previously left these settings as "Automatic".  Automatic defers the interpretation to the step setting on the pi point.

               

              At Or Before.PNG

               

              Cheers.