Distributed nature of data is the reality of modern information systems. Customer information may be stored in one database, sales orders may be stored in another database, and product information may be accessed only via a REST web service. In this article, we review how a reporting tool can access data stored in different databases, files, or returned via web service calls, integrate results and present output as if all data were coming from a single table.
Arnica WebReport can query and join data from heterogeneous sources due to its two-tier architecture, in which reports sources are responsible for connecting to databases, web services, files and other sources, while reports based on report sources control data processing and presentation aspects. The two-tier architecture principles are reviewed in more detail in Reports and report sources - two tiers for better business intelligence solutions. The focus of this article is implementation of heterogeneous queries and subsequent joining results obtained from multiple data sources.
WebReport provides connectivity with virtually any source, which can deliver data: relational databases, XML sources, web services, flat files, program components and scripts, etc. To join data from multiple sources, designers create a report source for each source of data. Report source includes properties, which specify how to connect to a particular source of data, corresponding user name and password, URL or file name for web services or files, etc. To test each report source, WebReport includes tools for quickly creating a report against each report source and analyzing data before integrating it with other reports.
When each atomic report source is tested, designer creates a master report source which targets atomic report sources created at previous stage. WebReport exposes data from each atomic report source as a virtual table (cursor); master report source allows joining these tables using SQL syntax, and derive the resulting cursor which becomes the final data product and may be used to build the target report.
What makes it easy with WebReport when connecting data from different sources is that each data source may be designed and tested separately and independently from each other. Reports built against atomic report sources may continue to co-exist with reports created against the master report source, which joins the atomic ones. Data retrieved from individual sources may be analyzed individually, and then joined together for integrated presentation.
Live examples built with Arnica WebReport may be further explored from the Arnica Software demo site.