Skip to Content

Why Ad-hoc Query is a Dead End?

This week, I spoke to a Web Intelligence customer who claimed to have found the perfect workflow for his ad-hoc users. His perfect workflow meant that almost none of his Web Intelligence users created their own queries but still were able to conduct ad-hoc analysis and ad-hoc reporting. Unfortunately, this evolution away from the historic “ad-hoc query” approach, while obvious to many WebI customers, is still painfully slow for others to embrace. I’ll explain what this customer’s perfect workflow is further below, but first some background.

For years industry analysts have categorized Web Intelligence as an “ad-hoc query” tool.  This term unfortunately leads some customers to pigeon-hole the use-cases that WebI supports. Ad-hoc query implies that WebI users must create a query to retrieve specific data when they don’t have a report to respond to their business questions. As a result, BI administrators target certain business users and train them to use WebI to define ad-hoc queries.

Here’s why this categorization is less than helpful. At the very least, query is an incomplete concept in terms of actual requirements. Rarely does someone generate a query then stop. Invariably, the real goal is to make sense of the data retrieved, to answer business questions and then apply that knowledge somewhere else (e.g. sharing through copy/paste to building a dashboard or report). So to categorize a product as an ad-hoc query tool is like saying a fishing boat’s goal is only to locate fish (and not catch it, freeze it, bring it to shore, etc).


But more importantly, the act of defining a query is a significant barrier for most users. In all but the most simple query needs (which likely have reports that already exist) it is just hard to define a query. It requires deep knowledge of the data source – the data behind the measures, dimensions and details – as well as the mechanics of the user experience – how to define filters, subqueries, intersections, etc. And if you understand the data and how to use the query features, it also requires defining the logic for a query – which, sadly, can often be beyond the conceptual grasp of many business users. Even simple business questions can involve relatively complex queries, e.g. “What is store revenue for states with $50M+ in total sales?” Why do we expect the average business user should be capable of overcoming such hurdles?

“Well,” you might ask, “if they don’t define query, where do these non-querying users get their data from?” The answer is: Reports that others – experts from IT and power users from the Lines of Business – create to answer a broad set of questions. These reports are not narrowly defined like an ad-hoc query might be, but enable a broad range of questions to be answered. Lines of Business users who have more narrow questions can then use the reports they receive to conduct ad-hoc analysis and then ad-hoc reporting on top of the documents and analytics they receive.

So, this was the magic use case I referred to in the opening paragraph: BI content is authored by the few capable of identifying broad sets of requirements for Lines of Business and skilled at the science of query creation and the art of laying out reports for easy interactivity. The end-users consuming that content are not encouraged to create net-new queries, but instead to use input controls, formula functions, sorts, the ranking button, turn tables to different charts, etc. to answer their questions. My experience, and I have years of research to back it up, shows that interactivity on top of pre-defined reports is a much more successful way to introduce fact-based decision-making to business users.

I hope analysts finally change the name of the category. I’d love to see an “interactive consumption” tool” category emerge to replace it. I’m open to other terms, but “ad-hoc query” is a term that should just go away.

You must be Logged on to comment or reply to a post.
  • hi,

    One important message is: you have to know data source and nature of data. Usage of ad-hoc queries is usually result of unidentified information requirements during implementation process. I think that each even simple report should be somehow proved by team (business client and experienced data analyst).


  • Isn’t Explorer a Better option for users in that environment? You could use Webi or crystal for the standard reports, and give users access to a different tool that allows for more “exploratory” requests?
    • Good question. I think the tools are more complementary than an “either/or” decision. Explorer clearly has blurred the concept of query by focusing on search and faceted navigation as the alternative to a specific query definition step. With Explorer, you can’t do the more advanced data retrieval WebI enables (e.g. subqueries, intersections) but that’s OK because here we’re talking about the garden variety business user who don’t need to go so deep.

      I like the direction Explorer and WebI teams are taking to enable content be moved from Explorer to WebI. In this way, the results of Explorer navigation can leverage a bit more analytical capabilities afforded by WebI such as moving around columns, sorting, defining variables that are not necessarily advanced but involve constants or simple functions and operands. And of course, by complementing Explorer navigation workflows with WebI’s layout and sharing workflows, you enable Explorer users to better communicate the results of their work.

      That being said, in my opinion Explorer is great when you have a specific question in mind that demands one table or chart or even one specific data point. Well-designed WebI reports, by nature of multiple tables and charts and broad interactivity capabilities, are much richer than Explorer navigation results and enable one to respond to a much broader set of business questions.

    • Yes, there are lots of definitions but I don’t find these too helpful. I’m not sure what the alternative is to the statement “created by a tool/human”?  And at least for the WebI world, “never saved to run again” and “is only there for them moment” frequently don’t correspond to reality — ad-hoc queries *are* frequently run again and do persist. Especially when the ad-hoc queries lead to “ad-hoc reporting” which is then saved and shared.
  • I may and may not agree with this. The query creation can be made simple if the universe is designed to meet the same need. Yeah, it is not always possible to design universes which are context free or has contexts of the same grain.

    • Fair point. While I should admit this post was a bit of a provocation to those whose reflex it is to emphasize the use of ad-hoc query, I recognize that some have put great effort co-designing universes with business users so others, sometimes even untrained at query design, can get the data they want.

      I’d be curious, however, to know how often these users create new ad-hoc queries that pull net new content versus queries that are simply slight variations of a theme e.g. a slightly different time slice or product range or region. Sometimes this means that, when they want historic data, users needlessly hit the database for retrieving data already in a different report. And because the query results by default puts content into a big table that requires rework (e.g. rearrange or remove columns, filter, change a number format, turn to a chart, etc), users likely spend significant time making the results digestible as with other finished BI content.

      I still maintain that the ad-hoc query frequently prevents efficent use of BI assets because of UX challenges simple prevent the use or because users have to repeat analysis and reporting tasks that are likely already built into to existing reports. A better approach is to build reports that enable easy interactivity after they’re opened. As a concession, perhaps we should train not on creating new queries but editing of existing queries attached to the reports they want to edit (e.g. how to remove/add content or tweak filters)? At least this would push users away from the “start from the blank slate” problem.

      • I agree with you when the BI system is very young. Over years, the ad-hoc querying ability really helps self-motivated end users to create some very interesting analysis. When the maturity of the users become better, they are better positioned to query as per their need than waiting for a BO developer to realize the potential idea.

        Mohanraj CP