Consolidate Data from Multiple Sources in a Single-Source Report

The report data sources cover the result set of SQL queries or stored procedures, and the 2D table from the text or Excel files. Owing to the technical competence or versioning, various reporting tools may only support a single data source, such as JasperReport, Quiee, BIRT, and Crystal Report.

But users often find themselves stuck in such cases that a report has multiple data sources to be consolidated into one data source as required. SQL can solve some simple cases of them, for example, associating 2 tables from a same database. But in the real world application, the real scenario is much more complex for SQL to handle.

Many people commonly use stored procedure to solve no matter the nested query and subquery involving multiple tables, or the complex computation involving judgment, loop, and temporary table. However, most report developers are more capable of presenting report than composing the SQL statement. To compose the complex stored procedures, they will have to request assistance from DBA and other IT experts. This is not only time-consuming to coordinate and communicate, but also inconvenient to debug. Not to mention, the amendment and upgrade workload in the future will be ever greater. Judging from the above discussion, the stored procedure is not the best choice to consolidate multiple data sources.

What’s more, the stored procedure is actually not allowed for many core business databases for the sake of safety. In this case, the best practice is to trade space for safety. For example, establish another server to replicate the data synchronously, or use ETL/data warehouse and other methods to implement it indirectly. It will obviously incur more cost and compromise the performance. So it is also not the best choice in this respect.

Worsely, there are some cases are absolutely unresolvable for the stored procedure. For example, since CRM uses the MSSQL database and ERP uses the Oracle database, the cross-database computation is required to compute out the yearly order value increase for respective client in ERP, and present these data on the report together with the client details. What’s even worse, there are also some data stored in the TXT/Excel/XML, in which case, the computation between the database and non-database data sources is required. Many people make a grave mistake to solve the problem with Java and other senior languages.

Then, how to consolidate multiple data sources completely and conveniently?

With years of experiences, I’ve concluded several methods to share and discuss with you.

Virtual Data Source for JasperReport Commercial Server

The Virtual Data Source provided in the JasperReport business edition can solve this problem. With wizard, one virtual table can be created by joining two entity tables of either physical table or SQL. The interface is shown below:

This method provides so simple a consolidation method that I only need to select the type of relation. In addition, it supports quite a lot data source types – not only the computation between database, but also the computation between HQL/EJBQL and database.

But this method is poor regarding its computational capability. First, this method can only consolidate two tables. The procedure will become exceptionally cumbersome for the multi-table or multi-level relations. Besides, without the native support for computational scripts, this method only provides three ways to consolidate, which is far from enough even for the normal data source computation.

esProc for reporting tools

Unlike the bundled reporting tools, esProc is a data source development tool that is totally independent of databases and reporting tools. So, esProc is quite convenient to work with other reporting tools. The computational interface of esProc is as follows:

The advantage of esProc is the strong computational capability in solving the complex goal easily. esProc is more powerful than SQL considering its intuitive interface of grid style, professional IDE, and convenient debugging feature. In particular, its support for the hybrid computation between database and non-database is very impressive.

        

esProc is characterized with the full memory computation. So, the standalone performance of esProc is worse than that of data warehouse. However, to partly remedy the drawback, esProc additionally supports the Hadoop. In other words, esProc can support the big data computation in the distributed environment.

Data Sources Join for BIRT

BIRT is the famous open source Java report. Despite the rather simple interface, its capability to develop report is widely recognized. The Data Sources Join can also solve many consolidation problems regarding multiple data sources. The interface is as follows:

As can be seen, BIRT has almost the same feature as Japer does. Since they share the common advantages and disadvantages, I will not dwell on it and you can read the Jasper introduction for references.

Merge Dimensions for SAP BO Report

BO provides many functions for data source consolidation, such as IDT (Information Design Tool), Database Expert, and Merge Dimensions. Generally speaking, the nature of these features does not differ much. So, I will discuss their pros and cons with the Merge Dimensions of Web Intelligent - a major offering of BO - as shown in the below figure.

As can be seen, the query1 and query2 is equivalent to left table and right table of Jasper. The year and quarter they are corresponding to are the Joined fields of Jasper. By and large, Merge Dimensions is weaker than Jasper. For some functions that can be implemented on one interface with Jasper, BO requires more than one tool, and each tool will be used by switching between several interfaces.

The above four methods are what I am most familiar with. Why not share your methods?