The non-time-series data in PI is not always presented clearly, which can lead to errors and wasted time. Ideally, this would be fixed directly in PI, but until then, the spreadsheets presented in this blog post should make your PI life a bit easier. You can download the spreadsheets from the comments section of their respective blog posts. Some spreadsheets do not have a corresponding blog post other than this one, and these spreadsheets are attached in a comment below.
All of these spreadsheets use Power Query, so to refresh their tables, you need a Windows version of Excel 2016 or later or a Mac version of Excel for Microsoft 365.
PI Access Control List Normalizer
Access control lists (ACLs) are lists of PI Identities or AF Identities and the permissions that they have on some object. Here is an example of an ACL from the PIPOINT database security table:
PI Reader: A(r) | PI Interface Configuration Utility: A(r,w) | PI Administrator: A(r,w) | PI Vision: A(r) | PI Buffer Subsystem: A(w) | PI Connector: A(r,w) | Buffered PI Interface: A(r) | PI Connector Relay: A(r,w) | PI Data Collection Manager: A(r) | PI System Connector: A(r) | PI Analysis: A(r,w)
Can you read that? I didn't think so. The PI Access Control List Normalizer spreadsheet solves this by turning lists of access control lists into a single table, which can then be sorted and filtered and generally navigated more easily. These ACLs can come from PI Builder (from PI Points or PI Asset Framework elements) or from a CSV export of the Database Security table in PI System Management Tools. See this blog post for more details and to download the spreadsheet.
PI Analysis & Notifications Log Tabularizer
The log files from the PI Analysis Service and the PI Notifications Service can be difficult to navigate, especially when the severity level "Trace" is enabled. This spreadsheet, which is attached in a comment below, tabularizes these log files so that entries can be sorted and filtered by timestamp, severity, etc. for easier navigation. This spreadsheet was made in response to this question and requires Excel for Microsoft 365. It may or may not be compatible with Excel 2019.
All Tuning Parameters
Most (>50% of) tuning parameters are hidden by default in PI System Management Tools (PI SMT). Before you can edit a hidden tuning parameter, you must enter its name in PI SMT. This leads to a chicken-and-egg situation. How do you know which tuning parameter you might want to edit if you can't see it? The solution is the All Tuning Parameters spreadsheet, which compiles, into a single table, a list of all tuning parameters that are supported by PI SMT, whether they are visible or hidden by default. For more backstory and to download the spreadsheet, see this blog post.
WARNING: Do not modify the values of hidden tuning parameters without the assistance of OSIsoft tech support. To quote Sam Pride: "I have seen a number of customers cripple their PI systems by changing these values unguided".
Enhancing OSIsoft Learning With Excel
The Enhancing OSIsoft Learning With Excel spreadsheet combines the data from the All Content page and learning path pages of OSIsoft Learning into a single easy-to-browse table. Get a big-picture view of what OSIsoft Learning offers, compare course prices, and find equivalent courses in your language or country. For more details and to download the spreadsheet, see this blog post. This spreadsheet uses Power Pivot.
Full Data Collection List
The Full Data Collection List spreadsheet shows the Data Collection List in its full, non-paginated form. This spreadsheet scrapes the inline JavaScript code of the web page, which contains all of the table's data regardless of which page you are viewing. Use this spreadsheet to find anomalies in the table and patterns among the data collection products. This spreadsheet uses Power Pivot and is attached in a comment below.
Trivia about the Data Collection List table:
- Not all PI APS Connectors are listed in the table
- All PI Adapters are non-standard and are for Windows & Linux
- All 4 PI AMI Interfaces are non-standard and are for 64-bit Windows
- All 9 PI COM Connectors are non-standard, are for Windows, and have an end-of-support date of 2021-12-31
- All PI Connectors that are listed in the table are standard (except for the PI Connector for IPMI, which is non-standard) and are for 64-bit Windows. Deprecated PI Connectors exist, but they are not listed in the table (e.g. PI System Health Connector, PI Connector for NOV WITSML).
- All products for NTA, VMS, VAX, LNX, LX32, Vendor Processor, SOL1, SOL2, and Sol2_DECNET are non-standard PI Interfaces
- More than ½ (specifically, 412 out of 737) of the rows are not even for products. These rows are dedicated to catching your search terms and directing you to the correct product, which is usually on a different row.
- There are only 330 products listed in the table. However, not every data-collection-related product by OSIsoft is listed in the table, and not every product in the table is made by OSIsoft.
- PI Interfaces span 15 platforms. All other types of products span only 1 or 2 platforms.
- The table has a hidden "Part Number" column whose values are either 0 or 1. The value is 0 if and only if the platform is left blank.
- 5 standard PI Interfaces are not for Windows
- 27 PI Interfaces are available for >1 platform
- 3 PI Interfaces do not contain "Interface" in their name: PI Event Frames Generator, PI Batch Generator (PIBaGen), and PI Point Sync for Siemens Spectrum
- 2 non-standard products and all 26 third-party products are listed without product codes
- There are far fewer standard PI Interfaces than OSIsoft's main website claims. See this suggestion.
All OSIsoft UserVoice Suggestions
The All OSIsoft UserVoice Suggestions spreadsheet compiles all suggestions on OSIsoft's feedback website into a single table. Break free from the rigid categorical organization of the suggestions on the feedback website. Get a big-picture view of the suggestions and find patterns in the suggestions that would otherwise not be apparent. When would anyone use this? I used a similar spreadsheet similar to write my suggestion compilation blog posts! Use your imagination!
As a bonus, this spreadsheet also uses my "Add Columns" custom Power Query function, which can greatly simplify Power Query code in large projects. You can learn more about it from this Excel UserVoice suggestion.
This spreadsheet uses Power Pivot and is attached in a comment below.
Conclusion
Which spreadsheet was your favourite? If you have an idea for a general-purpose PI spreadsheet, let me know and I might be able to make it! If you liked this blog post, consider checking out my other blog posts here.
