17.10 - Usage Considerations: Ad Hoc Queries - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

In the data warehousing world, the phrase ad hoc query generally refers to a query composed at the keyboard for immediate performance (as opposed to a permanent query stored in a macro, stored procedure, or SQL application that is performed over and over again without alteration). Such a query frequently undergoes careful, extended planning before being performed for the first time and then is often revised and refined interactively until the desired response is returned. The goal of this type of ad hoc query is not the reporting of data, but the discovery of information.

Ad Hoc Queries and Enterprise Tactical Support

Tactical queries are focused on operational decision making rather than enterprise discovery or bookkeeping activities. They typically have the following characteristics:

  • Relatively simple syntax
  • Direct access
  • Highly tuned
  • Have expected response latencies on the order of 20 seconds or less, and are often expected to return a response in less than one second.

See Design Issues for Tactical Queries for many of the special considerations you must take into account when designing to support tactical queries.

Ad Hoc Queries and Enterprise Discovery

Usage Considerations: Simple and Complex Queries established that discovery is the critical focus of data warehousing, not simple reporting. The trends fueling the data warehousing revolution are based on the need for information that can be acted upon proactively. Report-oriented systems rely on predefined questions that produce reports of what has already happened. This type of information can only produce evidence that a situation that has already occurred must be reacted to.

A data warehouse must have the capability of performing high performance, complex, ad hoc queries if it is to capitalize on the advantages of warehousing its business data. Furthermore, that data must be available for analysis in detail, as well as summary, formats. The value of data warehousing comes from being able to ask unplanned questions on detail data.

Business Situations That Drive the Need for Ad Hoc Queries

Two general situations typically drive ad hoc queries: exploratory analysis to discover business opportunities and detailed analysis of why some complicated event that had a negative impact on the enterprise occurred. You must be able to ask questions as they present themselves in either situation and the data warehouse you use for your analysis must be capable of accommodating that need.

Consider the search for new business opportunities. The following table lists some likely business opportunity searches by industry:

Industry Typical Query Focus
Financial Cross-selling opportunities
Retail Market basket analysis
Communications Calling patterns indicating high risk of losing a customer to the competition.

Now consider the “what went wrong” type of analysis. The following table lists some likely scenarios for this type of ad hoc query by industry:

Industry Typical Query Focus
Transportation
  • Over capacity
  • Under capacity
Communications Sudden customer attrition

Businesses cannot know what questions they will need answered in the future, but they must be able to ask questions that permit them to influence the future positively. Neither issue can be dealt with by means of an analysis of the data warehouse if the system does not provide support for complex ad hoc queries.