A New Way to Consolidate Various Data Sources for Reporting Tool

In report development, we may need to present the data from multiple databases in one report, such as data from MSSQL database for CRM and Oracle database for ERP. If the reporting tool like iReport only supports single data source, then we need to consolidate the multiple data sources into a single data source.

The Crystal, BIRT, and other so-called reporting tools for multiple data source can only join 2 result sets roughly, and are also very inconvenient for the complex multi-data-source computations. For example, compute the yearly growth rate of order value for each client in ERP, group by the client data from CRM, and then present in a report.

It is believed that most companies adopt the commence strategy to only provide the single-data-source edition reporting tools, even if they are already capable of providing full support for multiple data sources.

How can we handle such a situation?

The commonest practice is to utilize the ETL or Data Warehouse tool - consolidate the data from various platforms to a single database. This practice will surely require preparing ETL scripts for regular updates according to the specific rules, building a global view or organizing the data as a single data source with stored procedures, as well as the contribution of DBA. All in all, this way to achieve the goal is at the great cost of additional human resource, a great deal of time, and modification to the database.

If the cost is not a priority concern, then the higher edition of database administration tools could be a better choice for implementation, such as, Server Link or Linked DB. In doing so, we will have to purchase the database server for separate use, recruit additional staffing, bear maintenance expenditure, and keep the safety considerations in mind. In facts, such practice only automates a few ETL functions based on the same core. The inconveniences in handling the complex computations for data sources are still there unsolved.

We need a tool that is not only lightweight, convenient, and easy-to-use, but also powerful enough to handle such situation.

esProc is such a tool that is specially built for database computation, expert at simplifying the complex computation, and perfect in debugging. With the support for cross-database computation and JDBC interface, esProc can easily integrate with reporting tools.

Still the above case, the whole procedure of data retrieving, computation, and merging can be accomplished in a few lines of esProc scripts clearly and concisely, as shown below:

Then, the result can be queried directly through the JDBC interface for Java report, just as easy as connecting to the common database.

In addition, esProc is especially designed for the massive amount of structured data, with the grid style and agile syntax. Thanks to its support for the external parameters and the direct data retrieving & computing across multiple databases, text files, and Excel sheets, esProc can perfectly consolidate multiple data sources and act as the computation layer of report data source of less couplings and more common use.