Skip navigation
All Places > PI Developers Club > Blog > Author: hanyong
1 2 Previous Next

PI Developers Club

20 Posts authored by: hanyong Employee

Importing AF Data to Excel

Posted by hanyong Employee 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.

    2. Choose “Others/Advanced” for data source

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

    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

    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”

    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”




    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

    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.


    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.

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

    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

      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.

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

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




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

    Windows Search

    Windows Search (successor of the Windows Indexing Service, formerly known as Windows Desktop Search or WDS on Windows XP and Windows Server 2003) is an indexeddesktop search platform released by Microsoft for the Windows operating system.

    Search for ProcessBook file contents in Windows Search

    In Windows Vista, Windows 7 and Windows Server 2008, Windows Search is integrated into the operating system. Upon installation, Windows Search (and Windows Desktop Search) builds a full-text index of the files on a user's hard drive. Once a file's contents have been added to this index, Windows Search is able to use the index to search results more rapidly than it would take to search through all the files on the computer. Searches are performed not only on file names, but also on the contents of the file (provided a proper handler for the file type is installed) as well as the keywords, comments and metadata the file might be tagged with.


    Windows Search by default includes handlers for common filetypes, including Word documents, Excel spreadsheets, PowerPoint presentations, HTML documents, text files, MP3 and WMA music files, WMV, ASF and AVI videos, JPEG, BMP and PNG images, among others. It would seem as if there is no available handler for PI ProcessBook file format, unless we take a step further and look how the underlying file content is saved. We can see that there are alternatives to allow us to search for contents in PDI or SVG without a custom handler or IFilter


    .PDI format is an extension for ProcessBook display files. The .pdi file content is hashed and hence if we open a .pdi file using a text editor like notepad, we can expected to see something like:


    However if we take a closer look at the file content, we can see that not all information is hashed. We can see that some key information is still readable in text form, like tagnames and symbol names. This means that we can index .pdi files as a text file to search for such information in .pdi files.


    On the other hand, .svg format is a file type defined Scale Vector Graphics files. The graphical content of a .svg file is saved as an XML document which means that we can configure Windows Search to index .svg file content as a plain text file naturally.


    Given that we can index both file types as plain text file, all we have to do next would be to configure Windows Search to index .pdi and .svg content.


    In Windows Vista and Windows 7, this can be done pretty easily if you go into "Indexing Options" in "Control Panel". By going into Advanced Options and the File Type tab, you should see a dialog window like below:


    If you choose the option to "Index Properties and File Contents" you should see that the "Filter Description" column for .pdi and .svg to be showing "Plain Text Filter".


    Next thing that is required is to ensure that the location that is storing your ProcessBook files is an indexed location. If you refer to the screenshot above again, you can see the list of locations that are indexed as well. If your files are located in those locations, you can search it easily using Windows Search. Of cause, if you save your ProcessBook files in other locations, you can add that to the list of indexed locations.


    So after the configuration is done, you can use the Windows Search feature is to search for files that includes tagnames like CDT158, or Dataset names like:

    Indexing PDI, PIW and SVG in Microsoft SharePoint Search 

    In most enterprise environment, there typically is a lot of information sharing among different users. So instead of just plainly searching within the local desktop, we would want to extend it to search of your SharePoint portal if possible. There are similar search features implemented on various versions of SharePoint technologies and hence it is possible to configure SharePoint search to index .svg and .pdi files based on the file content, allowing users to search for in the files that are shared on a SharePoint portal.


    Let's see how we can configure Microsoft SharePoint Search to index the contents of .pdi, .piw and .svg files. Because there are different versions of SharePoint, configuration may differ on these different version. Another thing is that SharePoint servers can be deployed with different roles (Web Front-End server, Application server, Database server) in a server farm. I did my configuration in a Standalone installation of Microsoft SharePoint 2007 server (which runs every in a standalone box), but if you perform the steps on the appropriate servers, it should work.


    Of cause, the 1st thing that you need is to configure Office SharePoint Search to work. You can find more reference online here: Alternatively you can do a web search to find some blog posts or article on this. You should take note which servers have the role of Index servers and Query servers. Index servers are responsible for crawling (i.e. indexing) the SharePoint contents while Query servers handle search queries from users. If you are using Microsoft SharePoint 2007 server, you can find out your server roles from "SharePoint 3.0 Central Administration", under "Application Management > Search Service", you should be able to find out which are your Query and Index servers.




    The configuration of Office Sharepoint Search to search custom file types using text filter is not as straightforward compared to doing it in Windows Search; we even need to edit the Windows Registry on the server in order to achieve this! This means that you need to run this as a user with sufficient privileges. With that, lets start to do this:

    1. .pdi and .svg are not indexed by default, hence we need to include them 1st. To do this, open up "Sharepoint 3.0 Central Administration" page. Under Shared Services Administration, select the instance that you want to configure.
      Then go into "Search Settings"
      And finally "File Types"
      You should now see a list of file types that Office Sharepoint Search indexes. Click on "New File Type" that should be on the top left corner. Type in "pdi" as the extension, and click on "OK"
    2. Just adding the file type is not enough because by default, Office Sharepoint Search will attempt to find an IFilter for the new .pdi extension to index the file content. Since a PDI IFilter is not available, Office Sharepoint Search will only be able to index based on file's property. What we need to do next is to make Office Sharepoint Search to regard the extension as a plain text file and use text IFilter to index the file. This is the part where we start to meddle with the registry. You can leave the Sharepoint Central Administration page alone for a while. 
      1. Create a .reg file to edit the registry in order Sharepoint Search to use text filter to index pdi file. A sample .reg file can be downloaded here.
      2. Run the .reg file and you will be shown a warning message that tells you that you are editing the registry. Confirm to make the changes required. Of cause it is always a good practise to backup the registry before doing this.
      3. Repeat step 1 and 2 on all Index and Query Server(s)
      4. There is still another part that we need to edit manually on all the indexing servers. Run "regedit" to go into Registry Editor. Find the registry key
        HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Shared Tools\Web Server Extensions\12.0\Search\Applications\<some guid>\Gather\Search\Extensions\ExtensionList
      5. Create new String Value Replace "New Value #1" with 38 (or the next number available) and press Enter
      6. Set the value as "pdi"
        Your output on the registry editor should be something like
      7. Repeat step 4 to 6 for all Index servers
    3. Next thing to do is to reset all crawled contents (i.e. the index) on the server. So go back to Sharepoint 3.0 Central Administration and select Search Setting for the Shared Services again (similar to step 1). This time select the option to "Reset all crawled content"
    4. Restart the search service for the configurations to take effect. One way to do this is in command prompt and run the following commands to restart the service:
      - net stop osearch
      - net start osearch
    5. Finally, after the service is restarted, initiate a full crawl for the Shared Services. So back to Search Settings again, and this time select the option "Content Sources and Crawl Schedule" instead
      And in the next webpage ("Manage Content Sources"), Choose to start the crawls
      The search service should start to crawl through the sharepoint files and indexing them, and this time round the configuration to crawl through contents of pdi and svg file is included.

    After the crawl is done, it's time to test the configuration. You can type a tagname or a dataset name as a search term in any of the search boxes on your sharepoint sites to find the pdi or svg that contains the term.

    Note: There are still some limitations when we attempt to search files using tagnames with some characters inside, like "BA:Level.1" will not find the pdi or svg file with the tag, but "BA:Level" will.


    Thanks to Jay Lakumb for his idea of searching through ProcessBook file contents in Windows and SharePoint Search.


    Thanks to Anne and Navdeep for their respective blog posts about configuring SharePoint search for .csv and .cs files. These served as a reference for me to come up with the steps in this blog post:
    Anne Stenberg's Blog:
    Navdeep Madan's Blog:

    Internationalization and localization are means of adapting computer software to different languages and regional differences. As our users are distributed in many different regions in the world, this is one of the things that OSIsoft is tackling when it comes to handling different region/language/culture contents in our systems as well as our client tools.


    Though there have been a good amount of work that has been devoted into adding and improving localization features within the PI products, there are still things that you should watch out for when you are using PI Server, PI Clients and PI Data Access Technologies to store, read and display contents with localization features.


    And hence this give me the idea to come up with the new white paper that gives an overview on the different issues that we can encounter in handling localized contents and the available workarounds when using the Server and Client Products as well as Data Access Technologies


    The white paper is now available on the OSIsoft vCampus Library, you can go and download it now.



    The PI Software Development Kit (PI SDK) is a programming library providing access to PI Servers. The product is primarily middleware in that it provides an interface between the PI Server back-end and end-user applications.


    PI-SDK communications


    PI SDK communicates to the PI Server through the local PI-Network Manager (pinetmgr) which in turn opens a TCP\IP connection to the pinetmgr on the PI Server. The protocol that is used at the application layer is referred as PINET3.




    One of the known inefficiencies in PI SDK, more specifically PINET3 put data in 4kb message blocks and  an ACK message is sent for every 4kb message. This causes additional overhead, and causing PI SDK to perform badly especially in high-latency network.


    Improvement in 3.4.380 PI Network Manager


    The improvement of the PI Network Manager in 3.4.380 is mainly targeted at eliminate the known inefficiency. This makes the PI SDK client perform better with PI 3.4.380 compared to PI 3.4.375 and earlier versions. This can be tested by measuring the time taken for an SDK client to complete a call for data from the servers. For convenience in the rest of the blog post, I shall refer to the 3.4.380 PI Network Manager as "PINET380" whereas the older version shall be referred as "PINET375"


    For testing, I created a very simple PI SDK program in Visual C# 2008 with PI-SDK The program is only intended to read archived data from a PI tag for a period of time. The specific SDK call is PISDK.PIPoint.PIData.RecordedValues().


    A way to measure the time taken for the call is to measure the time before the call and after the call. Alternatively, we can perform PI SDK tracing to log the PI SDK connection and operations (see the "Troubleshooting" article in the PI SDK Programming Reference, available on the OSIsoft vCampus Library). We can set the debug level to "Wire Calls" or "Verbose" to see the time spend TCP/IP communicates for the call.


    The parameters that are varied include

    • Latency
      In terms of latency, I have simply varied it changing my connection setup, 1st by connecting to the servers through wireless LAN and also connecting to an external network and connecting to the servers via VPN, which drastically increases latency because traffic is routed to OSIsoft's San Leandro HQ before coming back to OSIsoft Singapore.
    • Number of Events
      This is pretty straight-forward, the start and end time of the PI SDK call is adjusted to retrieve different amounts of data.

    Running the test for a couple of times to get an average of response time, and here's the results that I have


    When running test on a client in the same wireless network as the servers


    When the network condition is good, with high bandwidth and low latency, we can see that reading from PINET380 has slight improvements in comparison to PINET375 but generally it is not significant to affect user's experiences.

    When running test on a client connecting to the servers via VPN through wide area network


    However if we look at the results in a situation where network has high latency (about 400 to 500 ms measure with PING), the results generally still shows that PINET380 performs better than PINET375. The difference would show if you are grabbing large amounts of data out at one shot. 




    When running test on client connects to servers through wide area network with high retransmission


    Another situation that I managed to get is a case where the network is bad enough that a lot of retransmission traffic is observed using Wireshark. In this case, this is really where significant difference in performance is observed. Because of the overhead, PINET375 performs significantly slower compared to PINET380.




    The results do show that there is a significant increase in the performance when we are in a situation with high latency. If you have a lot of users reading data from the PI Server via a high latency network, you probably find it helpful to upgrade the PI server for the performance improvement.


    In closing, I still like to highlight the fact that we are still subjected to other factors that can cause performance to degrade, like server performance, client machine's load. Upgrading to the new PI Server should not be regarded as a move that guarantees improvement in performance in all cases.


    One last thing to note is that even the latest PI SDK release is packaged with PINET375. This means that any API node without PI server installed will only have PINET375. Any connection from a PINET375 node to PINET380 server will only enjoy the performance improvement when reading data from the server but not the other way round, until PINET380 is released with PI SDK.


    New on OSIsoft vCampus Team

    Posted by hanyong Employee Oct 20, 2009

    I am new on the OSIsoft vCampus Team, based out of OSIsoft Asia, in the small island city of Singapore.


    This is my 4th year with OSIsoft, where I spent my last 3 years in Field Service and Tech Support in the AsiaPac region, mostly China. What I enjoyed in these years is working customers/integrators and helping them to find means and ways to fulfill their requirements as much as possible. I joined OSIsoft fresh out of college, graduating as a Computer Engineer.


    In my own experiences, I would say that OSIsoft Techsupport structure is doing a great job in providing users information and assistance about PI products and technologies, but given the nature of a technical support team, transfer of knowledge does not necessary spread out among the users. I like the concept of OSIsoft vCampus for encouraging more information sharing not just from OSIsoft to users and among users, and I am happy to be part of this initiative.   


    I'll be using this space to share some of my thoughts and learnings about PI development and integration, and you will probably see more of my postings in the forum as well =)







    Filter Blog

    By date: By tag: