Querying and Reporting Tools for Data Warehousing - dummies

Querying and Reporting Tools for Data Warehousing

By Thomas C. Hammergren

A querying and reporting tool helps you run regular reports, create organized listings, and perform cross-tabular reporting and querying. Here are some querying and reporting tools to familiarize yourself with.

The Role of SQL

SQL is the official database query language used to access and update the data contained within a relational database management system, or RDBMS.

The roots of SQL go back to IBM and its research labs during the early days of relational database technology. IBM and Oracle were among the first to adopt SQL as the language used to access their relational products (other RDBMSs used different languages that their respective vendors invented).

In the mid-1980s, SQL was submitted for approval to both the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), and during the next few years, other database access languages faded away. Later versions of the SQL standard were published every few years.

The significance of SQL for querying and reporting (and for data warehousing) is that the language has represented a mostly standard way to access multiple RDBMS products.

Each RDBMS product has a slightly different SQL dialect. Although the basic syntax is the same, especially for the most commonly used commands, all SQL dialects are slightly different. In the early 1990s, despite these syntactical differences, several different efforts provided a common gateway to SQL RDBMS products.

The most successful was Microsoft Open Database Connectivity (ODBC). The phrase ODBC-compliant became important to RDBMS applications in the early and mid-1990s. A similar standard for the Java community also emerged in the late-1990s — Java Database Connectivity (JDBC). Virtually all major database manufacturers today have accessibility through both of these standard connectivity interfaces.

Technical query tools

The use of SQL as the basis for most querying and reporting tools was both good and bad for data warehousing. On the positive side, many more product-to-product matchups are possible in data warehousing environments, enabling tools to be provided both by RDBMS vendors and other third-party vendors.

On the negative side, though, SQL is a relatively complex language after you get past the basics. A series of query tools primarily allow users to type in and edit SQL queries. These tools aren’t really designed for end users, though it’s amazing how often they are deployed in end-user organizations.

User query tools

Most end-user querying and reporting tools provide visually oriented, painting, environments that enable users to design screens for report layouts, the data columns desired for the report, or the rows of data that they want to select (only salespeople who have met their quota, for example).

Using all this “painted” information, most tools have increasingly taken a smart query generation approach. Instead of generating a single, overly complex SQL statement that could get you an A in database class but draw a disgusted shake of the head from someone who has done this stuff in the real world, a sequence of SQL statements (usually taking advantage of temporary tables for intermediate results) is generated.

This sequence, in effect, decomposes the query into a more efficient series of steps.

Reporting tools

When end users want more complex user interaction or sophisticated formats, a tool with more reporting features is leveraged. You can find a separation between pure query tools and pure reporting tools. The query tool provides data access, filtering, and simple formatting. If you’re distributing reports across your enterprise or need to generate form-safe presentation, you use a reporting tool.

Like with query tools, reporting tools provide an environment that enables you to create sophisticated layouts that focus on formatting the data retrieved by the database query.