Implementing Cross-database association reports with a collector

The data of many reports in actual application originates from many different types of databases, and the report data source cross-database is the norm in report development. There are many ways to implement this type of cross-Library associated report, but there are problems like that.

using the report Tool's own multi-source correlation feature

Most mainstream reporting tools now support multiple data source associations, which in some ways are really handy for reporting users. However, it is often difficult to achieve some cross-library related reports (due to the data structure and the business itself) through the report itself with the multi-source association feature. This is of course easy to understand, the report tool is mainly to do the data presentation, and the data calculation itself is not good.

even if some can be achieved, it is less efficient to do cross-library relational calculations in a report, much less than database performance, and often because a large number of hidden rows are used in the implementation process to further reduce report performance and increase memory consumption.

The following two methods are generally used.

unify data into a single database

unifying data from different databases into a single database is common in a variety of applications to use the database ( SQL) for powerful computing power. However, this approach adds additional cost overhead, and unifying data from multiple databases into a single database is bound to consume expensive database space and result in excessive database data, management difficulties, increased stress, and possibly additional database purchase costs and administrative costs. In addition, the completion of ETL migration data is also a large amount of work, the real-time requirements of the report also need to use a trigger way to do ETL(General ETL is timed), will seriously affect the performance of the original database.

use a high-level language to implement cross-library associations preparing data for reports

based on the problems mentioned in the two ways above, some users use high-level languages ( Java, etc.) program to complete cross-library operations and customize the data source for the report. The advantage of this approach is that it is flexible, theoretically any operation can be done through the program, and the disadvantage is that it is too difficult to write. Many high- level languages such as Java lack effective support for set operations, there is no corresponding class library, resulting in a simple group to write a lot of (loop) code, not to mention that the association will be after the group summary and other operations.

In this case, it is a good choice to use the collector to implement cross-Library Association reports.

How does the collector implement cross-Library association reports?

let's look at an example of how a collector can quickly implement a cross-library associated report.

Business Description

The employee's monthly salary should be related to the basic salary, attendance and performance of the employee, the attendance information comes from the attendance system (Hsql database) of the Labor Department , and the base salary and performance information are stored in the financial system (MySQL database). These two types of information need to be combined to calculate the employee's salary.

implementation Steps

Scripting (crossdb.dfx complete cross-Library association calculations to prepare the data for the report

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/43/77/wKiom1PbLLnwiGlUAAEZyAAcTuo419.jpg "style=" float: none; "title=" esproc_crossdatabase_report_1.jpg "alt=" Wkiom1pbllnwigluaaezyaactuo419.jpg "/>

• in the Connect Hsql and mysql data sources via connect in A1, A2, respectively

• in the A3,A4,A5 in the query statement from the two database to remove the data used, when the data is all taken out, no longer need to interact with the database, in the A6, shutting down two database connections in a A7

• in the Use join to complete the three-table connection in A8

• in the A9 calculation should be paid, the algorithm is: Basic Salary * (1- attendance factor + performance factor )

• finally through A10 result set for use by the report

Report Call Collector Script Complete report presentation

the class package of the collector is encapsulated into a standard JDBC, which allows the report tool to invoke the collector script as called by the class stored procedure, as in this example, in the Report tool, configure JDBC as a database connection , and then use Call CROSSDB () after building the stored procedure dataset To complete the call.

here to BIRT As an example to illustrate the invocation process:

1. Copy the Collector JDBC driver package to the appropriate directory

2. Configure the report data source

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/43/77/wKioL1PbLdPzH_MBAAEHs3FW4xg867.jpg "title=" Esproc _crossdatabase_report_2.jpg "style=" Float:none; "alt=" wkiol1pbldpzh_mbaaehs3fw4xg867.jpg "/>

3. Set DataSet, call the collector script

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/43/77/wKiom1PbLLrTWE-_AAEOGVNU_lM348.jpg "title=" Esproc _crossdatabase_report_3.jpg "style=" Float:none; "alt=" wkiom1pbllrtwe-_aaeogvnu_lm348.jpg "/>

a few simple steps to complete the call, The BIRT can be used to show the output directly using the calculation results of the collector.

Conclusion

as you can see from this example, the Collector excels in cross-library calculations and calculates the results in standard The ResultSet method returns a data source for the report, and it is straightforward to call the collector script in the same way that the report is called by the class stored procedure.

There is one more important point to pay attention to, which is price. The collector is a business software that needs to pay, fortunately only for big data of the cluster charge, as a report data source function is free, do not need to increase costs to easily solve the problem of multi-database Association report.