3 Replies Latest reply on Jan 31, 2018 7:22 PM by bdeslatte

    Best way to rollup future data?


      I have recently started using future data in Asset Framework and am wondering what the best way to rollup future data is. My ultimate goal is to perform some rollup operation (mostly summation) of future data over a relative time range (for example, * to *+1d), so that the end result is a new future data series for that time range. Right now it doesn't look like this is possible out of the box with rollup analytics, so I have to seek a custom solution. I'm wondering if anyone has experimented with this or developed a solution that scales well. My initial thoughts are there are two ways to implement this:


      • Develop a custom application using AF SDK.
      • Use a PI OLEDB Enterprise query in a linked AF Table and lookup the data using a Table Lookup Data Reference attribute.


      The second option seems easier to implement from an architectural point of view, but I'm not sure how it will perform (or even how to write such a query).


      Update: I made a first attempt at this using PI OLEDB Enterprise, but I'm not happy about its performance:


      SELECT ParentName(eh.Path, eh.Level - 2) AS WindRegion,
      FROM Asset.Category c
      INNER JOIN Asset.ElementCategory ec ON ec.CategoryID = c.ID
      INNER JOIN Asset.ElementHierarchy eh ON eh.ElementID = ec.ElementID
      INNER JOIN Asset.ElementAttribute ea ON ea.ElementID = eh.ElementID, [Data].[ft_InterpolateRange] i
        c.Name = N'WindGeneratingUnit'
        AND eh.Path LIKE N'\Wind Regions\%'
        AND ea.Path = N'\AWS_Forecast\' AND ea.Name = 'Forecast'
        AND i.ElementAttributeID = ea.ID -- first InterpolateRange TVF argument
        AND i.StartTime = N'*' -- second InterpolateRange TVF argument
        AND i.EndTime = N'*+1d' -- third InterpolateRange TVF argument
        AND i.TimeStep = N'1h' -- fourth InterpolateRange TVF argument
      GROUP BY ParentName(eh.Path, eh.Level - 2), i.Time
      ORDER BY 1, 2


      Basically this query is retrieving attribute values for multiple interpolated time points in the future. Each of the attributes are configured as a Table Lookup Data Reference using the "Table provided time series data" setting. The table lookup is on an AFTable that is linked to an external database. The query then sums up the retrieved values for each time point to effectively produce a new time series.


      When I ran this query against my AF database, it targeted about 200 attributes and took roughly 48 seconds to execute. Are there any PI OLEDB Enterprise gurus out there who might offer advice on how to improve this performance so it scales better?


      Message was edited by: Aaron Rosenthal

        • Re: Best way to rollup future data?
          Kenji Hashimoto

          In your example PI OLEDB query, it requests events from * to *+1d, Interval = 1h.

          If you want to run it like analysis service, then I think you only need to get one specific future timestamp.

          Or do you want to get summary values for 200 tags and want to calculate?


          Adding, you are using Linked table. If the performance bottle neck is table link, then it will be difficult to get performance improvement.

          I guess that it is good test to import the data to AF and run the query. (Or create PI tags and put the events to tags)

            • Re: Best way to rollup future data?

              I'm trying to rollup and have the result be a time series. Currently the only way to do this with analysis is to have one rollup analysis for every time point. For events from * to *+1d, interval = 1h, this would be 24 rollup analyses for each element. This can grow very quickly if the time range is expanded or the interval is shorted. My hope was to use PI OLEDB to do this rollup, but as you mentioned the performance seems very poor since the attributes are tied to linked table data. I suppose the only way forward is to use AF SDK?

            • Re: Best way to rollup future data?

              A very messy and indirect way of doing this using on analyses:


              1a) Periodic Analysis that sums the future data

              Tagmean('MyFutureDataAttr','*', '*+1d')*EventCount('MyFutureDataAttr', '*', '*+1d')


              1b) Map this to an attribute with at trigger time +1d      MY1DSum



              If you want to roll this up to parents, then do this instead

              1b) Map this to an attribute with at trigger time (no future*) MY1DSum

              2) Rollup the sum of all children with MY1DSUM and then map to an attribute with a trigger time +1d


              *Rollups fail when the search criteria pointed at future data, so use current time instead