5 Replies Latest reply on Apr 18, 2010 11:47 PM by Eng-Kiat.E.K.Tan

    PI datalink forn Excel 4.0/Excel Web access Moss 2007

    Eng-Kiat.E.K.Tan

      Hi, My question is regarding the PI Datalink for Excel services. I have contacted the support team. At this stage, they do not have any answer for me so i am guessing maybe somebody in this forum can help me :)

       

      I have a datalink spreadsheet which is working, so i put it on sharepoint and use the excel web access to display the sheet. However, the 'Operation in progress' message keeps on showing up everytime somebody access the site, and since this is a daily production report. I wonder if there is a way to cache the result at the first go, so the next user will be able to skip the operation in progress and the calculation process.

       

      ----------------------------

       

      When user A goes into the page, he will sees this message. “Operation in Progress “. The page loads for 5 mins, and then it appears. He finishes using it and close the page.

       

      Then 5 mins later, he wants to see the page again. He goes into it, and then sees “ Operation in Progress “ again.

       

      My question is; is there a way to prevent this ? cache the result or anything ? so the report won’t have to be regenerated again since in this case, it is a daily report.

       

      Another scenario is user A goes into the page, he will sees this message. “Operation in Progress “. The page loads for 5 mins, and then it appears.

       

      Then User B also goes into the page and he also sees the message “Operation in Progress “.

       

      Then User C also goes into the page and he also sees the message “Operation in Progress “.

       

      So will this crash the PI server for doing redundant jobs ? Is there a feature to combat this ?

       

      Thanks in advance

       

      regards

       

      Tan

        • Re: PI datalink forn Excel 4.0/Excel Web access Moss 2007
          cescamilla

          You could change the recalculate value from automatic to manual, in that way you would need to open it, see the date, and then update. if you can snave it then the next user will get this last value.

           

          Also runing more functions on the PI Server might make it faster.

           

          It might be easir for us if we could see what you are actually requesting in excel, the calculation usually depends on how many values you are requesting and what calculation you are doing with that.

           

           

            • Re: PI datalink forn Excel 4.0/Excel Web access Moss 2007
              hanyong

              Like what Cristobal say, it would probably be easier if we know exactly what you are requesting in Excel. Another aspect that we can look at the how Excel Services work with multiple sessions, and how it do caching for different situations. Perhaps you can post the same question to other forums about Excel Services in general and see if there is any suggestion on this.

                • Re: PI datalink forn Excel 4.0/Excel Web access Moss 2007
                  mhalhead

                  We've used EWA quite a lot; in some cases to the point of breaking. I don't believe that there is an out the box method to cache an EWA view. If you turn the automatic updates off then the display will always revert to the data that was present when saved. This is typically not desired; if the report was saved to the portal a year ago then the users would see data that is a year old. This said you can do some pretty clever things with EWA. An option I can think of is that the when the file is opened it checks it's date and time, if this is older than a certain parameter then the file is updated and resaved (bear in mind users can't save the EWA file normally), then other users would not have to update the file. As I said there isn't an out the box option for this so you will have to break out your visual studio and write an extension. This isn't as hard as it sounds; I did one that allows users to open an EWA file in any version of Excel (even the Excel viewer). To get started this link should provide you with a starting point (http://msdn.microsoft.com/en-us/library/cc540662.aspx). Another option is you could use write an extension that updates the file on a scheduled basis.

                   

                  The question I have to pose is whether this is really worthwhile. If you're worried about the speed then you need to look at what is causing the issue. We have EWA reports that contain >5000 PI tags which open in under a minute. You can improve speed in a number of ways; check your version of DLES (OSI improved the performance in 4.1.1), watch the security settings (this can make a huge difference, Kerberos authentication is faster as you don't get the double hop authentication that NTLM does), don't do complex calculations in the Excel put your calculation into Totalisers, PEs and ACE; only bring back the data required e.g. if you require an average only bring back the average don't read a time series of data and then average it in EWS. Check the latency between the portal and PI server; I always put them on the same subnet connect with a gigabit switch. If you've done all of this and you still don't get the desired speed then consider going 64 bit; Microsoft advised us to move our MOSS SQL servers to 64 bit SQL (either SQL2005 or SQL2008). I've moved one of the servers and the performance improvement is pretty spectacular. The portals are still 32bit; I will only upgrade them when we go MOSS2010 (probably around late 2011 or early 2012; let MS release the first SP).

                   

                  The last option is to use SSRS for the reports. MOSS/SSRS provides caching, scheduling, subscriptions and multiple display formats out the box. The add complexity of SSRS will be far lower than writing your own extensions to EWA and importantly it will make your upgrade path easier.

                    • Re: PI datalink forn Excel 4.0/Excel Web access Moss 2007

                      @Michael Halhead: this was a great post, thanks for sharing!

                       

                      @Tan: are you still experiencing those 5-minute "Operation in progress" delays? Has any of Michael's suggestions helped?

                        • Re: PI datalink forn Excel 4.0/Excel Web access Moss 2007
                          Eng-Kiat.E.K.Tan

                          Sorry for the late reply, gents.

                           

                          Michael, Han Yong, Cristobal, thank you for the insight and experiences in using EWA.

                           

                          Steve, Yes i am still experiencing those delays. The original spreadsheet i am using do have a lot of excel formulaes and references a lot of worksheets, so the delays are still there. Those spreadhseets are an integral part of reporting and is used by several folks ( we kept it in a shared folder ). We at first thought about publishing it on the web ( via EWA ( as a way of making it more 'user friendly' but however due to the slowness/delays, we have to stop using this. In a way, based on your suggestions, i may have to re-engineer the way the sheet calculates and how it use the PI data. The Osisoft support team also asks me to contact Microsoft to learn about sharepoint workflow to resolve the caching solution i was asking about but however, it is considers not very lean and i am not very sure i want to spend a lot of energy on this ( at the moment, a shared folder is still the most attractive solution )But thank you guys for the help, in a way i also learnt and get excited abt a few points- especially Michael's comment on the 64-bit sql server which i am keen to try out !