@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.
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:
=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?
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.
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.
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.
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.
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.
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.
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.