Develop reports efficiently with esProc

1 Abstract

Report-developing is the essential module of many web application systems, and it’s still the basic function of most of the BI projects. With the coming of the data era, the data comes from increasingly diverse sources (text, excel, MongoDB, Redis, ES…), which brings great challenges for the data preparation of reports. Traditionally, it is better to first put the data out of the database into the database, and then use the computing power of the database (writing SQL or stored procedures) to prepare the data for the report. Due to the dependence on pre-import, the real-time performance of reports can not be guaranteed, and the development process of reports has also been lengthened. With the increasing demand for reports, the database has become more and more bloated, and the management cost has been rising.

But if we use these data directly for report development, it is always time-consuming and laborious, and the performance of the final report is often not good enough. As programmers who are familiar with report development know, reporting tools provide only a few simple capabilities for computing such out-of-database data. When the computational requirements are complex, they need to be extended out of reporting tools and implemented in a user-defined way. This kind of computation is usually implemented in a high-level language (Java, .net) by hard-coding, where strong data programming experience is required, often beyond the capabilities of ordinary report developers. The development cost of high-level language implementation is very high, and it is not reusable.

Even when data is in a database, computing complex reports often requires advanced window functions or stored procedure, which are exactly the shortcomings of open source databases(mysql, hive…). It is much better for commercial databases in this respect, but the task is still not easy to implement. Usually, advanced extensions of SQL are needed. Since different database vendors have different SQL extensions, experts who are proficient with certain databases are needed to complete the task. It’s not easy to get familiar with all kinds of databases and become a master of SQL. How can ordinary report developers solve these problems quickly in the same way? Besides, stored procedures and databases are coupled together, which will bring huge overhead to database operation and troublesome maintenance.

If there is a computing engine with the same computing power as the database, without importing data, directly calculating data from various sources, providing general high-level window functions and stored procedures, preparing data independently for reports, and solving the above problems, it will greatly improve the development progress and application effect of reports.

The above mentioned is the original intention of the design of the integrated version of esProc. Here, by introducing its application structure, esProc’s ease of use is illustrated with examples, and the practical value of esProc in the application scenario of the report is demonstrated with the Open Source reporting tools.

2 Application architecture


Just like when database acts as the data source of reporting tools, esProc can also be the data source for reporting tools through the JDBC interface when it is embedded in the reporting tools. Only that SQL is executed when the database is connected, and SPL (esProc script language) is executed when esProc is connected to the reporting tools.

Please download the PDF to see more information.