OSIsoft: Get time-summary calculations with PI Calculated Data function (PI DL 2013 Excel Add-in)

[00:02] What is the PI Calculated Data function?
[00:42] Why use PI Calculated Data instead of MS Excel functions?
[02:06] Add a Calculated Data function: select data item
[02:36] Add a Calculated Data function: Specify start & end time
[03:00] Add a Calculated Data function: Link cell references
[03:25] Add a Calculated Data function: Specify time interval
[03:51] Add a Calculated Data function: Show time stamps
[04:31] Formatting tips for Calculated Data functions
[05:22] Copy & paste Calculated Data functions
[05:56] Speed up your spreadsheet by clearing raw data

The PI Calculated Data function allows you to calculate the Minimum, Maximum, Standard Deviation, Range, Average, Count, or Mean of the PI System's timeseries data.

Video content is copyright of OSIsoft, LLC © 2013. All rights reserved. Any redistribution or reproduction of part or all of the contents in any form is prohibited other than for your personal and non-commercial use.

---

Closed Caption:


The Calculated Data Function in PI DataLink
allows you to do quick, basic calculations
with things like taking the maximum-the minimum
over a time range for a tag. So what I have
here is I have some PI
tag--CDG158 in this case--and what I've
done over here is I've asked, I want to know what
was the daily maximum every day for a week?
We can see every day is represented
here and then at a different time each day
calculated has found the maximum.
And over here, this is an example of what I might try to
do if I wasn't going to use calculated data. So what I might
do is I would use a compressed
data query--so this is a compressed data query right
here--to just bring in all the raw
values for this tag over this week. And you'll
see that now I have seventeen hundred values on my
sheet. And you can imagine if I started to
do this for maybe twenty tags all at once this could be a lot of
data on my sheet; it's going to make my sheet bigger, it's going to take it
longer to load and the performance
as far as interacting with the PI Server is going to be
lower. And also if I want to know what was
the maximum value over this week, in Excel
I can ask for it using the max function. That
works but there is one limitation with
it, it will give me the maximum but it's more
difficult to find out exactly when this maximum happened
in this list of data. The thing is a lot of Excel functions
are extremely powerful but they are not built around the idea that you
have time based data whereas all the PI
System calculations underneath "calculated data" are built
around the assumption that you have time based data.
So I can very quickly do the
same calculation with calculated data that I'm doing
right there with Excel
right away in one cell. So in one cell I can know when the
maximum was, what it was and I don't need to have seventeen hundred
values on my sheet and I get to know exactly what
time this maximum happened--really easily, really painlessly.
So that's what we're going to see right now. So I'm just
going to delete all of this so you can get a sense
of what I'm doing here and it starts off
with--I'll just delete this too. I want to know what's the daily
maximum and we'll see some other calculated data queries.
First I need to know what data item I'm after so I'll search
and I'll go into my PI
Server and I'll search for a PI
tag and I find it and I'll just click
"okay" right there and the start time, well I'm interested in what's happening
over the last week so "t minus 7d".
So today minus seven days from now.
Until today so this morning at
midnight.
And then what's the daily maximum? So the raw values; well we saw how to
do that, that is just with compressed data.
But what we're interested in is what to do with calculated data so I'll just click on
"calculated data" here and then it asks for
much the same thing. So data item--that's going to be the
data item--the start time will be the start time, the end time will be
the end time here. So I click in "data
item" field and I click on my data item "B2"
and click on "start time", click on my start time "B3",
click on "end time" field and click in
B4 right there. Now the time
interval, that's going to be how often do I want this interval to
be so I'm asking for the daily maximum so I'm going to type
in "1D" for one day. I could also put this
on my sheet and just refer to that but I'm just going to type it in this field
here. And then I need to say
what calculation mode I want. Well I'm interested in a
maximum so I'm going to select "maximum" from that drop-down menu
there and I'll say
"apply" and I get a number of maximums. Now I'm interested
in what time did these maximums happen
at so I can click on "show min or max
time" and now I can see the time
at which this maximum happened.
If I were to show start time and end
time this shows me the start time
of this frame that I'm looking at--which day
am I looking at to see the maximum--and when does that
day end. So this is basically the eleventh of
March is this row and then the twelfth of March is
this row and the thirteenth of March is this row.
And on the eleventh of March at
six fifty-three am the maximum for the entire day
happened right there.
Now if I want to see other calculated
data items I can put those in really
easily. So this is the maximum, what if I wanted to know the
minimum and maybe the
--well what other options do I have
here? The range; so maybe the range
for maximum and minimum; so the range.
We can ask for different calculations that we want and all it takes
is we'll take a calculated
data query--click in "calculated data" and get a
calculated data query and link it up in much the same way.
So I click in "data item", click here, click on "start
time"--my start time field would be B3--click on
end time, click on B4, time interval
that's one day again--1D
and I'm going to say
"minimum" and I'll say "apply" and I get the minimum for those
days. And if I want the range all I need to
do--well I could go through the same wizard; that works--or
I could just take this field here, copy
it and paste it and now
I have two calculated data queries and all I have to do now
is just change this to the range.
There.
So I have the range that this tag went
over and if you want to check this I could just say the range is equal to
this cell minus this
cell, scroll this down and you can see that the range is being
calculated correctly. With calculated
data you can very easily do all of these
calculations on the PI Server so you don't need to
have your spreadsheet filled, and filled, and filled with lots of raw
values that then you're doing calculations with Excel calculations
on. It can all be done on the PI Server which is
faster, you'll have a lighter weight spreadsheet, you can do all sorts
of powerful time range calculations; things that aren't
available to you in the standard Excel functions.