AnsweredAssumed Answered

Best way to rollup future data?

Question asked by arosenthal on Jun 8, 2017
Latest reply on Jan 31, 2018 by bdeslatte

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,
    i.Time,
    SUM(i.ValueDbl)
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


WHERE
  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


OPTION (FORCE ORDER, EMBED ERRORS)

 

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

Outcomes