hanyong

Importing AF Data to Excel

Blog Post created by hanyong Employee on Nov 3, 2010

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:

  • programmatically through PI Web Services
  • using Microsoft SQL Server and SQL Server Integration Services (SSIS) and PI OLEDB Provider
  • using Excel and PI DataLink
  • 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:

  • Microsoft Excel
  • PI OLEDB Enterprise
  • Step 1: Defining a Data Connection Source

    1. Go into the Data menu and import data from other sources.
      screen001.jpg

    2. Choose “Others/Advanced” for data source
      screen002.jpg

    3. Choose “PI OLEDB Enterprise 2010” as the OLE DB Provider
      screen003.jpg

    4. 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
      screen004.jpgscreen005.jpg

    5. Select the AF Database you want to connect to. You probably will be joining the different tables exposed by PI OLEDB Enterprise, so we want to uncheck the option “Connect to a specific table”
      screen006.jpg

    6. 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”

     

    screen008.jpg

     

    Step 2: importing data using data connection to PI OLEDB Enterprise

    1. The next part would be to define what information to extract from the data source (in this case PI OLEDB Enterprise). Defining the data connection will transit into this part directly. And the 1st thing you would be prompted is to select a table
      screen009.jpg

    2. 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.

      screen010.jpg

    3. In the Connection Properties Dialog box, we can look at the “Definition” Tab. This is where we can change how we extract data from AF Server. The configuration that you should see: “Command type” would be “Table” and “Command text” would be the table that was specified earlier.
      screen011.jpg

    4. We want to change the “Command type” to “SQL” and input the SQL query as “Command text”
       screen012.jpg

    5. 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.

    6. 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.

      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.
      screen014.jpg

    7. Once you have made your selection and click “OK”, you should see the data being imported into Excel.
      screen015.jpg

    Conclusion

     

    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:

  • PowerPivot and PI OLEDB Enterprise (for Excel 2010)
    You can take a look at webinar recording for "Personal BI with PI DataLink and Excel 2010" here
  • AF Builder add-in
    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.

     

    Acknowledgement

     

    Thanks to Jay for his comments, which leads to this blog post.

    Outcomes