AnsweredAssumed Answered

How can PI OLEDB Enterprise get fast query results from SAP HANA through PI AF Data References?

Question asked by isolutions on May 15, 2018
Latest reply on May 16, 2018 by Roger Palmen

PI OLEDB Enterprise performs very poorly when loading data from ODBC (SAP HANA), with either a Table Lookup or a CDR.

  • With a Table Lookup, the performance is unusably slow. In System Explorer's "Time Series Data", it takes 2.2 seconds to load 10 rows, and 3.4 s for 1000 rows. In PI OLEDB, the query times out.
  • Using a CDR is faster in System Explorer (0.6 s for 10 rows, 0.8 s for 1000 rows), but the performance is still unacceptable in PI OLEDB.


While the performance in System Explorer is reasonable, the performance in PI OLEDB is not:

  • If I run a PI SQL Commander query for the table lookup attribute and then the CDR attribute, they both take a long time to run (at least 20 seconds), so I tried reversing the order.
  • If I run a query for the CDR attribute first, it takes about 8 to 12 seconds to return 7 rows. The Table Lookup query times out after 81 s, but it takes about 8 to 12 seconds in subsequent runs (possibly caching).
  • If I double the number of rows, the execution time doubles.


My CDR overrides GetValue, GetValues, RecordedValue, and RecordedValues in AFDataReference. Logging shows that PI System Explorer uses GetValue to get a single value and RecordedValues for the "Time Series Data" dialog, but PI OLEDB makes a call to GetValue per row in the result. Even with the SupportedMethods flags overriden to "GetValues" instead of "GetValue | GetValues", PI OLEDB still seems to call GetValue.


How can we improve the performance? Can we make it use GetValues instead of GetValue?




PI SQL Commander Query

The other query is the same with a column name changed to "\Totals\Steam Injection".

SELECT eh.Path, eh.Name Element, tir.Time, tir."\Totals\Steam Injection Slow"
FROM [<redacted>].[Asset].[ElementTemplate] et
INNER JOIN [<redacted>].[Asset].[Element] e
ON et.ID = e.ElementTemplateID
INNER JOIN [<redacted>].[Asset].[ElementHierarchy] eh
ON e.ID = eh.ElementID
INNER JOIN [<redacted>].[DataT].[ft_TransposeInterpolateRange_Injector Well] tir
ON eh.ElementID = tir.ElementID
WHERE et.Name = N'Injector Well'
AND tir.StartTime = DATE(N't-7d')
AND tir.EndTime = DATE(N't')
AND tir.TimeStep = N'1d'
AND eh.Path  = '\<redacted>\Offsites\<redacted>\'
AND eh.Name = 'I01'


CDR Queries


SELECT TOP 1 "Production Date" PITimestamp, "Allocated CWE of Steam m3" PIValue FROM <redacted> WHERE "Production Date" <= ? AND "Well Code"='<redacted>' ORDER BY "Production Date" DESC



SELECT "Production Date" PITimestamp, "Allocated CWE of Steam m3" PIValue FROM <redacted> WHERE ("Production Date" >= ? AND "Production Date" <= ? AND "Well Code"='<redacted>') ORDER BY "Production Date" DESC


PI AF Table

Attribute Config String (Table Lookup)

SELECT [Allocated CWE of Steam m3] FROM [<name of PI Table>] WHERE [Well Code] = '<redacted>';TC=Production Date


Connection string




"Well Code",
"Facility Area",
"Well Name",
"Well Pad",
"Well Type",
"Production Date",
"Meter On Strm Hours",
"Allocated Oil Volume m3",
"Allocated Gas Volume e3m3",
"Allocated Water Volume m3",
"Allocated CWE of Steam m3",
"Brackish Water Volume m3",
"Measured Water Volume m3",
"Fresh Water Volume m3",
"Inj On Strm Hours"
FROM <redacted> WHERE 
("Production Date" > ADD_DAYS(NOW(),-365) and "Production Date" <= ADD_DAYS(NOW(),-1))