Ad Hoc: The Way We Live. The Way We Work?
by Brad Marshall
Published July 3, 2007
While visiting an extremely large and well-known communication company recently, I was amazed to see firsthand the painful process business users must go through to get actionable information out of their data warehouse. The group of users was in the marketing and promotional department, and their jobs involved analysis of customer demographic data, matching the features a customer is already paying for with the current promotions in an effort to sell and bundle more services and features. The process went something like this: The business unit had to meet with a business analyst to describe the information needed for this analysis. This business unit had several individuals who were all responsible for varying aspects of the marketing of promotions. Once the business analyst understood their request, they marched that request over to the IT group responsible for the data warehouse. When they translated this request into technical terms, the IT group would manually develop and execute SQL statements against the data warehouse, pull the data in and manually format it, and then pass that report back to the business analyst, who would then hand it off to the requesting user. The average turnaround with this process was three business days, and in some cases it took a week.
We are creatures of habit. As a rule, our lives are built around frameworks: for instance, the average adult wakes up around 6:00 a.m., is at work two or three hours later, plans to come home shortly before 6:00 p.m., and goes to bed shortly after the late local news broadcast.
For many of us, myself included, this structure helps to define our actions, but it is deliberately open-ended in its approach. Few of us would want to see our whole days so structured, without the capability to respond to real-life opportunities or challenges as they arise. A last-minute weekend getaway to the lake might not be possible. Picking up a sick child at school would have to be scheduled. The very idea of an impromptu dinner out with friends or family after work would fly in the face of the imposed structure.
We live much of our lives on an ad hoc basis (your kids’ after-school schedules notwithstanding). This is not bad; it gives us the ability to flexibly respond to events, whether good or bad, with the information we have at that point in time. Total chaos is never a good thing, but maximum flexibility, adaptation and information are critical. In fact, they’re beneficial in many instances: have you ever been upgraded to first class at the check-in counter when you fly, or had your average hotel room upgraded to a suite? Ad hoc decisions, in many cases, determine the difference in customer satisfaction.
We expect this kind of service from the companies with whom we do business, because we are the customer, and the customer is king. Which raises a valid question: do you provide the same type of service to your customers? More importantly, is your company equipped with the means to make salient information quickly available as part of the process, so that the correct decision can be made?
Importance to a Business
Successful companies in this area generally exhibit three characteristics: quality, velocity and agility. Quality is a given for any reputable business, as part of its very survival strategy. This leaves us to examine velocity and agility, specifically as it relates to your business intelligence (BI) solution and the very nature of the information we use to make wise business decisions.
An example: In the steel industry, there is a term called freight equalization, which means occasionally matching a competitor’s lower freight costs in order to obtain the business. If you are shipping a truckload of steel from
Typically, this decision is made by an inside sales representative and should be made while on the phone with the customer. Several things factor into the decision process:
- How valuable is this customer?
- How much have they bought from you this year?
- Are they generally buying products from you that the competitor in
- Is the margin on this order enough to cover reducing the freight costs?
Remember, the customer is king; if it takes an hour or more to research and make this decision, the customer may decide to go elsewhere. Velocity and agility; the capability to make fast, agile and wise decisions can make or break us in the business world. Most anyone can make a quick decision and most anyone can make an accurate decision, but does your BI system allow them to make an accurate decision quickly?
The capability to execute ad hoc queries against your decision support systems will empower your users to begin making these types of decisions. It will also increase the adoption of your system as users begin to recognize the value. Who knows your business better than your employees? Why limit them to standardized reports that only provide a single view of the world? Allow your users to define the information that they need as they need it, giving them the power to look at trends and compare information that is normally not seen together as a way to make the immediate decisions that can positively impact your firm’s bottom line.
Typical Environments
Historically, only upper management has used BI solutions to look at long-term, historical trends. While this has been the catalyst behind the growth of data warehousing and analytical systems, we have now reached a tipping point with both technology and customer expectations where decision support systems must provide value to those people who make both tactical and strategic decisions throughout the day. Claudia Imhoff has described this move to operational or real time BI as “shortening the latency between when a business event happens and an action is taken.” In the example above, it would be the time between when your customer asks you to reduce the shipping costs on the truckload of steel and when you reply.
The three main components to operational BI are well documented:
- Data latency (the time it takes to get data ready for analysis),
- Analysis latency (the time it takes to receive the results of the analysis), and
- Decision latency (the time it takes for the person receiving the results to understand what action must be taken).
Performed properly, enabling ad hoc queries throughout the enterprise can allow accurate, appropriate decisions to be made in a rapid manner. In addition, the benefits of ad hoc queries will be quickly recognized throughout the enterprise, and more departments and lines of business will want to take advantage of them.
The very thought of opening a system up to ad hoc queries leaves many database administrators shuddering. In a typical environment, data is loaded from transactional systems throughout the day, while users are trying to run reports. Generally, the two do not get along very well. Throw ad hoc queries into the mix, and the prospect of one big mess has led many system administrators to restrict or even cancel the ability to perform ad hoc queries. Cubes have been tried as a stopgap solution to shift the workload away from the database; they can be useful and have their place, but do not provide the flexibility and level of detail that most users require. Cubes also tend to multiply over time, making them virtually impossible to manage.
Characteristics of an Ad Hoc System
Let’s allow experience and technology to be our friend and take a look at some characteristics and proven methods of high performance systems that will transform our BI solutions into strategic assets.
First and foremost, systems must be friendly to end users. A good semantic layer is necessary to present the data in a format that users can easily understand. Most BI software provides this layer of metadata, but it is not always implemented with the end users in mind. Implementing this layer can reduce the amount and time of training required and fosters an environment that is conducive to generating ad hoc requests. Secondly, the system must perform well on a consistent basis. If users are to rely on a system to make decisions, it cannot perform at blazingly fast speed one time and be sluggish the next. High performance is challenging but rewarding. Look for ways to summarize, cache and partition data in advance as a way of ensuring consistent, rapid access to the right information and delivery of answers. There are many technologies to assist here, such as indexed views, materialized views and some innovative software approaches to aggregate navigation, which can shield your users from the complexities of presummarizing and caching data. Another characteristic worth mentioning is continual improvement. The amount of data we collect and analyze continues to grow exponentially, so there must be cycles of continuous improvement.
Other key points:
- If the system is providing value, then the number of users is likely to grow as well; more people will want to access what works. Pay special attention to historical query patterns in order to stay ahead of the curve.
- Think “scale out” as opposed to “scale up.” A scale-out architecture will not only allow the system to grow as needs dictate but will lower upfront costs.
- Have controls in place to protect the system against runaway queries. This has traditionally been a problem, especially with new and untrained users who might mistakenly ask for 10 years’ worth of history. Use default constraints and let the user make a conscious effort to remove them, and have multiple copies of frequently queried data so you can move it offline for refreshing without impacting user access.
While this may belabor the obvious, it must be said: one size does not fit all. I shake my head, repeatedly read comments from people who should know better, such as, “Ad hoc BI is killing us,” “We don’t want the majority of our users doing ad hoc queries,” or even, “We know what information our business users need to do their jobs so we create a set of standardized reports and spend time educating them on how to use those reports.” That is directly in conflict with what businesspeople need, increasingly, on an everyday basis. They don’t want restrictions; they want tools that enable them to do their jobs more effectively, more efficiently and more profitably as a way of maintaining a business advantage.
A well-rounded solution that includes long-term strategic trend analysis, standardized or parameterized reporting, and ad hoc query capability will give your solutions and ultimately your business users the velocity and agility they need to do their jobs better.
Brad Marshall is the director of Commercial for Teksouth Corporation, a provider of data warehousing and business intelligence solutions. He has spent 18 years in information technology, with the last 12 focused on e-business and data warehouse technology solutions. He may be reached at brad.marshall@teksouth.com.
