Shulou(Shulou.com)06/01 Report--

This article mainly analyzes the relevant knowledge points of BIRT heterogeneous cross-database dynamic association query, the content is detailed and easy to understand, the operation details are reasonable, and has a certain reference value. If you are interested, you might as well follow the editor to take a look, and follow the editor to learn more about "how to do BIRT heterogeneous cross-database dynamic association query".

It is difficult to solve this kind of problem with Data Sources Join which comes with BIRT and how to convert it into the same library with ETL. You can discuss it through the following examples:

The transaction details data (trade table) is stored in the database DB2 of the production system, and another part of the business data (network table, account table) is stored in the Mysql of the business system. The relationship between them is shown in the following figure:

The so-called "dynamic association" means that the user inputs parameters in the foreground interface, and the report determines which table to associate the trade with through the parameters, and displays the associated data in the report. In the actual operation, the data may also be filtered and summarized. The query process is shown in the following figure:

For example, query scenario 1: according to the passed parameters, the report can dynamically associate the outAccount field in trade with the accountNo field in account. Finally, the query result shows all fields in trade and name,gender,city fields in account.

The common solutions and shortcomings to solve such problems are analyzed as follows:

1. The problem with BIRT Data Sources Join is that table names and field names are known and determined, but this kind of report is dynamically associated with parameters, so it can not be implemented.

2. You can use ETL to extract the data from the production database to the business database, so that the problem across the database will be transformed into the same database. The idea of this scheme is simple, but there are many difficulties in the details of implementation. First of all, real-time query: in order to query data in real time, we need to use functions such as triggers in the production library to detect real-time changes of data, and push the data to the business database, but the production database can not be easily changed, so real-time query can not be realized. Non-real-time query is also difficult to do, because the data in the production database is so large that it is impossible to take all of it every time, only by incremental extraction, while judging increment requires adding a timestamp field to the trade table. Similarly, such changes are not allowed in the production library and therefore cannot be implemented.

3. In terms of ability, BIRT JAVA bean data source can really solve the report problem, it is more flexible than Data Sources Join, and there is no need to modify the production library. But there is only one flaw in this scheme: the code is too complex because data computing is not a JAVA specialty, and it is not realistic to implement it in a hard-coded way every time you encounter cross-library problems.

It is recommended to use the aggregator, which is an independent data computing engine, has the computing power that does not depend on the database, supports the mixed operation of heterogeneous data sources, and is more suitable for dynamic association re-calculation. in fact, the aggregator can be regarded as a BIRT JAVA bean data source with simpler syntax. For example, to implement the above problem, the aggregator script only needs 6 lines:

A

one

= DB2.query ("select runningNo,networkNo,outAccount,amount from trade")

two

= "select" + crossJoinField+ "," + crossOtherFields+ "from" + crossTable

three

= Mysql.query (A2)

four

= join (A1 _ crossJoinField _ trademark ${tradeJoinField}; A _ 3 _ cross _ ${crossJoinField})

five

= crossOtherFields.array (). ("cross." + ~) .string ()

six

= A4.new (trade.runningNo,trade.networkNo,trade.outAccount,trade.amount,$ {A5})

Where tradeJoinField,crossJoinField,crossOtherFields,crossTable is the input parameter. Finally, the calculation results are returned to the DataSet of BIRT for report presentation. From this example, the parameter usage of the aggregator is very flexible and is very suitable for this kind of dynamic associative query. Because it is a specialized computing language, the code of cross-library computing is more concise and easy to understand than the conventional method.

About "BIRT heterogeneous cross-database dynamic association query how to do" is introduced here, more related content can search previous articles, hope to help you answer questions, please support the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.