AnsweredAssumed Answered

OLEDB Enterprise Transpose Query for Step Type Points

Question asked by DCell on Oct 25, 2018
Latest reply on Nov 20, 2018 by 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.

Outcomes