10 Replies Latest reply on Dec 2, 2014 3:55 PM by Gregor

    Workaround for very slow connections

    henderson

      Hello you all. Hope I'm posting this at the right forum.

       

      I have a Excel sheet used daily and it has something around 60 PI TAGs which I have to load every day.
      The problem is that the user is on an oil rig so the internet connection is really, really, really slow.
      If I use the usual "PIarcval" from PI Data Link, Excel sheet takes 10 minutes up to 1 hour to load completely.

       

      I am trying to find a way to decrease this time.
      I found a crazy technique here: http://www.excelhero.com/blog/2010/05/a-real-time-advanced-excel-chart.html
      And I could make it work. The time went from 10 minutes to 10 seconds. This tell me that the internet connection problem is the time response and not bandwith.

       

      But this is not a good solution. It's some crazy implemention I did in this sheet.
      Is there a way to deal with this is slow connections using just PI DataLink or something like this?
      I think that if I could do a bulk request, and request all PI TAGs at the same time, I would have a nice time response.

       

      Hope I made myself clear.
      I am not a native english speaker, so I sorry for my mistakes while writing.

        • Re: Workaround for very slow connections
          mhamel

          @Henderson: Is a static report that the users are reading every day? Do they need to interact with it in a dynamic way?

           

          We would need to better under your use case before giving you suggestions. Can you describe more? Caching techniques could be helpful in your case for example.

            • Re: Workaround for very slow connections
              henderson

              The user must create a daily report.

               

              He does it every day at 7pm.

               

              Some of the data of this report come from TAGs from PI, so we are using datalink to catch this value.

               

              It's a bunch of data that we fetch using one of this two functions:

               

              =PIArcVal("CAR_301086_FT_2615_08SP";"26/08/2014 18:20:00";0)

               

              And

               

              =PIAdvCalcExpVal("CAR_301086_FT_2615_08SP";"25/08/2014 18:20:00";"26/08/2014 18:20:00";"mean";"time-weighted";"interpolated";"10m"; 0; 1; 0)

               

              But the sheet has something around 50 cells with this PI Datalink formulas, catching values from many tags, but all at the same time.

               

              Did I made myself clear?

               

              Thank you!

                • Re: Workaround for very slow connections

                  Hello Henderson,

                   

                  What's the version of PI DataLink you are running for this report?

                   

                  What's the version of the PI Data Archive (PI Server)?

                   

                  The recent PI DataLink release 2014 SP1 is built against AF SDK Client 2014 R2 and requires .NET Framework 4.5 which indicates the developers have used the Rich Data Access (RDA) AF SDK assembly. I am not sure if at all, PI DataLink 2014 SP1 supports bulk calls against the PI Data Archive and if so what DataLink functions would use bulk calls. AF SDK bulk calls require PI Data Archive to be of version 3.4.390 or later. Please see this other discussion for reference.

                    • Re: Workaround for very slow connections
                      henderson

                      PI server is 3.4.390.18.

                       

                      Is there a way to use AF to do it?

                       

                      I've never used AF. I just know I have this ribbon on my Excel.

                       

                      I am not a PI professional, as you may have noticed. I am almost an end user just trying to improve some jobs here.

                       

                      I tried to get some help with the team responsible for PI in my company but I couldn't find anyone with a solution for my problem.

                        • Re: Workaround for very slow connections

                          Hello Henderson,

                           

                          Helping with an out of the box solution should always be preferred upon re-inventing the wheel.

                           

                          Can you please check with "Add or Remove Programs" what version of PI DataLink is installed?

                           

                          I'll see if the product manager of PI DataLink can help us with an answer to the question if and what bulk calls are implemented in the latest PI DataLink Release.  

                            • Re: Workaround for very slow connections
                              henderson

                              PI Datalink 2013 v5.0.0

                               

                              Thank you for your help Gregor!

                               

                              I managed to make it work better with that solution I've posted on my first post, but it's a crazy solution, using a lot of VBA and VBS skills to make it work. And it's a very specific solution. If we can get to a way to solve this problem using built in functions, would be much better!

                               

                              Thank you again.

                                • Re: Workaround for very slow connections

                                  Hello Henderson,

                                   

                                  Currently none of the functions in PI DataLink 2014 SP1 uses bulk calls. We’re migrating to bulk calls for DataLink 2015.

                                   

                                  A few options:

                                   

                                  1)     Remote desktop or Citrix to a machine closer to the PI server

                                   

                                  2)     Host the report in PI DataLink Server and change parameters as needed. Access via SharePoint page.

                                   

                                  3)     Do the calculations as AF attributes

                                   

                                  4)     Take a look at the construction of the report to minimize data calls

                                   

                                  Generally speaking the performance of this type of query is going to be 2 times the number of roundtrips times the latency of the connection.

                                   

                                  Is it a satellite link with about 3000ms latency? If so, try and reduce the amount of calls.

                                    • Re: Workaround for very slow connections
                                      henderson

                                      Hello Gregor.

                                       

                                      Thanks for all the information.

                                       

                                      About your suggestions:

                                       

                                      1) It's one of the options we give to our users, but Citrix doesn't have a very nice performance, so they end up not using it. As I said, the internet connection is really slow.

                                       

                                      2) I suggested this to our PI team, so they can study a way to implement this. Study is in progress.

                                       

                                      3) I have no idea how to do this. I've never used AF. To you have some literature to indicate as a start point?

                                       

                                      4)The only way I can think to reduce the amount of calls is to reduce the number of tags. But I can't do this.

                                       

                                      Its a satellite link with a big latency. I don't know if it is 3000ms, but it is something around this value. There is nothing much I can change on the structure of the report. Unfortunately it must have all those 60 tags, sometimes even more. What I was able to do was to make Excel require multiple tags at the same time, as a "multithread" action using a crazy implemention, this way I can "bypass" the latency problem.

                                       

                                      Since there is still no way of doing this with built in functions, I will look back at this issue once we have DataLink 2015.

                                        • Re: Workaround for very slow connections

                                          Hello Henderson,

                                           

                                          I've more or less posted what I've received from the PI DataLink product manager.

                                           

                                          # 3 is about calculated data points. Asset based Analytics is just one but today the most recommended approach because most future proof solution. This recording of Builders' Café Webinar Series - Asset Based Analytics provides you with a good overview and introduces how calculations become set up. Other approaches are the more classic ones existing with the PI Data Archive (PI System). Available calculation engines are PI Performance Equation Scheduler, PI Advanced Computing Engine (PI ACE) and PI Totalizer subsystem. My personal preference would be using PI Performance Equations or with more complex calculations PI ACE. PI Performance Equation Scheduler (and PI Totalizer Subsystem) belong to the so called server applications and are described in the PI Server Application User Guide. On a machine with PI SDK installed, PI Performance Equation Reference (PEReference.chm) can be found at %PIHOME%\Help\. PI ACE mainly consists of PI ACE Scheduler, PI ACE Manager and PI ACE Wizard as plug-in to Visual Studio. With PI ACE 1.x Visual Basic is the programming language and Visual Studio 6 the development environment. PI ACE 2.x supports different versions of Visual Studio .NET between 2003 and 2010 or even later. The programming language is VB.NET.

                                           

                                          You've posted your question in a forum that has "development" in its name and at vCampus we support users in developing applications against PI. Therefore and in order to list all available approaches, you can develop an application that makes use of most recent data access products (AF SDK). Excel VBA is not really an option from my point of view but I can think of an application that uses a bulk call to get the data - optimally in a single call and a) stores them in a comma separated file (CSV) for further treatment or b) uses OLE automation to write the data into an Excel sheet.