This section is the most important part of the course and sharing this information is
the main reason this course was created. In this lesson we will extract analytics
performance information from the PI System convert it into a convenient
format using Microsoft PowerShell and then summarize the data in Microsoft
Excel to gain understanding about how our analytics are performing.
If you work through the entire course, at the end we will run through this activity again to
see the results of changes made in other sections of the course. The first step to analyzing the
performance of our analytics is to export the performance data from PI System Explorer. To
accomplish this we'll launch PI System Explorer, we'll go to the management tab in the lower left
and then looking in the lower right under pending operations we'll right-click and select view
analysis service statistics. This window gives you performance information about a number of
your analytics, but it's not the easiest format to look at at a broad scale. So instead instead
of just looking at the individual analytics themselves, we'll click Save and save the
resulting text file to our C class folder and then we'll analyze that file later in Microsoft Excel.
Next, we'll use Microsoft PowerShell to convert the data from XML to a more easy to analyze format
like CSV. So first we'll launch PowerShell and if you haven't used PowerShell before
it's like a upgraded command prompt with some more functionality that we'll take advantage
of. Once we're in Microsoft PowerShell we'll use the change directory command to change to c-class.
Once there our next step is to import the XML file that we exported in the previous step into
Microsoft PowerShell. For all the content in this class we've copied the PowerShell and
Excel content that you'll need to use into an file called PowerShell and Excel content
to copy dot text in the c class folder. So we'll open this file Microsoft PowerShell. We'll press
ENTER to execute the command, so now inside this XML document variable we have the contents of
our analysis service statistics dot XML that we exported from PI System Explorer earlier.
Next, we need to store the relevant performance information into a different variable;
basically the information that we're interested in called eval summary. So we'll take the next
line from our copy and paste file and we'll copy that over and then execute. Next we're going to
parse that data and export it into a CSV format so that we can analyze it in Microsoft Excel. So
if we click back into our PowerShell and Excel content to copy dot text file and we'll copy
the third line there into Microsoft PowerShell. This line parses the data in the eval summary
variable and exports it to a CSV file that we can then analyze in Microsoft Excel. Once
that's complete you should see a AnPerfStats dot CSV file created in your C class folder.
Next, we'll take a look at this AnPerfStats dot CSV file inside Excel, so if you double-click on
that file it'll launch Microsoft Excel. If the Microsoft Office activation wizard comes up just
press cancel and then once you're inside this file you'll see informational statistics about
your analytics. So in the first column will be the display name which is which analytic it is,
specifically the element that the analytic lives on and the name of the analytic itself. You'll
see the average elapsed which is the amount of time it takes on average for the analytic to
complete. The maximum elapsed time which is the max amount of time it took to complete
during the statistics. The average lag which is the lag between when the analytic should have
executed and when it did execute and the maximum of that lag as well. As well as the duration of
the data export and the number of times that the analytic executed during that period.
Once we have this file open in Excel we'll click Save and say no to convert it to an XML format.
We'll save it in our c - class folder and we'll change it to an Excel workbook. We can leave it
the same name AnPerfStats dot xlsx and click Save now we want to enhance this data with a
few more columns that help will help us in our analysis the first thing we want to add
is a template column indicating what analysis template was used to create this analysis. So
we're going to insert a new column to the right of the display name column and we're going to
call it template and the way we're going to find the analysis template is going to be using Excel
parsing to find the data inside the brackets in the first column. Say to accomplish that we've
put that information into our PowerShell and Excel content to copy dot text folder.
So we'll copy the template column text and then paste that into the first row of our template
column. And you can see that it pulls out revenue from cell a2. Now we're going to fill that down by
double clicking on the square in the lower right and now you can see that all of our templates
are visible for our various analyses. Next we're going to create another column that is our average
triggering interval, so to the right of execution count we're going to create a column called
average trigger and we're going to get the average trigger time by dividing the data duration by the
number of times that the analytic executed. So we've done this once again in our copy text file,
so it should be G 2 divided by H 2 I'll copy that and paste into our column and then fill down.
You can see here that most of our columns, most of our analytics are executing approximately
every 10 seconds but it varies from analytic to analytic. Next thing we want to calculate
is something called weighting factor this will be a combination of both how often an analytic
executes and how long it takes to execute when it does. So to do that we'll click into column
J and we'll call this weighting factor. Going to be c2 divided by I2 so we'll copy this text
from our text file, paste it into the column and fill down. So that's dividing the average elapsed
time by the average trigger to determine a weighting factor for our calculations.
The next step is to build a pivot table; we'll start by creating a new tab by
clicking the plus button at the bottom of the Excel window. We've created our new tab,
we'll right-click and rename and call this pivot table. On a new sheet on the insert ribbon,
we'll select pivot table and here where it says select a table or range we'll click over to our
perf stats column and select all the data. Once we've done that click OK and you should see all
of our fields from our initial analysis show up in the pivot table fields column. The next step is to
grab the template from the pivot table fields and drag it to the rows area in the lower right. This
will show all of our analytic templates in the first column down the left side of the screen.
Next we're going to add some values, so we're going to drag average elapsed down
to values and we're going to change this to an average by clicking the drop down,
clicking value field settings and selecting average. Next we're going to drag max elapsed
down to the values area and we'll change this to a max. So that we can see the maximum of our max
elapsed. Next we'll do the same thing with average lag and max lag, so we'll drag average lag down
select the drop down change it to an average and we'll drag max lag down and change it to a max.
Next we'll drag average trigger down to the values area and we'll change it to average of
average trigger. And lastly we'll drag down our weighting factor and we'll change this
to average of weighting factor as well. So your pivot table once you've dragged the fields down,
should look something like this. You'll have your analytics along the left side
then your average of average elapsed, your max of max elapsed, average of average lag,
and max of max lag. As well as average of average trigger, and average of waiting
factor. Next we're going to enhance our pivot table with a few of the Excel functionality.
I'll show which analytics are causing problems. The first step for this is to sort the pivot
table by the sum of weighting factor. We're going to highlight the row labels column and
select the drop-down and then select more sort options and we'll select descending Z to A by
average of weighting factor and then click OK. Now your pivot table should be sorted
by the weighting factor column, which is the most significant analysis of our analytics. After that
we're going to add some conditional formatting to highlight the analytics load caused by these
various analytics, by selecting the average of weighting factor column, excluding the header,
then on the home ribbon selecting conditional formatting, data bars and the data bars of your
choice . This will highlight which analytics are the most expensive in our system.
As you can see in my example here high wind speed calculations is by far the
most expensive part of our analytics, closely followed by our lost power 30-day average.
The goal of the pivot table we created in this activity is to be able to see which
analytics are the source of the overall performance problems with asset analytics.
In other words we then know where to focus our efforts on improving performance. In particular,
the weighting factor column is useful in determining which analytics to look
at. This is because the weighting factor column that we created is a combination of
both how long the analytics takes to execute combined with how often it could execute.
This is necessary because sometimes there are analytics that take a long time to execute
but don't execute very frequently and therefore aren't necessarily worth it looking at to improve
performance. One key factor to keep in mind is that the performance information in PI System
Explorer only shows the five worst performing instances of each analytic template. This is
normally sufficient for our purposes but in the scenario where you have thousands of instances of
a particular analytic template, the pivot table we have built here might not be sufficient for your
purposes. One way to deal with this scenario is to add an additional column indicating how many
instances of each analytic template there are and factor that into the weighting factor by
multiplying it in the calculation. This will weigh the analytics with more instances more heavily.
Another way to deal with this scenario is to contact OSIsoft technical support.
Technical support has a special tool that will extract performance data
for all analytic instances. The output of this tool can be used to create a similar
pivot table but with more detail about how many instances of each analytics there are.