Kenneth_Barber

Beyond PI DataLink: analyzing large data sets in Excel using PI SQL products

Blog Post created by Kenneth_Barber Champion on Jun 21, 2020

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.

 

AttributePI DataLinkPI SQL Client
AgeOlderNewer and likely still being improved significantly

Suitability

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)
DifficultyEasyEasy enough
Languages requiredExcel formulas (none if using the GUI)Power Query or SQL (none if using Power Query's GUI)
Compatible programsExcel onlyAny program that uses OLEDB, ODBC, or JDBC
Requires PI Asset Framework?NoYes
Install kit size (MB)290100
32-bit + 64-bit program size (MB)10JDBC: 2
ODBC: 25
OLEDB: 40
Data retrieval1 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 DataFrom Other SourcesFrom OLEDBBuild.

Menu selections to connect to an OLEDB data source in Excel

 

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.

Data Link Properties window

 

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.

Power Query "From OLEDB" window

 

In the Navigator window, select OLE DB in the hierarchy and click on Transform Data.

Power Query "Navigator" window

 

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.

 

OleDb.DataSource(
"provider=PISQLClient.1;data source=PIAFDatabaseName;location=PIAFServerName",
[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.

 

In summary:

AttributePI BuilderPI OLEDB Provider + PI OLEDB Enterprise
Configuration data available

PI Point

PI Asset Framework

PI Point (PI OLEDB Provider)
PI Asset Framework (PI OLEDB Enterprise)
Type of tableStaticRefreshable
Setup timeShortLong
Most suitable consistency checkEnsuring 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 changesPI BuilderPI 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

Outcomes