Am need of your suggestion for scenario below :
one of our clients has 8 postgres DB servers used as OLTP and now wants to generate MIS reports/dashboards integrating all the data in the servers. - There are around 100 reports to be generated - There would be around 50k rows added to each of these databases - the reports are to be generated for once every month - they are running all there setup in baremetals - they don't want to use hadoop/spark , since they think the maintainabilty will be higher - they want to use opensource tech to accomplish this task
with all said above, one approach would be to write scripts to bring aggregated data into one server and then manually code the reports with frontend javascript. is there any better approach using ETL tools like Talend,Pentaho etc. which ETL tool would be best suited for this ? community editions of any ETL tool would suffice the above requirement..?
I know for the fact that the commercial offering of any of the ETL tools will not be in the budget.
could you please let me know your views on this.
Thanks in Advance Deepak
Sure Yes. I did similar things successfully a dozen time in my life.
My suggestion is to use Pentaho-Data-Integrator (or Talend) to collect the data in one place and then filter, aggregate and format the data. The data volume is not an issue as long as you have a decent server.
For the reports, I suggest to produce them with Pentaho-Report-Designer so that they can be send by mail (with Pentaho-DI) or distributed with a Pentaho-BI-server.
You can also make javascript front end with Pentaho-CDE.
All of these tools are mature, robust, easy to use, have a community edition and well supported by the community.