Microsoft Excel has an interface for getting data from the Web that works directly with the PI API. It's the same query interface for both Excel and Power BI. Make sure you can connect to PI from Excel using the instructions to Get Project Insight Data into Power BI Using REST. Here's an easy example to get started with your first Excel report once you have your API connection working. Practice with this first and we'll look at the details of the syntax and how it works later so that you can easily modify this for the data you want.
This simple report will get the active projects name, type, status, percent complete and primary project manager.
- Use the connection string for the "Get Data > From Web" option.
- Use this example and remember to use the instructions referenced earlier for connecting with an API token. You can copy and paste everything here from /api after your full URL (including projectinsight.net).
Your query for getting and arranging the data you want is now the data source for your worksheet, you can save it and Refresh All from your saved Excel file anytime you want to update your worksheet from real-time PI data.
While the above steps are quite simple, you may look at the REST API References used to construct the query string and see the references can be quite overwhelming. No worries, we've got your back. Getting what you want out of those references and constructing that query is much easier than it looks. Let's look at this string again and break it out into sections which will help you build your own data query.
Your base address with /api which is required to access all of the data references
You're identifying that the data will come from projects and that those projects are in the active state.
modelProperties define which data fields we want to retrieve. We'll follow with two different sections of the modelProperties to explain the difference between the first set of data records we retrieve and the field references within those data records we want in our worksheet.
The data record names we want in our spreadsheet are separated with commas in this section. To further identify the data fields we want in our worksheet from within those records, we need to separate these records with a semicolon from the more targeted data fields we want from within those records.
As you noticed when we extracted data fields from within the records, each record may contain many data fields including a unique identifier (GUID) for the record. Rarely do you want all of that data within the record. Most frequently, we only want the Name field. This section helps to minimize the data downloaded within that record. We specify the record and separate that record from the data fields we want within that record using a colon. We separate the data fields within that record with commas and end each record data field request with a semicolon if we have more records to add.
We often need more complex data than what we've described here and that's why we work with real-time data retrieval into powerful analytics tools in the first place. A single worksheet often contains multiple queries for different item types using reference data and pivot tables for analysis and presentation. We use Excel as an example because everybody knows Excel. This same method works for most business intelligence tools available today with only a few slight modifications in most tools.
As stated before, we've got your back! Questions about query strings, data record names and modelProperties are often answered from references within this article, but you can always search and post questions to help get what you need in our PI#enterprise Discussions for additional assistance from the community.