How do I write SQL queries against PI using PI OLEDB?

PI OLEDB provides SQL access to PI Data Archive and PI AF data through standard OLEDB interfaces, enabling queries from Excel, SSRS, and custom applications.

PI OLEDB Enterprise (AF Data)

-- Query AF element attributes
SELECT
    eh.Path,
    ea.Name AS AttributeName,
    ea.Value,
    ea.Time
FROM
    [Asset].[ElementHierarchy] eh
    INNER JOIN [Asset].[ElementAttribute] ea
        ON ea.ElementID = eh.ElementID
WHERE
    eh.Path LIKE '\\MyDatabase\Plant1\Pumps\%'
    AND ea.Name = 'Flow Rate'
    AND ea.Time BETWEEN 't-1d' AND '*'

PI OLEDB Provider (PI Data Archive)

-- Query archived data from picomp2
SELECT
    tag,
    time,
    value,
    status
FROM
    piarchive..picomp2
WHERE
    tag = 'Sinusoid'
    AND time BETWEEN 't-1h' AND '*'
ORDER BY
    time DESC

Useful Tables

Table	Description
piarchive..picomp2	Compressed archive data
piarchive..piinterp2	Interpolated data at regular intervals
piarchive..pisnapshot	Current snapshot values
pipoint..pipoint2	Point configuration

Tips

• Use piinterp2 for evenly spaced data (great for reports and charts).
• PI OLEDB uses PI time syntax (t-1d, *, y) — not SQL datetime.
• Performance: always filter by tag and time range to avoid full scans.
• PI OLEDB Enterprise requires AF SDK installed on the client machine.