support

Multi-database Source Integration in the Unisource Reporting Tool

Target:

The customer order data is stored in ERP system and the customer information is stored in CRM system. Now we need to create a report to present the annual sales amount of each customer and the growth rate compared with the previous year. And the available reporting tools, such as, iReport, Crystal and BIRT, only support single data source. Below is the desired layout:

esProc_demo_unisource_report_1

It is difficult to achieve by using the traditional programming method, while it becomes easy and simple with esProc.

1. Computing structure

Design a cellset file in esProc IDE to conduct cross-database operations. Then make the reporting tool access the cellset file via standard JDBC. See pic below.

esProc_demo_unisource_report_2

Note: Like accessing an in-memory database like HSQL, the reporting tool will indentify the cellset file name as that of the stored procedure.

2. Implementation process

esProc can connect to multiple data sources simultaneously. In the pic below, there are two data sources: Oracle and MSSQL.

esProc_demo_unisource_report_3

esProc can execute multiple SQL statements, such as A1 and A7. Different data source names represent different databases:

esProc_demo_unisource_report_4

Through a series of operations, data in A1 and A7 has been combined into a single dataset. The detailed data is on the right. A11 indicates exporting A9 to JDBC (Multiple result sets can be exported). It is shown as below:

esProc_demo_unisource_report_5

Now you’ve created a single JDBC data source for the reporting tool. In the pic below, the esProc’s exclusive JDBC Driver and URL are used.

esProc_demo_unisource_report_6

Next, create a report. For example, pick the template Coffee.

esProc_demo_unisource_report_7

Then, Enter SQL in wizard. Notice that “p4” is the cellset file name, which is used as the stored procedure name.

esProc_demo_unisource_report_8

Then, we can see the execution result as below:

esProc_demo_unisource_report_9

The subsequent tabulation process follows the general steps of report building. Here we won’t go into details. Let’s view the final report:

esProc_demo_unisource_report_10

We can see that esProc enables the integration of data from multiple sources. This is because esProc not only provides all the capabilities of SQL, but also supports cross-database computing.The following contents are syntax features of codes mentioned above.

3. Cross-database computing

In the pic below, A5 completes the calculation on Oracle database: the annual sales amount of each customer and the growth rate compared with the previous year. And A7 gets customer ID, Name, Country from MSSQL.

esProc_demo_unisource_report_11

And A8 joins A5 and A7 by ID:

esProc_demo_unisource_report_12

It is a cross-database computing, which is equivalent to the SQL left Join. Of course, SQL only enables join within the same database, while esProc supports cross-database join. The join result is shown on the right side. Click on the blue font to see the internal members, for example, the first line as shown below.

You may find that unlike SQL, esProc divides “join” and “select” into two steps, e.g. A9. It performs “select” based on the result of “join”.

This step-by-step method is particularly suitable for complex database computing. Another example, the result of A5: the annual sales amount of each customer and the growth rate compared with the previous year. You must write all the complex computations at one time and this requires high technical capabilities if you are just using SQL. It is much easier to do it with several simple expressions as explained in the next feature.

4. Step-by-step computing

Step-by-step computing is the basic esProc ability. See the pic below:

esProc_demo_unisource_report_13

A1: The original order data.

A2: Reference A1 and group it by Client. Click on the cell to view the third member:

esProc_demo_unisource_report_14

How to summarize data of each group by year? It is A3: regroup by year on the grouped data and summarize the sales amount for each subgroup. Notice that “~” represents the current member and the result of the third group is shown on the right as below:

esProc_demo_unisource_report_15

A4: Add a column to compute the growth rate compared with last year. The description in business language is: (Current year / previous year) -1. Please notice the third member as below:

esProc_demo_unisource_report_16

Note:

sAmount: Current member,current year

sAmount[-1]: Previous member, previous year

A5: Union members of all groups. Now the processing of data in Oracle is completed.

esProc_demo_unisource_report_17

The step-by-step method can divide a complex computing object into simple computing steps. It is difficult for SQL to do complex database computing because it does not support the step-by-step method directly.

Complex database computations are common during report data preparation or data processing, and you can use esProc to handle them.

Besides the cross-database computing and step-by step computing, esProc has many other features, such as grid-style scripting, ordered set handling, complete set orientation, objects reference etc. These features make esProc a more capable business computing developer tool.