August 6, 2012

esProc Being Invoked by Report Tool via JDBC

Robust, platform-independent, and easy for clustering, load-balancing, maintenance, and extension, Java reporting tool has been widely used in various applications. Therefore, esProc provides the JDBC interface for external use and being called by Java reporting tools, as shown in the below structure figure:

For a system in which the Java reporting tool is adopted, esProc is an ideal choice to perform the complex computation, compute with multiple data sources, and clean the dirty data sources. The reporting tool will receive the result returned by esProc via JDBC regarding esProc as a database. Then, the ultimate data can be rendered by the reporting tool boasting the form & chart design, beautiful style, query interface, entry and commit, export for print, and other advantages by nature.
Please find some scenarios below about invoking esProc by reporting tools:

Data is easy to render and algorithm is hard to implement

esProc is quite ideal to handle the complex computations on mass data in the reports.
Different men may have different areas of expertise. Same law applies to the tools. Reporting tool is good at data rendering. The key advantage of reporting tool is to render the data in a way compatible with business style. However, reporting tools can only offer limited support for the complex computation on mass data. By compassion, esProc can help reporting tool solve the most complex computation of the business reports by working with the reporting tools.

Computation with Multi-Datasources

Most reporting tools only allows for one data source. In order to handle the computation with multiple data sources, the data will have to be pre-processed through the complicated SQL/stored procedures in the background. In the background, multiple data sources are merged into one to be submitted later for report use. esProc is ideal to handle such situation.

The applications of multi-datasources may not only refer to multiple query results of one database, but also the query results from various databases, and even the data from the database or the Txt/Excel files. Many computations on such data are not supported by SQL/stored procedure, or the support is limited. esProc is good at the interactive computation with multiple datasources.

Dirty Data Source Need Cleaning

If the data source is quite dirty and not fit for the reporting tool to use directly, then you can use esProc to clean up and output the result to the reporting tool.

Dirty data is quite a common thing: two duplicate employee records, the employee name is null, a document having several UIDs, the date is mistaken to “2011-13-01”, and the digit “10” is taken to be “IO” by mistake. Such dirty data cannot be used directly and must be examined and cleaned according to certain rules. esProc can clean these dirty data well, and the reporting tool can be ensured to receive the clean data from esProc.
In this last example, a communication enterprise needs a report that will display the top 10 states of the highest turnover and the respective rankings of the communications product in these states. The turnover in each city and the administrative divisions are stored in different physical tables.
The computation on this report is too complicated to handle well for the traditional BAND style reporting model. The obvious difficulty is to convert the 3-level relation of “state – county, county – city” in the administrative division to the two-level relation of “state – city” so as to facilitate the summarization of the turnover table. The individual reporting tool is hard to implement such complex computation by itself. esProc is more ideal to solve such problem.
  1. Reduce the 3-level relations to the 2-level relations by the loop statements of esProc.
  2. Use the align function to perform the align action and summarizing computations in a mode of 2-level relations regarding the turnover data in each city. Summarize to the state level, and get the result named detail is just the product turnover of each category in each state.
  3. Use the Group function to group the detail by state, and get the list of turnover of each state. Retrieve the top 10 states with the name top.
  4. Leverage the previous computation and use the align function again to align the detail by top. The result, detailOfTop by name, is composed of the top 10 states listed in order as well as the turnover data of each product in each city.
  5. detalOfTop is a result set that is easy to render. Just return the detalOfTop to Java reporting tool via JDBC.
  6. The reporting tool will output the result in the required format.
Leave ur comments below!

No comments:

Post a Comment