OSIsoft: Totalize and Integrate with the PI Calculated Data function (PI DL 2013 Excel Add-in)

OSIsoft: Totalize and Integrate with the PI Calculated Data function (PI DL 2013 Excel Add-in)


We’re going to cover an advanced feature of calculated data and that’s the Totalization function of it. Now in another video we covered how to use Calculated Data to do really quick calculations of maximum-minimum range but onto Totalization. So what Totalization allows me to do is essentially take an integral of something. So what I have here is I have a tag–and I’ll just move it over– so this is my flow rate. I have a flow rate in gallons per minute and it’s defined by this PI tag right here. And I’m interested, as I would be, well given this is my flow rate how many gallons did I produce over a week? So here is all the data I have; I have a lot of data. This is just the raw value that I’ve been recording for gallons per minute of my flow rate and I’m interested in if I were to graph these –I’ve got a graph right here–you can see if I were to graph these we can see what the values were doing over that time. So this is the flow rate over some period of time and you can see that it ranges between three hundred and fifty but this is essentially, this is just gallons per minute right there. And what I’m interested in is whatever it is that I need to get out I want to know what was the gallons right? Now what I need to do is I essentially need to take an integral of this data or –said another way–I need to find out the area of underneath the curve here. That will allow me to get gallons over this period of time. So this is a week– if you look at the dates down here–so this is over a week and I’m interested in over the week how many gallons did I make. In order to do that I need to take a totalization or an integral of this data. All right so back up to the top. And that’s what Calculated Data allows you to do; you can just quickly come in here and–I’m just going to say calculated data, data item (well that’s my PI tag right here) and then start time is clicking on G3–that’s the start time, the end time is clicking on G4, time interval–you don’t need to use that right now. I’m interested in the number of gallons I had over the whole week. We’ll see that in just a moment. And then I click “apply” and I get a value. Now at first–let’s see here; this is supposed to be gallons–gallons this week. And if you notice a thousand –that’s pretty low actually because it looks like I’m producing around two hundred gallons per minute on average and that would really quickly over a week go well over a thousand so what’s going on here? We can check this actually; so if I take an average of my data so it’s around a hundred and forty-four is this value here. That’s the average flow rate. If I want to just do a quick calculation of the number of gallons I would expect to make over the week I could say it’s going to be the average times sixty minutes in an hour times twenty-four hours in a day times seven days. That looks like about 1.5 million gallons which is a lot different than what we calculated below; a thousand versus 1.5 million. So what is going on here? Now what it is is that we have a rate that is in gallons per minute and the thing is PI tags are unitless. So if we’re going to do a calculation with times of variable like every integral is a calculation with time of variable, we need to know what the time unit is and PI tags don’t hold that so we need to assume something. What the PI Server does when calculating a totalization it assumes that we have units in gallons per day–or whatever rate we have is in per day. Now you can imagine if this is gallons per day then well ten thirty four suddenly seems reasonable–that actually makes sense–be we’re interested in gallons per minute. It comes down to this conversion factor here. This allows us to convert time units from days to hours to minutes. It goes with, you just put in… Number of minutes in a day which is–for me because I’ve done this before–I know that there is one thousand four hundred and forty minutes in a day. One thousand four hundred and forty, I click “apply” and suddenly you see this calculation is now in line with this calculation down here; they are both around 1.5 million. So we can see that these two are now in line with each other. Okay now you might ask, “Well okay it looks like I can just do this in Excel so why would it with this calculated data function?” Well let me show you something you can do that you can’t quite do as easily with Excel. I’m just going to move this down. I’m just going to move this down. Maybe I want to know how many gallons that I produced each day this week. So I’ll bring it down here to this day. All I need to do now is just change my time intervals. So before it was over this whole week and now I just need to say “one day (one d)”, click “apply” and now I have daily values for each calculation and I can even tell DataLink to show me the start time. So it looks like for March eleventh I produced around two hundred and seventy thousand gallons. March fifteenth two hundred and ninety four thousand gallons and then it looks like on March twelfth and thirteenth I had a low–I was producing a lot less. So you can very quickly bring this out to see like daily production totals–very easily. Or basically anything that has a rate you can get the integral of it and get the unit of some amount of time. Also, the other big bonus about this, when you do the calculation this way with a calculated data query, you don’t need all this stuff. You can just delete it from our sheet and you don’t need to carry it around with you. It can just be elsewhere and then your sheet is a lot more lightweight–you only have about eight cells of calculations there instead of having seventeen hundred cells filled with data that you have to email around and move around and it has to refresh every time and it gets slower. This way it is much faster, much more powerful and it’s really easy to just slice the days with the time-based functions of PI DataLink.

2 thoughts on “OSIsoft: Totalize and Integrate with the PI Calculated Data function (PI DL 2013 Excel Add-in)”

  1. I was trying to match the summation of the daily values you presented and apparently it is short by 234.50 from the total in a week (t-7d). Could you please explain why? Is it more accurate to get a total by a large time frame? If it's getting area under the curve the result must be the same since you're just chopping the curve's area underneath to chunks.

Leave a Reply

Your email address will not be published. Required fields are marked *