We are using a mysql and mariadb sql system hosted on AWS that is connected to remote industrial automation systems over the internet. We need a tool for developing and then automating the regular system production and compliance reports from the data archive. This might include summarizing data over a period of time (month, week or day), creating a chart or table, performing some analysis of of the data to remove outliers and create summary statistics. There could be boiler plate text that goes with each report and the report might be sent as a file to someone for review and then posted from a public repository with links provided automatically to a web index of reports.
There may be existing tools for this, or something that needs to be modified... perhaps we have to build from scratch, but I hope not! Other IoT tools such as Thingwork, Periscope etc. seem to be more tied to data presentation that report writing, but we may be overlooking options in those new IoT solutions.
This projects first phase would be to help us evaluate existing solutions and review the functional requirements of this tool that could be installed in each Amazon Web Services instance to allow a user to design reports and have them setup for scheduled running. It might be initially we have a designer installation that provides a service to create the reports so the instances only have the released reports and just a run time package. Over time we'd like to have a system that would make it easy for an end user to access the data and modify or create their own reports, but we expect that initially the complexity of the data dictionary will make it more practical to have a designer create and maintain the reports.
An additional element of this project is to anticipate that some of the data needed for the reports will be in another sql database, might need to be entered at the time the report is run, might need to have attachments from a file directory. This is all additional data that would not be in the primary scada system historical database repository but we'd like the system to be able to integrate or attach and bring with the main report.
So deliverables for this project are:
1. Down load, install and review the system we are using. (Mango from infinite automation) and determine the issues related to record locking and access to the database for report generation... can we work with the live data base or do we need to replicate the data we want and create a data warehouse for report writing.
2. Consider the integration of other data, files or records that not part of the main database of process data.
3. Develop a short list of 3-5 available tools that might address our need for a report writing system we can deploy in each cloud AWS instance we have running where the customer has elected to pay for the option of report writing system. This list would include pricing (developer and run time AWS installations), key features, strengths and limitations and major investments required to bring the system up to the functionality we need.
4. Alternative configurations we should consider to build data warehouses for each instance and approach this differently.
5. A recommendation for whether we move forward with an off the shelf system or customer system and approximate effort and list of tasks required to develop a production system for deployment.