2 Replies Latest reply on Jun 7, 2011 12:52 PM by andreas

    Call an AF Data Reference Attribute from Excel

    mrpoisse

      Hi,

       

      I like to use Excel as an interface to show data, because the end user can easily adapt it to its particular needs.

       

      I am programming a custom data reference in AF (with VB.NET) in order to calculate the OEE for a given period.

       

      The goal is for the Excel to call this reference, given a start and an end timestamp.

       

       

       

      my challenges:

       

      1- It appears that AFSDK is not available in VBA. What would be the best option to call the attribute from Excel?

       

          OPTION 1A: Write a custom dll that connect to AF

       

         OPTION 1B: Connect to AF through another method such as OLEDB

       

         Other OPTIONS?

       

      2- For the Data Reference to return a good OEE value, the users needs to specify timestamps. How easy is it for the user to provide parameters to the data reference call?

       

         OPTION 2A: I could store the timestamps in Attributes and the user modifies those attribute before he calls the Data Reference calculation... however we might encounter issues when 2 different users call this at the same time.

       

         OPTION 2B: Given that I have a solution for #1, I could create a dynamic unique attribute in AF, which would contain the timestamps of the query, then delete this temporairy attribute once the value is returned.

       

      OPTION 2C (answer from OSIsoft's support team): On the client side, you can use the GetValue(AFTimeRange) on an AFAttribute object configured in AF with your custom data reference. This method has an argument for the desired start and end time that is set in an AFTimeRange object. The custom data reference can then use this AFTimeRange object to apply the user desired time range to the OEE calculation.

       

      Other OPTIONS?

       

      Thank you for your help

        • Re: Call an AF Data Reference Attribute from Excel
          Ahmad Fattahi

          You can use Visual Studio Tools for Office (VSTO) add-in for Microsoft Office to access .NET functionality (AF SDK in this case) in the Excel environment. Also, you can get query-type access to PI AF using PI OLEDB Enterprise. For more information please see [DEAD LINK] this thread and this one.

           

          in terms of your other question, it depends on how flexible your OEE definitions are. If they are well-defined-enough to be captured by an AF attribute or formula that would be the cleanest way. Creating temporary attributes in PI AF and deleting them would seem an unnecessary load in your architecture. If the time ranges and calculation types are too flexible to be defined in PI AF, you could get the data for the desired time range from PI AF and do the rest of your calculation in your .NET code.

            • Re: Call an AF Data Reference Attribute from Excel
              andreas

              Marc-Andre Richer

              Other OPTIONS?

               

              Marc-Andre - what is the timeframe? You might want to wait:

               

              PI System Roadmap

              Upcoming Releases
                Product Version Date Description Features
              PI DataLink 5.0 Q4 2011 – Q1 2012
              PI DataLink 2011 brings support for the PI Asset Framework allowing you to work with both assets and tags. A new PI System Search enables you to find the information you are looking for quickly and efficiently. PI DataLink 2011 is the most compatible version of DataLink to date and supports 32-bit and 64-bit versions of Microsoft Excel 2007 and 2010.
              • Accept PI AF attributes as equals to tags
              • Integrated Search & AF Guided Find
              • 64-bit Support