esProc Implements Cross-database Report Associating

In practical applications, many reports are populated with data from different types of databases. So, the cross-database data sources are quite common in report developing. Currently, there are many ways to implement such cross-database report associating, and none of them are perfect.

Rely on the native multi-source associating function of the reporting tools 

Nowadays, most major reporting tools are real convenient in specific aspects for enabling its report users to implement the associating across multiple data sources. However, these native functions for implementing the associating across multiple data sources are not always so effective and efficient in handling many database report associating problems. It all depends on the data structures and specific businesses. Considering reporting tools are mainly designed for the data presentation but not the data computing, we cannot really complain.

Even for those reporting tools that can barely do this job, they are relatively inefficient in handling the cross-database associating - far worse than database. Moreover, since a great number of hidden rows and columns are often involved in the implementation procedure, the performance can be further hindered with more memory spaces are consumed.

So, the below two methods get popular:

Consolidating data into one database 

Consolidating data from various databases to one database is very common in various applications with an aim of leveraging powerful computing capability of database (SQL). However, this practice costs more. It unavoidably takes additional expansive database spaces, accumulate excessive data in database, incur great difficulty in administration and management, and cause more pressure. In addition, the additional database purchases and management costs may also another burden to bear. ETL data migration means lots of incredible workload. Not to mention that the trigger is necessary to prepare those reports demanding high on the realtimeness. The original database performance will be affected greatly.

Implement the cross-database associating to prepare data for report with the senior languages 

To remedy the drawbacks of the above two solutions, some users use the senior programming languages like Java to implement the cross-database computation and customize the data source for reports. The major advantage of this practice is its flexibility. Theoretically, all computations can be implemented by programming. But such programming is never an easy thing. Java and other alike senior languages still have not effectively supported the function and syntax for the operations on sets. Without the corresponding class library, a simple group could require many (looping) codes, not to mention the grouping, summarizing, and other operations after associating.

In such challenging conditions, esProc is an ideal choice to implement the cross-database report associating.

How esProc implements cross-database report associating? 

Let’s check out how esProc implements the report associating across databases with an example.

Business Description 
The monthly payable salary of a typical enterprise employee is related to the basic salary, attendance, and performance of that month. The attendance information comes from the attendance system (HSQL database) of Human Resources. The basic salary and performance data is stored in the finance system (MySQL database). To compute the employee salary, we need to consolidate these two types of data:

Implementation steps 

Compose script (crossDB.dfx) to complete the cross-database associating, and prepare data for the report 

• In A1 and A2, connect HSQL and MySQL data sources respectively with function connect 
• In A3, A4, and A5, retrieve the required data from two databases respectively with the query clause. To this point, all data have retrieved and database exchange is not necessary. In A6 and A7, the connection between the two databases is closed. 
• In A8, use Join clause to complete the joining of these three tables 
• In A9, compute the payable salary with the algorithm: Basic salary*(1-Attenance coefficient+Performance coefficient) 
• Lastly, generate the result set for use in report with the function result in A10

Calling esProc Script in Report for Report Presentation 
The esProc class is packaged into standard JDBC, allowing reporting tools for calling the esProc script in the same way as calling stored procedures. For example, in this example reporting tool, firstly, configure the esProc JDBC just in an alike way to configure the database connection; secondly, establish the dataset for stored procedure, and then use the call crossDB() to complete the calling.

Let’s take BIRT as an example to demonstrate the procedure of calling:
1.Copy the esProc JDBC driver package to the corresponding directory
2.Configure the report data source



3.Set DataSet and call the esProc script


Then, the whole calling procedure can be completed in a few very simple steps for BIRT to use the result from esProc and present the output directly.

Conclusions 

As can be learned in this example, esProc is quite good at cross-database computing and returning the result in the form of standard ResultSet, acting as the data source for the reports. In the report, it is quite simple to call the esProc script in a way similar to calling the stored procedure.

One more thing worthy of attention is the price. Although a priced commercial software, esProc is free for its report data source feature, and only a reasonable fee is charged for its big data cluster feature. In a word, no additional cost will be incurred if using esProc to meet the challenges on report associating across multiple databases.