4 Replies Latest reply on Jan 22, 2019 10:28 PM by htakai24

    Manual Data Collection and Visualization through Microsoft Excel

    GVatui

      Our customer has specific needs regarding a manual data collection project from the Mill operators.

      Currently the operators  use paper print-outs form some Excel sheets with lots of cells (dozens..)

      Requirements:

      -> write manual data [ process values, comments, process status tags(drop-down lists), downtimes (timeframes, reason codes, total minutes) ] into PI DA Server or through PI AF.

      -> be able to write to tag annotations (actually we might write the user name to every event sent to PI by a specific operator)

      -> send data to PI with the press of a button

      -> be able to see latest 10 entered values for every item

      -> some cells to be grayed out and not allow manual data entry but only display some connected PI tag data (to help operators asses the current and historic situation better)

       

      We are looking into keeping the same look and feel for operators' user experience hence using Excel sheets to do all the above. What would be the best option in terms of the developer technology to use behind?

      Form some other PISquare posts, it seems that several options are available:

      -> Use PI DataLink functions.

      Some questions here: Can PI DL show an array of historical values for a tag in a pop-up window when user clicks on a cell (for cleanliness, we would have this in a separate sheet I believe)

      Can PI DL send values to PI (reading from a cell (any type pof data - numbers, timestamps, digital codes, strings) and write annotations at the same time ?

      -> use PI SDK (I understand this is deprecated..)

      -> use PI API (Is this deprecated as well ?)

      -> use wrapper arounf PI AF SDK (I understand this is not 'production'-ready, although there are wrappers available..)

      -> VBA code to read and send data to PI using PI WebAPI (webAPI wrapper for VBA..) Is that something OK for a production environment and if so, does OSI have a production-ready WebAPI wrapper for VBA ? If so, is Kerberos delegation absolutely needed to work for this ? 

      -> reading data with PI DataLink and writing manual data to a REST service (can this work with PI UFL Connector ? I believe it had the option to expose a rest service to ingest data... can then Excel VBA just call the REST service (how?)

      -> other ideas... VSTO add-ins, etc  (note: we are considering exporting data to CSV and using PI UFLs..)

       

      Note: PI Manual Logger is not an option here (we evaluated it with customer) because of the lack of customization of the data entry screen (both Web and Desktop versions)

       

      THANK YOU !

        • Re: Manual Data Collection and Visualization through Microsoft Excel
          Lal_Babu_Shaik

          Hi Gheorghe

           

          I would suggest below options

          1) PI Vision using custom symbols (Manual Data Entry)

          extenstibility Manual data entry : PI-Coresight-Custom-Symbols/Community Samples/OSIsoft/manual-data-entry at master · AnnaPerry/PI-Coresight-Custom-Symbol…

          https://github.com/AnnaPerry/PI-Coresight-Custom-Symbols/tree/master/Community%20Samples/OSIsoft/manual-data-entry2) Another option Web application using PI Web API where you can post data using web forms. 

          3) UFL Interface: Export data into specific shared location and UFL interface updates the data to PI Data Archive.

          4) Datalink older version : 1501OSI8 - How do you send or write data to the PI Server from an Excel Spreadsheet

          • Re: Manual Data Collection and Visualization through Microsoft Excel
            Eugene Lee

            Requirements:

            -> write manual data [ process values, comments, process status tags(drop-down lists), downtimes (timeframes, reason codes, total minutes) ] into PI DA Server or through PI AF.

            -> be able to write to tag annotations (actually we might write the user name to every event sent to PI by a specific operator)

            -> send data to PI with the press of a button

            -> be able to see latest 10 entered values for every item

            -> some cells to be grayed out and not allow manual data entry but only display some connected PI tag data (to help operators asses the current and historic situation better)

            As you mentioned that you want to maintain the same look and field with Excel. I will recommend using PI Datalink for data input and also displaying data.

            Let me address each requirement that can be addressed by PI Datalink.

            1. Yes. PI DataLink

            2. No. BUT I do not recommend annotating every archive event. Annotations are meant to be used sparingly. Instead, you should create a string PI Point to store the names of the operators at the same timestamp as the process values tag. 105988 - Every archive event annotation consumes 8 bytes of virtual memory for the Archive Subsystem 

            3. Definitely yes. Look at PIEXAM32.XLS or PIEXAM64.XLS for an example.

            4. Use Datalink Compressed Values PINCompDat() to do that. Specify -10 to retrieve values backwards.

            5. Your VBA macro just do not have to target these cells and no values will get sent from them.

             

            -> use PI SDK (I understand this is deprecated..)

            -> use PI API (Is this deprecated as well ?)

            -> use wrapper arounf PI AF SDK (I understand this is not 'production'-ready, although there are wrappers available..)

            -> VBA code to read and send data to PI using PI WebAPI (webAPI wrapper for VBA..) Is that something OK for a production environment and if so, does OSI have a production-ready WebAPI wrapper for VBA ? If so, is Kerberos delegation absolutely needed to work for this ?

            -> reading data with PI DataLink and writing manual data to a REST service (can this work with PI UFL Connector ? I believe it had the option to expose a rest service to ingest data... can then Excel VBA just call the REST service (how?)

            -> other ideas... VSTO add-ins, etc  (note: we are considering exporting data to CSV and using PI UFLs..)

            Now let me address the other options you mentioned.

            1. Deprecated

            2. Ancient

            3. Gone from TS website. Unsupported. If you manage to find it, use at own risk.

            4. PI Web API client libraries are unsupported for production. Use at own risk.

            5. Possible but you introduce an extra complexity

            6. You will have to find some way to distribute the VSTO addins to your client computers. Troublesome.

            • Re: Manual Data Collection and Visualization through Microsoft Excel
              htakai24

              Gheorghe,

              We had a very similiar requirement a few years ago.  Some of our operators were using an Excel spreadsheet to capture daily generation data, engine hours, fuel consumption, CB operations, fuel deliveries, etc., without saving this data in PI.  They simply used the Excel spreadsheet to input the data, and would then save a copy of the form every day.

               

              We decided to take the existing Excel form that the operators had been using for years, and with some PI programming behind the scenes, were able turn the spreadsheet into a big input form - but with the data now being sent to PI.  The spreadsheet retained the exact same look as the original, but with the inclusion of a 'SUBMIT' button to send the data to PI.

               

              In addition to sending the data to PI, an operator can easily change the date on the form and bring up historical data in the same form.  We've implemented error checking on many of the input cells and support annotations on a few.

               

              The biggest benefit of archiving the data in PI, is that this daily data now drives our monthly reports.  Prior to archiving the data in PI, all of the monthly reports were done manually, and took many hours to compile. Now, the monthly reports are automatically completed as soon as data for the last day of the month is submitted.

               

              To answer some of your questions, we used some PI DataLink functions, some VBA and PI-SDK.  As Eugene mentioned, the PI-SDK is deprecated but it works fine for us at the moment.