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.