To get PI data into Excel, most people would choose PI DataLink due to its simplicity or because that is all that they know. However, if you are using PI DataLink repeatedly in the same spreadsheet or need to retrieve large amounts of data, PI SQL Client is more appropriate, especially if you are already using Power Query or Power Pivot in your spreadsheet. The table below compares PI DataLink and PI SQL Client.
|Attribute||PI DataLink||PI SQL Client|
|Age||Older||Newer and likely still being improved significantly|
|Small direct-to-sheet reports (refreshes too slowly when there is a lot of data)||Medium and large data sets (too much work for small data sets)|
|Languages required||Excel formulas (none if using the GUI)||Power Query or SQL (none if using Power Query's GUI)|
|Compatible programs||Excel only||Any program that uses OLEDB, ODBC, or JDBC|
|Requires PI Asset Framework?||No||Yes|
|Install kit size (MB)||290||100|
|32-bit + 64-bit program size (MB)||10||JDBC: 2|
|Data retrieval||1 value at a time (single-cell formulas) or in bulk (array formulas). Formulas can be recalculated separately.||Excel does not have an incremental refresh feature, unless you get hacky. To get new data, you must retrieve all of the desired data from PI, even the data that you already have.|
Using PI SQL Client in Excel
Data import and transformation in modern Excel is handled by Power Query. This section assumes that you have basic knowledge of how to use Power Query.
In Excel, go Data tab → Get Data → From Other Sources → From OLEDB → Build.
Excel uses the same program as PI SQL Commander Lite to build the connection string. Proceed as you would in PI SQL Commander Lite. Click OK when you are done. To clear any confusion, the "Data Link" in the Data Link Properties window's name is not a reference to PI DataLink.
Even though the From OLE DB window allows an SQL statement to be entered directly, we will instead keep each query in its own .sql file to make it easier to edit it in PI SQL Commander Lite in the future. Otherwise, you will be copying and pasting the queries to and from PI SQL Commander Lite and possibly have a version in Excel and a version for editing, which is not ideal. When you are done building your connection string, click OK on the From OLE DB window.
In the Navigator window, select OLE DB in the hierarchy and click on Transform Data.
We are now in the Power Query Editor. Go to the View tab and click on the Advanced Editor. Add the [Query = …] code as shown below and replace the "Path to the query" string with the file path of the .sql file containing your query. Click on Done.
[Query = Text.FromBinary(File.Contents("Path to the query"), TextEncoding.Utf8)]
What you do after this point depends on your specific case.
What about PI OLEDB Provider and PI OLEDB Enterprise?
Querying time-series data
If your organization does not use the PI Asset Framework, you are stuck with using PI OLEDB Provider to retrieve large amounts of PI data into Excel.
For non-administrator PI users whose organization does use the PI Asset Framework, PI OLEDB Provider and PI OLEDB Enterprise have been largely superseded by PI SQL Client. A major problem with PI OLEDB Provider and PI OLEDB Enterprise is that there is no dedicated server-side program to process requests from them. If a query is not written correctly, large amounts of data will be returned to the client for filtering and processing. The fast version of such queries is often longer, more repetitive, and less intuitive to write than the naive version. In my experience, PI SQL Client queries are still faster than their optimized counterparts for PI OLEDB Provider or PI OLEDB Enterprise.
Querying configuration data
To PI administrators, it is useful to be able to retrieve PI configuration data into tables for bulk analysis. "Configuration data" is the non-time-series data about PI Points or the PI Asset Framework themselves. Unfortunately, neither PI DataLink nor PI SQL Client can currently be used for this. PI Builder can be used, but its output is static, and when multiple tables are involved, it can be cumbersome to update all of the tables. This is where PI OLEDB Provider and PI OLEDB Enterprise shine in a modern PI system, since they can provide PI configuration data in a refreshable table. To clarify, even when PI OLEDB Provider and PI OLEDB Enterprise are used, PI Builder will still be used to publish the changes; it just will not be used to retrieve the configuration data.
|Attribute||PI Builder||PI OLEDB Provider + PI OLEDB Enterprise|
|Configuration data available|
PI Asset Framework
|PI Point (PI OLEDB Provider)|
PI Asset Framework (PI OLEDB Enterprise)
|Type of table||Static||Refreshable|
|Most suitable consistency check||Ensuring consistency across all items of a single table (across PI Points, across attributes, etc.)||Ensuring consistency between tables (e.g. between PI Points and PI AF attributes, find unused digital state sets and unused enumeration sets)|
|Publishing changes||PI Builder||PI Builder|
For a deeper comparison between the different PI SQL programs, see this video.
To use PI OLEDB Provider or PI OLEDB Enterprise in Excel, follow the same steps as for PI SQL Client, but select PI OLE DB Provider or PI OLEDB Enterprise in the Data Link Properties window.
Versions of programs used for this post
- Excel for Microsoft 365 (Windows)
- PI DataLink 2019 SP1 Patch 1
- PI SQL Client 2018 R2
- PI OLEDB Provider 2019
- PI OLEDB Enterprise 2019
- PI Builder 2018 SP3 Patch 2