How to Integrate esProc with BIRT

esProc provides the standard JDBC to integrate with reporting tool. Let's take the BIRT for example to demonstrate the integration procedure.

1.  Integrate in IDE1.1 Create profile

Create two configuration files of config.xml and dfxConfig.xml required by esProc JDBC.

1.1.1  config.xml

<?xml version="1.0" encoding="UTF-8"?>

<Config Version="1">

         <!-- registration code-->

<regCode>HOjw6-9da4x-NXkD6-089IX@w</regCode>

<charSet>GBK</charSet>

         <!-- Configure the addressing path for dfx file. This path is the absolute path and multiple paths can be set and separated with “;”. The path to the dfx file can also be put on the class path of application project. The file in this path will be loaded prior to that in the addressing path -->

<dfxPathList>

<dfxPath>D:/dfxs</dfxPath>

</dfxPathList>

<dateFormat>yyyy-MM-dd</dateFormat>

<timeFormat>HH:mm:ss</timeFormat>

<dateTimeFormat>yyyy-MM-ddHH:mm:ss</dateTimeFormat>

         <!-- Data source configuration method 1: Configure the connection pool on the application server. Specify the data source name here -->

<DBList>

         <!-- Data source name, which must be consistent with the data source name in the dfx file. The hsql database is connected here -->

<DB name="demo">

<property name="url" value="jdbc:hsqldb:hsql://127.0.0.1/demo"/>

<property name="driver" value="org.hsqldb.jdbcDriver"/>

<property name="type" value="HSQL"/>

<property name="user" value="sa"/>

<property name="password" value=""/>

<property name="batchSize" value="1000"/>

<property name="autoConnect" value="false"/>

<property name="useSchema" value="false"/>

<property name="addTilde" value="false"/>

</DB>

</DBList>

<mainPath/>

<tempPath>temp</tempPath>

<bufSize>65536</bufSize>

<localHost/>

<localPort>0</localPort>

<logLevel>DEBUG</logLevel>

<callxParallel>1</callxParallel>

<nodeParallel>1</nodeParallel>

</Config>

1.1.2  dfxConfig.xml

<?xml version="1.0" encoding="GBK"?>

<dfxConfig>

         <maxConcurrents>10</maxConcurrents>

         <maxWaits>10</maxWaits>

         <log></log>

</dfxConfig>

1.2  Create the new report and load the driver

Create a new report. Under the DataSources, create a new data source of JDBC Data Source type, and proceed to the next step.

In the new data source window, click Manage Drivers and add the driver for esProc JDBC.

 We need to add five jar packages of dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar, and dom4j-1.6.1.jar. These jar can be obtained from the esProc IDE[installation directory]\esProc\lib directory. If the esProc program also takes other database as the data source, then the corresponding database driver needs to be added. As an example, hsqldb.jar is added in this case.

The two configuration files config.xml and dfxConfig.xml created in the first step must be put under the root path of classpath. For esProc, no function is available to set classpath witha directory. The configuration files can be compressed to the root directory of any above-mentioned jar, such asdm.jar, and the name must be left unchanged.

After the connection test, select the driver class name com.esproc.jdbc.InternalDriver (v1.0) in the Driver Class. Fill the database URL jdbc:esproc:local://, and leave the user name and password blank.

esProc JDBC is a completely embedded computing engine. All computing are completed in the embedded computing engine. So the URL is local, and it is not the complete database because there is no user name and password.

Once filled, click Test Connection till the data source connection test succeed.

1.3  Deploy esProc scripts

Copy the esProc script (dfx) to dfxPath configured in the config.xml. Take the stocks.dfx for example, query the closing price of stock in a certain month. 

1.4  BIRT calling

Create a new Data Sets, select the just configured data source, and then select the stored procedure as data set type. 

In the next step, fill in the query statement {call stocks(?)}. For the esProc scripts, the stored-procedure-like calling method is adopted. In this case, the "stocks" is the dfx name, and "?" is the parameter to pass, which is the month of query in this example. 

Set parameters: 

 The calling succeeds if the query result can be viewed in the data set preview.

2. Deloy on Web side

Let's take Tomcat6.0 as an example to demonstrate the application.

2.1 Prepare Web release package

Download BIRT4.3.0 release package birt-runtime-4_3_0.zip at: http://www.eclipse.org/downloads/download.php?file=/birt/downloads/drops/R-R1-4_3_0-201306131152/birt-runtime-4_3_0.zip

Copy the WebViewerExample directory to the webappsdirectory of Tomcat, and rename it as birt.

2.2  Create configuration file

Prepare two configuration files of config.xml and dfxConfig.xml, as introduced in the above-mentioned section “Integrate in IDE”.

2.3  Copy the relevant jar package

Copy the required files mentioned in above section “Integrate in IDE”: dm.jar, poi-3.7-20101029.jar,log4j_128.jar,icu4j_3_4_5.jar and dom4j-1.6.1.jarto the WEB-INF\lib of application. Compress the two configuration files to dm.jar.

2.4 Publish report

Copy stocks.rptdesign or other developed resulting reports to the root directory of application. Start Tomcat and visit http://localhost:8080/birt/frameset?report=stocks.rptdesign. The deployment succeed if the below contents appear. 

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.

Several Factors Affect Report Performance

In software projects users often complain that the report and query is too slow to be accepted. This brings great pressure to the developers.So, what are the main steps in report production process? What stages of this process may suffer from performance issues? How to optimize these stages?

Let's look the steps for report processing, which is shown below:

Figure 1：Report handling process illustration

As shown in the above figure,the report process is:

1. The user selectsparameters for report, and the report engine will interpret the report according to the report template and input parameters. Requests for data computation and retrieval are then sent to data layers.

2. The data layer is responsible for data retrieval, computation and the return of the data. Data layers are normally traditional databases: Oracle, DB2, etc.

3.  The data layer pass the data result, through interfaces like JDBC, to reporting tools.

4.  Reporting toolspresents to users the data they received with visual reports, according to the report template & styles required.

During the whole process of report preparation, each stage in the diagram, report layer and data layer can become a performance bottleneck.

One thing worth to discuss is that in Figure 1, the stage"R2 generate visual report" normally includes some data computation process: data returned from the data layer has to be further processed to meet the report requirement.It’s especially true for those reporting tools with programming capabilities that this stage will consume some computation time.For example: the the custom scripts for BIRT, custom dataset for Runqian Report, and inter-cell computation.

Therefore, we can separate this stage, which is good for us to analyze the factors affecting the performance of the report. After being separated,the process is as below:

 

Figure 2 Report handling process illustration after being further separated

In Figure 2, the highlighted stagesare the main reasons for performance degradation. Obviously, the interpretation of report by the report engine, and the presentation of the report seldom become the key factor for performance issues. Instead, data computation, as well as JDBC data transmission is normally the root of such issues. Therefore, optimization on data computation or JDBC transmission is a more rewarding approach.
As we are clear with the report generation, let’s now deep dig for reasons in the highlighted stages of Figure 2, which are causing the performance issues. 

I.The data layer"D1 executing data computation"stage.

In development practices, report developers often need to write very long and complex SQL statements (or stored procedure) to prepare data for the report.

Developers usually find that it’s difficult to implement complex computation with SQL (or stored procedures), especially when they are related to ordered sets. For example, when computing for products, which ranked in the monthly top 10 products lists, or stocks, whose price rise for last five consecutive days. The SQL statement to prepare data for these report are too complicated and may run for tens or even hundreds of lines.

Since it’s difficult to manually specify the full execution path for the database, the execution of complex SQL statements are often slow. It’s hard for developers to manually optimize these complex SQL statements. This is the main factor that affects the performance of "D1 executing data computation" stage.

Example: Computing for customers who rank in Top 10 revenue contributor in every month.

select Client from(

select * from(

select B.*,row_number() over(partition by month order by SumValuedesc)

rown from(

selectto_char(SellDate,'mm') month,Client,sum(Quantity*Amount) SumValue

from contract

whereSellDate>=to_date('2012-01-01','yyyy-mm-dd')

andSellDate<=to_date('2012-12-31','yyyy-mm-dd')

group by to_char(SellDate,'mm'),Client order by month,client ) B

)C

whererown<=10

)D

group by Client

having count(Client)=(

select  count(distinct(to_char(SellDate,'mm')))

from contract

whereSellDate>=to_date('2012-01-01','yyyy-mm-dd')

andSellDate<=to_date('2012-12-31','yyyy-mm-dd')

)

II.The data layer "D2 JDBC data transmission" stage.

The JDBC drivers from traditional mainstream database are usually less efficient when transfer data stream into Java objects. However, they cannot be bypassed. When handling large amounts of data the performance is not acceptable. Even with new versions of databases, the problem remains. Therefore, for reports against large amount of data, JDBC is often the reason that the report is becoming too slow.

III.The report layer "R2 data re-computation"stage.

1. Many reporting tools handle " R2 data re-computation" and "R3 generating visual presentation"together. The data computation stage includes lot of display attributes(font, color, cell size, etc), which will slow down the computationspeed.

2. The Computational programming in reporting tool often includes custom functions, JavaScript or java language and does not provide complete relational data computation library. Most of the functionality requires programmers to write their own implementation. The result is high cost and lengthy development, as well as unpredictable code quality or report performance.

3. Reporting tools do not have the ability for parallel computing. This means complex task with large amount of data cannot be split into small and separated tasks to be processed by stand-alone or multiple machines.Thusit is difficult to effectively improve computing speed.

Know the causes for low performance issues existed in these stages, we can now consider the following optimization alternatives:

I.The data layer"D1 executing data computation"stage.

To cope with the difficulties associated with complex SQL statement optimization, we can use procedural programming language. In this regard, esProc is a good choice. Its code is more in line with the natural way of thinking, and isseveral times shorter than SQL when solving same issues. On the one hand, it’s easy to develop, debug and maintain, reducing development costs; on the other hand, it’s up for the programmer to select the execution path and process, which is easier to improve computing performance.

With use esProc, it’s very easy to computefor customers who rank in Top 10 revenue contributor in every month:

II.  The data layer "D2 JDBC data transmission" stage

To resolve the issue of slow data transmission with JDBC in traditional database, we can consider storing the relatively large-scale historical data in file system. It’s much faster to access data in file system through esProc, than JDBC transmission from traditional database. Although the data transmission from esProc to reporting tools is also done through JDBC, it’s much faster as esProc’s JDBC doesn’t need to convert the data stream object in any way.

Another possible solution is the use of parallel computing with several esProc subroutines to retrieve data from database through multiple JDBC connections. During actual test against Oracle we found that with 16 parallel tasks the data transmission speed can be increased by ten times. Considering the increased overhead to the database, this solution is suitable for systems where the database is less stressed.

III.  The report layer "R2 data re-computation" stage

1．esProc does not include display properties in its computation. Thus the computing speed is improved .

2. esProc comes with a rich library for relational data computation, which offers great varietyof performance optimization options. Especially when used with ordered sets, programmers can effectively leverage the regularity ofbusiness data to control the execution plan and improve computing speed.

3. esProc comes with parallel computing capabilities, with which we can split large data and more complex tasks into small parallel tasks running on multiple machines. This will effectively improve the computing speed.

Note: for specific performance test measurements, please refer to esProc’s performance test reports.

esProc Implements Report with File as Data Resource

In the real business, quite a few report data is from file instead of database. Take the below case for example: To compute over the attendance data files for preparing the salary payable report, the log file for preparing the user behavior analysis report, and the stock trading record file for preparing the stock analysis report. Because the report usually comes with the parameter, people often need to group, filter, and perform other similar operations over the row data. To serve this purpose, the file should support the re-computation which is unfortunately just what the file lack. Nowadays, these two practices are generally adopted:

Import data to database

Since the file itself is not powerful enough in computing, it is natural for people to consider storing the data in the database, with an aim to leverage the computing capability of database to prepare data for report. As is commonly believed, regarding the solution to get the data source tables ready for most files, we cannot care more about the cost problem.

The expenditure usually comprises the costs in these 3 aspects: time, resources, and administrations.

Time cost. After all, programming does take time (even the load action requires scripting). Moreover, the speed of inserting data into the database is not always satisfactory - more time-consuming for more data volume.

Resource cost. Importing data to the database will definitely take up the expensive database space, and incur the extra database purchase cost.

Administration cost. The flat storage mechanism for database increases the difficult of administration when there are massive amount of data tables, along with the higher pressure and the administration and maintenance costs.

Process file using the custom data source of reporting tool

The typical reporting tools all provide the custom data source function. Programmers can thus code by themselves to implement the data source. With this advantage, the high level languages like JAVA and C# can be used to implement the data computation function for the specific files.

The major advantage of this practice is its flexibility. Theoretically, all computations can be completed by programming. But such programming is never an easy thing. Java and other alike senior languages lack the effective support for operations on set. Without the corresponding class library, a simple summary could require many (looping) codes, not to mention the grouping, summarizing, and other operations after associating.

Comparatively, the free edition product of esProc is a more convenient solution to provide the custom data source for the reporting tools.

How esProc implement report with file as data source

Let’s illustrate it with an example:

The data file is the stock price information for each month (a file for a month, and the file is name as “stock_record_yyyyMM”, for example: stock_record_200901. txt, stock_record_200902. txt…), including the stock code, trading date, and closing price for each stock. We need to query out the respective longest consecutive rising days for each stock in a certain period, along with the date and the closing price on which the longest consecutive days have reached.

The report format is shown below:

Stock Code (code of stock)

TradingDate (trading date)

Closing Price (closing price)

BallooningNum (maximum rise in number of days)

Setting parameters

The “begin” is the query starting month, and the “end” is the closing month. They are sent by report.

Scripts

• In A1, with the parameters for the inputted starting and ending months, compute the involved month sequence, that is, the used file
• In A2, with import, loop and retrieve all file data required for this period
• In A3, with derive, add field ballooningNum to the TSeq
• In A4 and A5, after sorting by the stock code and trading date, group by the stock code
• In A6, by looping, judge if the closing price is higher than that of the previous day, and compute the consecutive rising days for each stock on every trading day
• In A7, with pmax, select the records holding the longest consecutive rising days of each stock, including the date on which the longest consecutive rising days is reached and the closing price. Then, sort by ballooningNum in descending order.

• Lastly, in A8, export the result set for use in report with the function result 

Report calling esProc script to complete the report presentation

The esProc  is packaged into standard JDBC, allowing the reporting tool for invoking 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 call stockRecord(? ,?) (In which the “?” is the parameter, representing the starting/ending years or months) to complete the invoking.

Let’s take BIRT as an example to demonstrate the calling procedure:

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 simple steps. BIRT can use the esProc computing result to directly present the output.

esProc’s Contribution to Report Development

With the constant improvement and maturing of reporting tools, the presentation features of the reports are growing stronger, supporting more and more reporting styles. At the same time, some report development aspects has gradually become bottlenecks. Two obvious ones are: 1) data preparation and computation for reports with complex business logic; 2) report development with heterogeneous and diversified data sources. A new development tool, esProc, can remove both bottlenecks easily. This will reduce the difficulty of report development and improve efficiency. 

Regarding the first bottleneck, let’s see the issue with data preparation and computation for reports with complex business logic. 
In project practice, report developers need to write some very long and complex SQL statements (or stored procedure), to prepare and compute the data for reporting. 

The main reasons are: 

1.The original database structure is normally designed for transactional system, and is not suitable for direct presentation in statistical reports. This results in very complex business logic to get report data from original data. 

2. Even for data warehouse, in which all data has been sorted and prepared to some degree, the structure is not suitable for all reports, especially some new reports needed in later stage of the projects. Usually, more complex computation is needed on the basis of data warehouse before submitting data to reporting tools for presentation. 

Report developers normally find it’s very difficult to do complex statistical computation with SQL (or stored procedures), especially computation related to sequence and sets, eg., computing for products which are top 10 revenue contributors every month, or stocks rising for five consecutive days. SQL statements for preparing such data is extremely complicated and could be tens or hundreds lines long. 

Let's look at a real life example with SQL. The following statements are written for Oracle, leveraging the window function. Other DBMS does not support the window function to the same degree as Oracle, which means the SQL statements written for them could be longer. 

Example 1: Computing for customers who are Top 10 revenue contributor in every month. 

 select Client from(
 select * from(
 select B.*,row_number() over(partition by month order by SumValuedesc)
 rown from(
 select to_char(SellDate,'mm') month,Client,sum(Quantity*Amount) SumValue
 from contract
 where SellDate>=to_date('2012-01-01','yyyy-mm-dd')
 and bSellDate<=to_date('2012-12-31','yyyy-mm-dd')
 group by to_char(SellDate,'mm'),Client order by month,client
 ) B
 ) C
 where rown<=10
 ) D
 group by Client
 having count(Client)=(
 select count(distinct(to_char(SellDate,'mm')))
 from contract
 where SellDate>=to_date('2012-01-01','yyyy-mm-dd')
 and SellDate<=to_date('2012-12-31','yyyy-mm-dd') )

Example 2: computing for stocks rising for 5 consecutive days.

 select max(number_of_consecutive_days)
 from (select count(*) number_of_consecutive_days
      from (select sum(flag_for_rising_or_falling) over(order by trading_days)                days_not_rising
        from (select trading_days,
              case when closing_price >lag(closing_price) over(order by                           trading_days)
              then 0 else 1 end flag_for_rising_or_falling
              from price_index) )
      group by days_not_rising) 

These two examples are relatively simple. For more complex business logic, there will be multiple levels of nesting, and SQL statements with many window functions, running to tens or even hundreds of lines. These complex SQL statements does not follow our natural way of thinking. They are hard to write or debug, and difficult to modify. After a period of time, even the original developer might need extra time to understand.

Moreover, complex SQL statements is normally slow to execute, leaving the end users waiting long for the report. Since it’s difficult to specify the full execution path for the database, it’s usually hard for the report developers to find a way to manually optimize these complex SQL.

As the new generation of development tools, esProc can easily resolve such bottlenecks.

First, esProc is more in line with our natural way of thinking. When used to solve the same problem, its codes are several times shorter than SQL. It’s easier to debug and maintain. The execution process of esProc can be decided by the developer, which means it’s easy to be optimized.
Also with the two examples mentioned above, let’s see what’s esProc's way of implementation:

Example 1: Computing for customers who are Top 10 revenue contributor in every month.

Example 2: computing for stocks rising for 5 consecutive days.



Meanwhile, esProc's provides standard JDBC interface, which means reporting tools can access the result-set from esProc in the same easy and seamless way as they do with databases.

The second bottleneck is the trouble of developing reports with heterogeneous and diversified data sources.

Many complex reports contains data from multiple heterogeneous databases, non-relational databases or even file system. A typical example is the payroll report: basic information about employees are from HR database; salary information is from accounting system; and attendance record is in text file.
All mainstream databases support integration of heterogeneous data sources, but in reality, these functionality does not prove to be handy. Even if they support integration of heterogeneous data sources, the performance is very bad.
Reporting tools can also be used to integrate heterogeneous data sources, but a considerable number of such tools do not support multiple data sources association. For the few of them which does support, the association feature is very limited, and might suffer from poor performance with any increase of data amount. Developers need to take care of the codes for data integration.
However, ordinary high-level languages (Java, C#) used for developing customer-defined data sources for reports lack class libraries for batch data computing. It’s very tedious to develop codes for such computation. A simple filtering summary will require tens of lines of codes, which is time-consuming, and code readability is poor. 

This bottleneck can also be easily resolved by esProc.

esProc can access all kinds of heterogeneous databases, non-relational databases and files. Its complete computing capability can easily associate multiple data sources for computing. The code length is several times, or even ten times shorter than Java.

For example, there is a "Project Payment Schedule Report", in which the data are from project information table project underlying the Project Management System (Oracle based), payment information table payProcess underlying the Accounting System (SQL Server based), and contract information table contract underlying the Contract Management System (SQL Server based) .

With traditional methods, it’s very difficult to join these three tables from three databases through project number projectNo. However, with esProc, it could be done easily. The scripts in following illustration shows that with 3 JDBC connections, oracledb, mssqlFin and msssqlCon, data from 3 databases could be retrieved for easy association with esProc's join and new functions.


Note that esProc's computing power does not depend on a database or other third-party engine. Thus regardless of the data sources, once they are retrieved into esProc, you can rely on esProc's wealth of computing functions and class libraries to do unified computing.

In summary, esProc can easily resolve the two major bottlenecks encountered in report development, and significantly improve development efficiency.