To date, the reporting tools have been proven
and developed pretty well. We have embraced a lot of emerging tools like Qlikview,
Tableau, Spotfire, and still have the choices of classic tools like Sap Visual
Intelligence, JasperReport, and Cognos. However, in developing the report,
there is still a key obstacle – data source computation.
The
goal of data source computation is to compute out the convenient and usable
result set for report. SQL/SP is the traditional method to perform the data
source computation. But it is a bit difficult for SQL/SP user to design, debug,
and migrate.
What’s worse, with regard to the computation with various databases and
non-database data sources, this traditional method becomes useless, let alone
the JasperReport and those tools that is restricted to use a single data source
only. To override the last obstacle, we need to employ some other techniques,
and the typical ones include data warehouse, data source computation layer, semantic
layer, table-joining, and API programing. I would like to share my viewpoints
on the respective pros and cons of these methods according to my personal
experience.
Data Warehouse. As a way to build a single consistent data view with ETL, data
warehouse offers lots of relevant tools that is already proven and
well-developed, for example, Teradata Fastload, DB2 data warehouse, and Oracle
Warehouse Builder. The data warehouse has many advantages: cross-database
computation, non-database computation, historical data tracking, high
performance, and low pressure on business database.
The
major drawback of data warehouse is the high cost that mainly resulting from the
building and maintaining, but not the product purchase. The data warehouse
requires the double expert who is skillful in both IT and business. They must be
excellent in Perl/VB Script/JAVA/SQL/SP and business logics. The data warehouse
users have to perform the complicated data splitting and consolidating, design
the norm form and the algorithm for incremental update, and prioritize the
task-scheduling across multiple levels. To keep the data warehouse efficient
and usable, more than one ETL processes are required. The data warehouse may
easily get failed due to the uncontrollable cost.
Data source computation layer. As an emerging method in recent years, the data
source computation layer tries to root out the problem in data source
computation through a common method. With the computational scripts
specially-designed for the structured data, the data source computation layer
supports data retrieval from various databases and non-database data sources,
and even the interactive computations between heterogeneous data sources. Plus,
it offers the professional development tool and perfect debugging tool to
reduce the difficulty of complex computation. All in all, this method is more
efficient than SQL/SP is. As for the portability, this method is better because
it is independent of any specific database or report by nature.
esProc
and R are the typical data source computation layers. esProc can output the
result via JDBC for direct referencing by report. But esProc does not support
the correlativity analysis and other models. Contrarily, it is awkward to
invoke R in the report, though R supports various fixed analysis models.
Typically,
the full memory computation is adopted in this method. So, its performance on standalone
machine is poorer than that of the data warehouse. However, both esProc and R
are excellent in handling the distributed computation because they all support
the big data computation model like Hadoop.
Semantic Layer. The semantic layer was introduced when the data warehouse becomes disappointing.
The semantic layer redefines the physical data model used to be quite difficult
to understand. Regarding the semantic layer, the database\text\Excel\XML will
not be differentiated strictly. So report designers can operate the data in a more
user-friendly way. Sap Universe and Qlikview are the most characteristic ones.
Both
the semantic layer and the data warehouse are designed to build an easy-to-use
data source, and thus their advantages are basically the same. However, building
the semantic layer consumes fewer resources and less time because it requires
less procedures and comparatively more agile. It is established and maintained
at the cost higher than the cost of data warehouse but less than that of data
source computation layer.
The
drawback of semantic layer is mainly the insufficient computational capability.
This layer works with the wizard as the core, complemented with some elementary
functions of data source consolidation and computation column. However, it
lacks the special-tailored structured data computational script. Therefore, the
semantic layer can hardly perform the data source computation independently.
Additional assistance from simple data warehouse or ETL is often necessary. The
full memory computation is also weak in performance. Lastly, the semantic layer
is often bundled with reporting tools and thus poor in portability.
Table-joining. This is one of the basic
functions of semantic layer. In many reporting tools, it is defined separately
as a simple way to solve the multi-data-source problems for the single-source
report. For example, the JasperReport and the Birt only support a single data
set. If the data from Oracle and MSSQL are to be represented in a single report
at the same time, then you can take advantage of their table-joining
functionality.
The
table-joining is superior to other methods for the lowest workload and human
resource costs. But, there are comparatively more feature restrictions. These
restrictions include: only the easiest joining can be performed, the normal
data source computation is not allowed; insufficient support for the
non-database data source; the bundled reporting tools and database are not easy
to migrate; The performance is quite low due to the full memory computation;
API Programing. It is the
reporting tool API to compute the data source programmatically, and return the
result set ultimately. Most reporting tools provide such methods as CDS frame
of SAP, and Bean data source of Jasper.
This
method has the utmost flexibility, capable of solving any data source computation
problem theoretically. Among all these methods, this method involves the
greatest development complexity.
In
order to access the non-database data source, Java and other senior languages
is generally adopted in this method. These languages may require chunks of code
to implement a simple algorithm, which is unlike the languages designed for data
computation, such as SQL, esProc, or R. So, even a simple algorithm could be
quite difficult to implement and require great workload. As a matter of fact,
its existence value largely lies in its role as a complementary method to the semantic
layer or table-joining.
In
conclusion, the above 5 methods are fit for their respective scenarios: the
table-joining is fit for the simple joining operations between databases; the
API programing is fit for the simple non-database computations; the data source
computation layer is fit for the computation requiring great portability or the
complex data environment; the data warehouse is fit for the LAN environment
with great performance demand; and the semantic layer is fit for those business
personnel processing massive amount of simple reports.
