5 Replies Latest reply on May 4, 2015 7:30 PM by jlickver

    slow excel datalink

    jlickver

      Hi all,

       

      I have created a report in Excel using datalink.  there seems to be a point where things get too much for the system to handle in a reasonable amount of time.  my report takes in excess of 30 mins to calculate.  there are several calculations, but 30 mins?  almost like I went back in time to running Mechanical Desktop on a x486, in 3-D!

       

      anyone have any thoughts to how I can find a troubled calc or something??

       

      Cheers - J

        • Re: slow excel datalink
          Roger Palmen

          There is no easy way to solve this. So some general approaches to find the area that is causing issues: limit time ranges, switch to manual calculation and check performance per worksheet. Within worksheets, check individual calls to DataLink.

          For each, try to find out if it's Excel, the network, PI, archives, sheer volume, calculations that is causing the load.

           

          Any fast car runs slow on a bad road...

          • Re: slow excel datalink
            aseck

            Hello Jason,

             

            To begin with, which kind of calculations are being run in those reports? Totals? Averages? How many data items (PI Points or PI AF Attributes) are we talking about? About how many values over the time range of the reports?

             

            If doing extensive calculations, one thing that may help is to do the calculations directly in the PI Data Archive server (using PEs or Totalizers, for example) or in PI AF using the PI Analysis Service, then store the results in PI Points. This basically amounts to shifting the burden of these heavy duty calculations to the server, however it does help with the client applications (in this case, PI DataLink) performance.

             

            Something else that may help is to make sure your report runs bulk queries by grouping several cells containing a data item in one function call instead of several calls with one data item each.

             

            For example, do this one time:

             

            Not this several times:

             

             

             

            A little bit on the architectural side, are you running the latest versions of PI Datalink/PI Data Archive (aka PI Server)/PI AF? Is your PI DataLink report collecting data for PI Points or PI AF Attributes?

              • Re: slow excel datalink
                jlickver

                Thanks for the quick responses guys!!

                Alassane, I think you may have pointed out what I am doing wrong.  It makes perfect sense to group - I currently have every query to PI as a singular.  I am not a master at the grouping thing, so I will have to experiment.  Not 100% sure it would apply to what I am doing... again, will have to try it out.

                 

                in short, the report is a list of about 150 points each of which I want a monthly total.  then just a wee-bit of massaging for averages and sums.

                 

                To answer the other points...

                latest datalink (5.1.1 I believe)... I will double check when my last manual calc update finishes... lol.

                 

                there are maybe 1500 queries to PI and then I have some simple "sums" and/or multiply or divide with the received data...  hmm, didn't realize it was 1500 or so!!

                 

                also, will this make a difference?? ... I have the "yet to be" months with queries that do not have data yet (we are only starting May of course)... do the "yet to be" months slow down performance perhaps?  suppose, I can try that too... once my calc finishes!!

                 

                Cheers and thanks again for your help!

                PS... agreed Roger... I live in Windsor Canada and we are all about the bad roads!!

                1 of 1 people found this helpful
                  • Re: slow excel datalink
                    aseck

                    If you don't want to wait, you can set the Calculation Options to Manual before opening the troublesome workbook. The procedure is outlined here.

                     

                    Also, let's not get started on bad roads already ... we have one or two in Quebec, Canada that I could tell you about

                      • Re: slow excel datalink
                        jlickver

                        I think I found the culprit... it appears to be all the queries for the months that do not have data yet.  I deleted those and the sheet came back "alive"!!!  Will monitor the performance as the year progresses and I add them back.

                         

                        Thanks again for the help Alassane and Roger!  I'm back on the productive track once again!

                        Cheers!

                        ok - I won't go down the path of bad roads, it's Monday and will keep it positive