Now that we know how to make use of large data sets in PI DataLink let's talk about
some considerations to avoid performance issues in our spreadsheets. Under the hood PI DataLink
takes the information entered into the tasks pane for a given function and creates an AF
SDK call. So the more PI DataLink functions you have the more AF SDK calls must be made
to retrieve data. For this reason it is important to keep in mind how you can minimize calls when
building out or modifying large workbooks containing many hundreds of formulas.
This lesson will teach you some best practices when troubleshooting why workbooks containing
many PI DataLink functions open or recalculate slowly. You will learn how to minimize unnecessary
recalculations quantify the number of calls made by PI DataLink to its data access layer the AF
SDK and finally reduce the number of calls necessary by using cell ranges as input and
or bulk data retrieval methods. You can find more detailed information on architecture and
data flow of PI DataLink in the PI DataLink playbook on the customer portal. We'll go
over how to find that in a later lesson. So how can we quantitatively measure the
background activity required to recalculate the DataLink functions in this workbook?
Specifically how do you log the calls that PI DataLink makes to its data access layer
the AF SDK to retrieve PI System data? We're going to make use of a tool called AF Get
Trace. We'll start by opening Excel and open a blank workbook. We'll go to file explorer
and go to the PI home 64 folder. Under the AF folder you'll see the utility called AF
Get Trace. We're going to open up task manager to find the process ID for Excel. It should be
listed under the details tab but you can use select columns to make sure you see it. We'll
find Excel. EXE and take a note of its process ID. In AF Get Trace will put it into the enable
whitelist field. Under keywords we'll choose server and connections and then we'll press
Start. This is going to start recording the activity from the AF SDK from this machine.
We'll open our spreadsheet called performance exercise, notice that it's kind of freezing up
when we open this sheet. Now that it's open we'll take a look at what we see in AF Get Trace. As you
can see it logged many calls and if we take a look we'll see all those calls recorded in a log file.
Let's try something different, let's go to Excel options and under the formulas
tab we're going to choose manual workbook calculation. Once we do this we're going
to close the spreadsheet we have open making sure not to close Excel totally;
you want to make sure that Excel keeps the same process ID.
I'm going to rename the log file and run the trace again. Now let's see the difference when
we go to open the same Excel file. As you can see that opened much quicker. Let's stop the
trace and let's go in and compare what we see for these two options. I'm gonna open both log
files in notepad plus plus. As you can see there's a huge difference in the number of
calls AF SDK had to make between the manual and automatic options. We can keep this to
only times when it's necessary rather than when we open, close or make edits to the workbook.
Let's try something else. I'm gonna rename the log again this time to update and start
the trace. I'm gonna go back into options and turn back on automatic recalculation.
Now we can see the workbook recalculate again. But what if we type in something in cell N3?
This is unrelated to the function, but still as I type it in and delete the value we can
see some lag. Let's stop the trace again and let's open it up and compare it. This
trace is even longer. That's because with automatic recalculation, sometimes any
change will trigger a recalculation. We'll talk more about that later.