A couple of weeks ago, I've put up a new tutorial titled "Exporting PI Data to XML". As the title of the tutorial suggests, it is focused on exporting PI Data to XML format. If it is of your interest, you can access it in the vCampus Library, under "White Papers and Tutorials" -> "Miscellanous" -> "Exporting PI Data to XML".
One of the comments that I've got about the paper is "What about exporting AF data to XML?", which is the main intention of having this blog post.
3 methods of exporting PI data to XML format is covered in the tutorial:
The first two options are pretty straightforward. PI Web Services is able to read data from AF if a path to AF Server and the specific attribute is given. And we can always use PI OLEDB Enterprise in place of PI OLEDB for connection to AF in the SQL world.
What I'll cover in this blog post is the steps to import AF Data into Excel. And what we will use is PI OLEDB Enterprise in Excel to get data from AF. Here's what we will need to start the work:
Step 1: Defining a Data Connection Source
- Go into the Data menu and import data from other sources.
- Choose “Others/Advanced” for data source
- Choose “PI OLEDB Enterprise 2010” as the OLE DB Provider
- In "Data Link Properties", input your AF server name and other appropriate settings. In the example here, I've configured it to use Integrated Security
Click on "Next" to enter information to save the configuration as a Data Connection (.odc) file, then "Finish". You might be prompted for a login at this time. If you select Windows Authentication, you can click on “OK” directly to proceed.
At this point, you have defined a data connection to AF Server from Excel. The same data connection can be re-used for future, so you would not need to re-do them again. If you want to re-use the data connection, simply go to “Data” -> “Existing Connections”
Step 2: importing data using data connection to PI OLEDB Enterprise
We can select any table here and click “OK” to continue. As mentioned earlier, you probably want to join different tables together. In the future steps, we will input the SQL query to extract data instead of simply importing from a single table.
The next dialog box that you should see allows you to choose where and how you want to view the data in Excel. But before that, we want to go into “Properties” to change some configurations.
You can put in any query as long as it works. You probably want to first test out the query using client tools like PI SQL Commander.
The query that I am using is as follows:
SELECT eh1.Path, eh1.Name as "Plant", ea1.Name as "Attribute", a1.Time, a1.ValueDbl as "KPI"
FROM [Demo].[Data].[Archive] a1
INNER JOIN [Demo].[Asset].[ElementAttribute] ea1
ON a1.ElementAttributeID = ea1.ID
INNER JOIN [Demo].[Asset].[ElementHierarchy] eh1
ON ea1.ElementID = eh1.ElementID
WHERE eh1.Name LIKE 'Plant1'
AND a1.Time BETWEEN DATE('29-Aug') AND DATE('*')
ORDER BY a1.Time
OPTION (IGNORE ERRORS, EMBED ERRORS)
As you can see, I am joining 3 tables together to display the element path, element attribute and archive data from the attributes.
We can click on “OK” to proceed, and you would be prompted the following dialog. Click “Yes” to continue. You should be back at the following dialog box, to choose the way to display the data, and where to put the data in Excel.
- Once you have made your selection and click “OK”, you should see the data being imported into Excel.
With the AF data imported into Excel, you can use the information to fit your needs, creating a report, export to XML format like what is described in the tutorial, etc...
This would be one of the methods to import AF data into Excel. There are other viable methods like:
You can take a look at webinar recording for "Personal BI with PI DataLink and Excel 2010" here
This is currently on Community Technology Preview (CTP), available for download in Download Center
Depending on your requirements, you can play around with these options as well.
Thanks to Jay for his comments, which leads to this blog post.