Let data warehouse users hit the open road

Enable and encourage query freedom.

by Dan Higgins

Most of us take for granted the freedom of getting in an automobile and traveling anywhere at any time using a wide range of routes. Travelers are not restricted to the use of a taxi or chauffeur. Drivers are not required to submit a trip plan. Time limits are not placed on trips. Drivers won't lose their licenses simply because they lose their way. The only requirements are to have a valid driver's license and to obey the rules of the road.

Similarly, as business users gain an understanding of technology and their data, shouldn't they have the freedom to query the data warehouse as needed to get the answers they require? In order to maximize the return from a data warehouse investment, you must free business users from the roadblocks of predefined queries, burdensome management processes and constrictive performance-tuning approaches. Such "query freedom" allows the exploration of new business questions and provides new opportunities to gain insight from the data.

Not only for end users, query freedom can also aid application developers. Rapid prototyping (RP) and rapid application development (RAD) benefit from iterative development of queries as business users refine their needs.

Classic application development typically employs a "waterfall" software-engineering approach where you move serially through extensive requirements, design, code, test and deployment phases. But developing decision support system (DSS) applications is much more effective when database and application developers collaborate with business users. Query results can be quickly assessed and improved. Developers arrive at a set of queries that meets the user's needs. Then these queries or reports can be packaged as an application. The ability to quickly iterate through previously unknown queries can dramatically improve the quality and speed for a collaborative RAD or RP process.

Breaking the rules
Query freedom provides business users the ability to acquire answers to any reasonable business question or query against any of the data in the data warehouse (assuming access rights) in a timely manner. It offers freedom from restrictions brought on not only by technology inadequacies but also by restrictive IT processes and procedures. Query freedom means that procedures for managing and protecting the data warehouse must also facilitate its effective use.

Could your database users use a little query freedom? Consider these scenarios:

• There is a large telecom database that contains call-detail records. Accessing this data requires users to supply the date and the originating or receiving phone number. But what if a user needs to know all phone numbers with call durations greater than 10 minutes?
• One data warehouse manager proudly declared having no performance concerns. That's because any query that runs for more than five seconds is terminated.
• What about the DBA who declared the data warehouse "finished" once he had implemented the 26 queries identified in the initial business requirements? He believed that the scope of the data warehouse extended only to providing those 26 queries or reports.

If you or your database users have felt constrained by the limits of your data warehouse, think about this: The most significant business insight-in fact the greatest benefit a data warehouse can provide-is the freedom to ask an increasingly wide range of business questions. These business questions are often complex and unanticipated, yet they must be answered in a timely manner. Sometimes it is our own IT processes or procedures that need to get out of the way!

Learning to drive
A fundamental principle of DSSs and data warehouses is that business users don't know what they don't yet know. In other words, as people begin to work with data and gain an understanding of that data, new business questions arise, which in turn lead to more questions. Queries can change or evolve as business users iterate through this process in numerous ways:

• Adding or removing filter criteria
• Modifying the existing filter criteria
• Adding one or more additional "joins"
• Changing the aggregation or ordering of data
• Changing which columns are included in the answer set
• Changing the derived values in the answer set

In order to effectively and efficiently exploit the data warehouse end users need training. They need training on the technology and just as important they need training on the data. This latter is often overlooked. (See sidebar below on "Querying Responsibly.")

Knowing your vehicle
So how do we make query freedom possible? While technology alone is not enough to enable query freedom, having the right technology is an absolute necessity. When selecting a technology platform, look for the following query-freedom attributes:

• Support for robust data models: Beware of physical data models that are tuned for "known queries" or designed to enable certain tuning features. They may be effective for canned reporting applications, but they often result in reduced flexibility and the elimination of potentially important relationships between data entities.
• A cost-based optimizer that does not require "hints": The optimizer must not balk at complex queries, even when they require dozens of joins.
• Database Views: Views are able to hide complexity and direct business users toward more effective and efficient queries.
• Performance enhancement features: Such features should be implemented by IT and be transparent to the business user, and they should apply to a wide range of queries.
• Query governors: They should be configured to trap business user queries that may be incorrect.
• Priority-based workload and resource management: Long-running, resource-consuming queries must be permitted to run without blocking short, service-oriented, tactical queries.

Driving to success
For decades, business users have been conditioned to believe it will take a prohibitive amount of time and money to implement requested changes. They've also been trained to only ask certain business questions or to request certain reports from IT.

There may be great potential for new business insight, but the burden of making it happen is too difficult. IT says it cannot help until the business users clearly and succinctly define their requirements. But the business users are not certain what they will need or how to define it.

In the end, many business users have simply given up.

How can you encourage business users to break free of past patterns and begin exercising their query freedom?

1. Provide end-user-friendly education.
2. Develop internal "user groups" where people can share new ideas on how they are using the data warehouse.
3. Begin celebrating and publicizing examples of new uses.
4. Learn to expect and accept the occasional "query from hell."
5. Recruit internal "consultants" and implement a proactive help desk that goes out to the users rather than waiting for them to call.

If you want to uncover new business insight from your data and realize greater return from your data warehousing investment, you must be open to new ideas. You must enable and encourage business users who can freely use their data warehouse.

IT will, in turn, be free to enhance and enrich the data warehouse. The relationship and collaboration between business users and IT will strengthen. The enterprise will reap the rewards.

So set your business users free! T

Querying responsibly: The data warehouse "driver's license"

Query freedom does not mean that users are allowed to run roughshod through the data warehouse. Instead, a "driver's license" for the data warehouse can be a useful tool for balancing query freedom and data warehouse management. Most of us remember that rite of passage when we earned our driver's license. For many of us, this accomplishment gave us freedom we had not known previously. We were free to travel farther than ever before. New worlds and opportunities lay before us. But to attain that license and the ensuing freedom, it was necessary to demonstrate an understanding of and experience with the operation of the vehicle and all the rules of the road. We can implement a similar approach to enable "free yet responsible querying" in the data warehousing environment. This might consist of the following components: Training on the technology and tools is analogous to learning how to operate an automobile in driver's education class. It is important to understand the technical tools, but too often companies stop here. And too often the training is not done in the context of the business user's own business needs and data. Just as drivers learn the rules of the road, users need training on the data in the data warehouse. While often overlooked, data training is critical to successful exploitation of the data warehouse. It must include the data entities and attributes. It must include the proper interpretation and application of data values. It must include the relationships between entities as represented in relationships or join tables and, of course, examples of how to use the data. Safe zones are smaller databases, limited in access but fully functional. These provide a safe environment for new business users to hone their skills. Driving instructors are business-user consultants whose purpose is to coach other users through the training and help them apply it to their daily business needs. This will often extend through the initial learning curve. A learner's permit may initially be a system logon ID that allows limited access controlled through views, limits placed on useable system resources and low priorities in the workload and resource manager. The graduation to a driver's license allows business users to use all of the data in the data warehouse for which they have appropriate access rights.

Dan Higgins is director of Teradata Warehouse Sales Support and leads a team of data warehousing and Teradata technology subject matter experts who assist Teradata's worldwide sales force. He is co-author of "Evolving Through Action," a book on data warehousing wisdom.