3 Replies Latest reply on Apr 1, 2016 8:02 AM by pthivierge

    Managing Event Frame from Excel Sheet using PI AF SDK


      Hi everyone,


      PI AF records Event Frame.

      PI historize raw data.

      We would like to use an Excel sheet to manage our Event Frame.


      An Excel application based on PI AFSDK, would be used by end users to validate the data.

      The goal is to send data into SAP (probably using PI data Integrator). For that the validated data need to be store as Event Frame. But we need a way to replicate the Event Frame (generated by Event Frame Generator (for Batches)).

      In our case EFGen records real batches, but end users can validate some recorded EF or can also add or modify some new EF and attributes.


      I imagine to do this kind of Excel Sheet.

      The user has to validate production declaration by day, through Excel, he requests EF (using PI DataLink), then he checks and manages the result, then he can publish the validated EF into a dedicated PI AF database. This validated MES (PI AF DataBase) will be used to send data into SAP.


      Here is that someone has already done this type of development?

        • Re: Managing Event Frame from Excel Sheet using PI AF SDK

          Hello Pascal,


          This is an interesting scenario.


          I think that one approach could be to create an application to :

          • Reads the event frames ("batches") from the source AF database
          • Displays the "batches" to validate with possibility to make additions and corrections
          • Validate the "batches" --> insert event frames in the target AF Database dedicated for validation.


          You would need some sort of code anyway to create the event frames in the target database, so having only one layer instead of many ( datalink + export process ) should be easier to maintain.

          I think that PI Web API could be a good fit, so you could build this as a Web-Based application and that would be very easy to deploy for the users. This requires Web development skills though.

          (AF SDK could work too for both, Server-based web application or a traditional .Net Client Application.)


          These are just some thoughts, hope this helps!

          1 of 1 people found this helpful
            • Re: Managing Event Frame from Excel Sheet using PI AF SDK

              Hello Patrice,


              It is exactlyt what I intended to do.


              But, at the moment, I think that it will be more simple to do that in Excel.

              Excel is a calculation engine. Users who validate the data, need to do some calculations. These calculations are not stored in PI AF.


              For example, they need to sum production by day (with human conditions that are not historized). In PI AF, we will monitore Event by batch. For the daily declaration, they need to modify some data.

              So if we build an Excel sheet interface to do this kind of calculation it will be more easy to do. (In Excel we have a way to read raw data, valide, save validated data and report in one tool.)


              A Web Based application is probably better, but at the moment all cannot be modelized in PI AF.


              I think that I will start do build this application in Excel using PI AF SDF. (What I appreciate in Excel with PI DataLink is that it is quite simple to get EF and Attributes with the PIEFDAT function. It will be interesting to have the same function to write attributes values. I know that PI Builder can do that. But I will have to write it with PI AFSDF.)


              You understand that if I can write validated data into an PI AF DB, it will be simple to send the data to SAP through PI Integrator.

                • Re: Managing Event Frame from Excel Sheet using PI AF SDK

                  Hello Pascal,


                  In this case, I can see three possible solutions, both solutions will need to be deployed on the clients,

                  • Write a COM wrapper to expose AF SDK functions to excel --> see example here AF Wrapper For ProcessBook
                  • Create a VSTO Excel AddIn that will integrate with excel
                  • Create your AddIn with Excel-DNA.  I have used it successfully in the past as I found it easier to deploy (the .xll format is easy), to use.  But this is really a question of preferences.


                  Worth to mention, Visual Studio allows you to create a Click Once installation package.  This allows you to place the installer on a network drive, and after users install it from there, each time they start the application, it will trigger the update mechanism and see if there is a new version to install.

                  2 of 2 people found this helpful