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 |
|
| 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.