Alarm and archive reporting tool refactoring

[utzeln]

We have integrated SNS reporting tools and they already provide valuable information.

But we need to make some changes such as:

• alarm configuration part should list the automated actions
• graphics should fit the page
• periodic requests to the archive RDB impact overall performance
  ...

It is then a good opportunity to look at Open Source Business Intelligence (BI) architecture and tools in which runtime data is extracted and transformed from various operational systems and loaded into a staging area or data warehouse that feeds downstream analysis and reporting tools.

This type of architecture ensures an efficient and scalable method for managing the flow of operational data to support reporting and analysis applications.

It should be possible to install only the alarm or the archive part, or the both.

A good point is that we can use open source solutions such as Eclipse BIRT and there is a lot of BIRT Report Designer Tutorial Demo available on the Web. So we could provide standard reports - the ones from SNS, perhaps some new ones from ANSI/ISA-1.2-2009 Management of alarm system for the process industries and everyone can design its own ones.

Any comments?
Thanks

[kasemir]

We have integrated SNS reporting tools and they already provide valuable information.
Excellent!

Note there were some recent changes to make it work for BNL, so we need to compare your tweaks against the latest source snapshot...

But we need to make some changes such as:

alarm configuration part should list the automated actions
Sure, they didn't exist when the reports were first implemented.
graphics should fit the page
Well, that depend on the page that you have...
It would be good to remove all s, in case there are any left, to get something that flows somewhat independent of the actual display size.
periodic requests to the archive RDB impact overall performance ...
True. The RDB can go to 100 percent CPU load while some of the reports are running.

As for birt, we had looked at that many years ago, and it wasn't all that great.
IDE would hang when trying to browse RDB tables during development of the reports.
Reports would not work in Firefox on Linux, or use 100 percent CPU there in its javascript. That's why w switched to simpler web pages that basically show tables and images, no birt-type interactive charts.

Maybe birt is better now, by all means give it a try. They certainly hand out nice T-shirts at each eclipsecon.. ;-)

-Kay

It is then a good opportunity to look at Open Source Business Intelligence (BI) architecture and tools in which runtime data is extracted and transformed from various operational systems and loaded into a staging area or data warehouse that feeds downstream analysis and reporting tools.

This type of architecture ensures an efficient and scalable method for managing the flow of operational data to support reporting and analysis applications.

It should be possible to install only the alarm or the archive part, or the both.

A good point is that we can use open source solutions such as Eclipse BIRT and there is a lot of BIRT Report Designer Tutorial Demo available on the Web. So we could provide standard reports - the ones from SNS, perhaps some new ones from ANSI/ISA-1.2-2009 Management of alarm system for the process industries and everyone can design its own ones.

Any comments?
Thanks

—
Reply to this email directly or view it on GitHub.

[kasemir]

As for RDB speed, it is slow because of the way the message database tables are set up.
In the very first schema, every message property was in the separate table. After we moved TYPE, SEVERITY and NAME into the main MESSAGE table, at least those could be indexed, but looking for the current severity of messages is still slow.

Overall we might be better off with a Nosql database for the messages, like mongodb, because the message properties change depending on the message type.

So if you go with birt, make sure that it would still be possible to use mongodb as a data source.

Thanks,
Kay

On Jul 22, 2014, at 10:31, utzeln notifications@github.com wrote:

We have integrated SNS reporting tools and they already provide valuable information.

But we need to make some changes such as:

alarm configuration part should list the automated actions
graphics should fit the page
periodic requests to the archive RDB impact overall performance ...
It is then a good opportunity to look at Open Source Business Intelligence (BI) architecture and tools in which runtime data is extracted and transformed from various operational systems and loaded into a staging area or data warehouse that feeds downstream analysis and reporting tools.

This type of architecture ensures an efficient and scalable method for managing the flow of operational data to support reporting and analysis applications.

It should be possible to install only the alarm or the archive part, or the both.

A good point is that we can use open source solutions such as Eclipse BIRT and there is a lot of BIRT Report Designer Tutorial Demo available on the Web. So we could provide standard reports - the ones from SNS, perhaps some new ones from ANSI/ISA-1.2-2009 Management of alarm system for the process industries and everyone can design its own ones.

Any comments?
Thanks

—
Reply to this email directly or view it on GitHub.

[utzeln]

Thanks for the feedback!

Regarding the open source solutions we found the following:

It seems that Pentaho is quite popular - I do not know if they hand out T-shirts :)..

What I would like to assess is the possibility to plug additional data source without too much effort. These solutions are more or less based on Eclipse, so the extension point mechanism should be great for site adaption.

[berryma4]

I was picturing eventually using JasperReports for this at FRIB. But, if
someone finds something nicer ....

On the topic of data warehouses etc., Dong Liu shared a nice Stonebraker
paper on the subject with our group:
http://cs.brown.edu/~ugur/fits_all.pdf

I know mongoDB is the current hammer, but what was everyone thinking for
the data warehouse? Hadoop is popular for this use case (ie. facebook)?
There are maybe too many choices in this area (using Hive, or Facebook's
Presto, etc.). A lot of "hip" decisions :)

Thank you Nadine for looking into this ... it's something we keep putting
off at our lab.

On Wed, Jul 23, 2014 at 8:19 AM, utzeln notifications@github.com wrote:

Thanks for the feedback!

Regarding the open source solutions we found the following:
[image: image]
https://cloud.githubusercontent.com/assets/4686181/3672611/a6859c28-1262-11e4-9da2-a4608b2e7f42.png

It seems that Pentaho is quite popular - I do not know if they hand out
T-shirts :)..

What I would like to assess is the possibility to plug additional data
source without too much effort. These solutions are more or less based on
Eclipse, so the extension point mechanism should be great for site adaption.

—
Reply to this email directly or view it on GitHub
#597 (comment)
.

[utzeln]

It seems that for alarm reporting at least we need first to "improve" the message logging.

Extract of some exchanges regarding the latter:

On 15.09.2014 15:26, Kasemir, Kay wrote to Matthias:

For the RDB, you may remember that we started out with the DESY table layout (AMS-Log-DDL.sql from Markus Moeller, ca. 2008). In there, the 'message' table had just the message date, and all other message properties were in joined tables message_content & msg_property_type.
WIth that, it was extremely slow for us to for example list all "alarm" messages. So we added the type, (pv) name and severity properties to the main message table, see https://github.com/ControlSystemStudio/cs-studio/tree/master/applications/plugins/org.csstudio.logging.jms2rdb/dbd
Works for us, but it's still not the fastest..

We have several different message types:

• CSS log message from java.util.logging,
• alarm system traffic,
• IOC log messages,
• EDM 'write' messages,
• messages sent via some web applet from LabVIEW.

All have a different set of properties, it's hard to come up with RDB tables that can hold all, are fast to search, and don't waste space because some message types may not have some property that others do.

A no-SQL database which would allow any 'map' for a message makes more sense.

I believe Kunal is looking at that, because BNL just started to use MongoDb for experiment data, so writing the log messages into MongoDb would overall fit them.

Nadine had started to look at a more generic reporting tool, checking BIRT et all for possible starting points.

Meanwhile, Eric showed me last week how he used Apache Camel to route the JMS (alarm) messages into ElasticSearch (i.e. also MongoDb as storage), then used ElasticSearch to create web reports of the (alarm) messages.

From that it looks like we have a few common goals:

Everybody wants to route as many messages as possible to JMS: IOC, alarm, CSS, ..
Everybody wants to capture them, where MongoDb is gaining favor over a plain relational database, were we weren't able to agree on a schema.
For reporting, you can all have the SNS reports, but there seems to be interest in creating something better.