kpierce

best way to generate query to provide periodic summaries with OLEDB/E

Discussion created by kpierce on Aug 1, 2013
Latest reply on Aug 6, 2013 by hanyong

Hi,

 

I have a bunch of wind turbines in a wind farm, let's say about 84 turbines.  From each turbine some met data is collected, such as temperature, wind speed, etc.  PI receives 10 minute summary data of temperature and wind speed, etc.  In PI AF, we have defined one template for the turbines and 84 elements, one for each turbine.  There are attributes that use PI point data references that point to the 10 minute summary met data.

 

I'd like to query using OLEDB/E to get daily aggregate data, such as min, max, averages of the met data, such as max temperature for the day.  This data will be queried (in part) by MS PowerPivot for further analysis there.  It will likely be looked at over a given year so we'll be calling back daily summary data for each turbine, e.g. 84 * 365 * aggregation count, say 3 * number of met measurements, say 5 which is about 500k values.  We can break up the data further, say by measurement, to create separate PowerPivot tables and then integrate within PowerPivot if need be.

 

The question is, what's the best way to query this data given the above metrics?  We could bring all the 10 minute data back to PowerPivot and then do the summaries there, but that seems like way too much data move around unnecessarily.  There are a number of other ways that have been considered so far, including...

 

1. using Abacus, PE's, etc. for PI tags that maintain this information, but this could end up in a lot of unnecessary tags to try various types of summaries.

 

2. use PI AF attributes that calculate the daily summaries, again this creates a lot of attributes that may or may not be used a lot

 

3. use AVG, MIN, MAX functions within OLEDB/E, but to date I haven't been successful in creating this query.  I'm not sure if I should pull back the 10 minute data then aggregate by day?

 

4. use some other novel approach with OLEDB/E that I've just not been able to put my head around yet, such as retrieving archive values for the aggregation, use another built-in function, etc.

 

Any and all input is appreciated.

 

Below is a sample query of something tried earlier.  This is not of the real system, just an example and it was based on the OLEDB/E users guide.

 

select avg(tir.[\Bearing Shaft Temperature]) [AVG Bearing Shaft Temp], 

 

e.Name, et.Name, date(FORMAT(tir.Time, 'yyyy-MM-dd')) [DateTime], time(FORMAT(tir.Time, 'HH:mm:ss')) [Time]

 

from

 

(select ID, Name

 

from elementTemplate where Name = 'GE Wind Turbine') et

 

inner join element e on e.ElementTemplateID = et.ID

 

-- -- dynamically use Transpose TVF --

 

CROSS APPLY TransposeInterpolateRange

 

< N'GE Wind turbine' /*templateName*/, '\' /*attributePath*/ ,

 

TRUE /*includeSubtree*/,

 

FALSE /*valuesAsVariant*/ >

 

( e.ID, N'1/1/2013 12:00:00 AM' /*startTime*/, N'26/7/2013 12:00:00 AM' /*endTime*/, N'1d' /*timeStep*/ ) tir

 

group by e.Name, et.Name, date(FORMAT(tir.Time, 'yyyy-MM-dd')), time(FORMAT(tir.Time, 'HH:mm:ss'))

 

option (ALLOW EXPENSIVE, IGNORE ERRORS, FORCE ORDER)

 

(sorry, it didn't copy in well)

Outcomes