Today is...
Saturday, June 15, 2019
The OPC Community Forum.
Trend graph on Excel data
Need to do ad-hoc calculations on OPC HDA data and show the results in a trend.

What we currently have:

- A historian with an OPC HDA server on top.

The historian allows us to do calculations (on history data) but these need to be configured in the historian. The standard trending tool allows the users to do ad-hoc calculations (just put a formula where otherwise a tag is entered) but this desktop tool is to expensive to be installed on every PC. Further we would have to have every user to connect directly to the historian and I would like to keep this door closed.

- A web based trending tool to get the history data to the users.

A problem some of our users have is to do ad-hoc calculations on historized data. This enables what-if studies based on history data.

- An Excel add-in to get the history data in Excel

Currently our users use this to do ad-hoc calculations but the MS-chart component has a lot of missing functionality for our users. They typically complain about:

-- having only one vertical axis so all data needs to be scaled and normalized first,

-- having only one horizontal (time axis),

-- having no easy way to zoom in on the data and

-- lack of a hairline cursor.

So, I'm looking for a tool that can either use data out of Excel and show a decent trend with features described above or a tool that allows us to do ad-hoc calculations directly on history data and show the results in a trend. In the latter case we don't want to open up the OPC HDA server connection for the whole plant so the ideal would be to have something that connects to a different server.

In any case it is the end user who has to be able to create the calculations so the calculations should not have to be configured on a server (we already have that). Also, when the user is done looking at the data he will probably never need the calculations again so there is no need to have an engine that stores (and keeps storing) the calculation results.

The way I see it the tool would have to fetch all history data needed to do the calculations, perform the calculations point per point and show the results in the trend.

Pointers to good (and reasonably priced - open source ?) tools and pointers to libraries that can be used to create such tools are very welcome.

What is your Historian / OPC HDA source ?

There are a number of thin client solutions on the market which may be more flexible/powerful than using Spreadsheets. We provide solutions like this as do many other vendors. Google will provide a list of them

Bill Graham
Industrial Thinking Ltd

Bill,

Thanks for your reply.

I have Googled and Googled and installed and tried several applications from vendors who claim (or others told me) they have the functionality I need but so far I had no success. The data source of the OPC HDA server is Honeywell PHD but that should not be relevant. PHD desktop is the best trend tool I've ever seen but it's not thin client. The thin client trend application that we have is Matrikon O.I. and although it is a very good product it just doesn't support ad-hoc calculation trends.

For any tool that provides the functionality and is an OPC HDA client I would have to have some kind of HDA bridge because I don't want every user connecting to the historian directly.

By Bill Graham on 7 January, 2010 - 8:20 am

Hi Rudi,

We support both OPC HDA and Honeywell PHD with our CounterPoint solution. This is true thin client connectivity and it allows on the fly calculations between any data source that is supported. These are also available within our Excel addin.

If you would like more information there is a contact email address on our website.

Kind Regards,

Bill Graham
Industrial Thinking Ltd

By Andrey Romanenko on 7 January, 2010 - 9:47 am

Hello Rudi,

You asked about open-source libraries and I would like to point out some that we use extensively:

- openreports
- jasper reports
- jxls
- jfreechart

Besides, the Java Virtual Machine is good for dynamic languages (not only for Java). You mention you do not want to allow your users to access the HDA server. What would you think about having another historian? We use PostgreSQL (open-source) and OPC DA client Plantstreamer (open-source) to acquire and store plant data. Then you can even use ODBC to retrieve the data.

Using the tools above you can put together a simple client (java program or applet) that does custom calculation and nice plotting.

We have a web based process information system Plantbeat based on these principles but its focus is somewhat different from what you are looking for. Nevertheless, if you are interested to see how the building blocks work, please let me know.

Best Regards,
Andrey Romanenko
Ciengis - Advanced process control and Optimization
andrey (at) ciengis (dot) com

Thanks for the info and offer. I've send you an e-mail.

By curt wuollet on 7 January, 2010 - 12:30 pm

Nothing that runs on Windows is a thin client. The OS by itself is extremely obese. Use what works.

Regards,
cww

Hi Rudi,

I have used Matrikon Operational Insight in the past, and the trend does support some ad-hoc calculations. What kind of in-trend adhoc calculations were you looking to perform?

e.g. Running Hourly Average, Daily Totals, Flows into Volumes, Unit Conversion (Celsius into Fahrenheit)

As for your request for an HDA bridge, are you doing this to restrict availability of certain tags to specific users, or are you doing it in an attempt to reduce loading on the historian?

If it is the first, you can accomplish this by using an OPC Server for PHD that supports OPC Security to manage user permissions (like http://www.matrikonopc.com/opc-drivers/636/index.aspx).

If it is a loading issue, a bridge will not prevent any overloading, only a mirror database will do the trick.

Mustafa Al-mosawi

The calculations are of the form

item_1 * item_2 / item_3 + 2

Where each item_x is a set of history data.
The result would be a new set of history data created ad-hoc by the client (not stored) which is trended.

The OPC-bridge was just a suggestion to not have the users to connect to the historian as there is a firewall in between. I would like to have only one connection through the firewall. O.I. provides functionality of that kind but I don't know it can be used by other apps.

Hi Rudi,

You can do those kinds of ad-hoc calculations in the Matrikon Operational Insight Trender today. You can create calculated series from other series and combine them together to get a final result of the form you described.

item_1* item_2 / item_3 + 2. Are you doing a unit conversion?

Mustafa

You are right. However, our users complain that when they want to do that they have to add all calculation inputs to the trend, create the calculation and then hide all calculation inputs. This, they find, takes too much time and effort compared to the system they used to have. The net result is that calculations are not done which actually is a cost as there is a lot of value in these (quick) what-if studies.
Unit conversion is something PHD also does automatically but is not considered to be an issue (so far) in OI.

So, it isn't the capability, so much as the way it is implemented? If the ad-hoc calculations were easier, that more people would use them?

Would you say that the calculation is 'templated'? In that most of the time, the calculation happens the same way, or that there tend to be variations?

Mustafa Al-mosawi

The calculations are completely different all the time. It is like what-if analysis using process data history. Therefore I tried to "sell" net-office because it is quick and easy to do calculations in Excel but the charting possibilities are not sufficient.

If the calculations are always the same I usually put them as virtual tags in our PHD system (or even do them on the DCS). If trend functionality is not all that important (always the same period, same ranges) I advice to use net.office.

Rudi,

You may have already found your solution, but if not, you could install a PHD Shadow Server on the other side of the firewall (on the same side as the users). I can tell you all the ports that need to be opened between the two if that would be a good option for you. The installation of the Shadow server would only take a couple of days. Also, as the PHD system is licensed by user, you won't have to pay additional fees (You can check with your Honeywell account manager to be sure). Email me (JasonBaum [at] Vision-Stone.com) if you need more information. Thanks,

Jason

By Wassim Daoud on 8 January, 2010 - 11:19 am

Hello Rudi,

What you described can be easily implemented with the Excel reporter. I've used it many time and it's great. It comes with sample trends for

You can download it from here:

http://www.matrikonanalytics.com/products/excelreporter.aspx

Give it a try and let me know how it went for you

Cheers,

Wassim Daoud
Global Solutions Architect

Rudi,

The more I think about what you are doing, the more I think you would be served by using the following approach.

1. Install a PHD Shadow Server on the User's side of the firewall. This will not incur any additional licensing fees and will ensure that users are not connecting directly to the control network, but remain separated. Additionally, it will allow you the option of using the Honeywell PHD client tools if you later want to do so.

2. Install the Honeywell PHD OPC HDA Server on the Shadow Server.

3. Use any OPC HDA clients that you wish for your Excel data pulls and trending. See the following for some ideas in this area:
http://www.opcconnect.com/freecli.php

Email me directly if you have any questions or need clarification. Thanks,

Jason Baum
JasonBaum [at\ Vision-Stone.com