David Raab Article Archive

True story: I recently received an e-mail from a friend who is training himself to be a database marketer. He has taken courses in being an Oracle database administrator, learned about lifetime value and segment analysis, studied advanced data mining systems, subscribed to database marketing journals, joined the Direct Marketing Association, attended conferences, and learned as much as he could from leading consultants. He wanted to know what else he should be doing.

It was tempting to answer Get A Life, although in fact my friend is a devoted husband and father. My actual reply was that he should get some hands-on marketing experience to balance the technical expertise he is developing.

Whatever the wisdom of my answer, my friend’s question is really a condemnation of the software industry. If developers provided systems that were truly easy to use, people who wanted to be marketers would not have to spend their time mastering the underlying technology. It is as if you could not drive a car without learning to be an auto mechanic.

Perhaps the most glaring shortfall in usability has been query generation. Older marketing systems sometimes used specialized query languages that were tailored to marketing requirements, but newer systems nearly all rely on Structured Query Language (SQL) to access standard relational databases. SQL is ill-suited for the sorts of complex queries that marketers routinely generate. This means it takes an extremely skillful programmer to have any hope of returning the correct answers in a reasonable period of time. Most marketers lack the training or inclination to develop this level of SQL skill. Even “point and click” query builders, which guide the user through the process of creating a SQL query, are limited to fairly simple questions.

One solution is to simplify the problem–either by adding new functions to SQL itself, or by using “dimensional” database designs that let simple SQL queries answer complicated questions. The first approach has been taken both by individual vendors who add proprietary extensions to the SQL language, and by the SQL standards-setting body, which adds new functions to the standard definition of SQL itself. Unfortunately, vendor-specific enhancements are often not available to third-party query programs, or may require custom coding that make it harder to switch from one database to another. Changes to the SQL standard take a long time to reach the market: many vendors are only now releasing products that conform to the standards set in 1992.

The “dimensional” or “star schema” approach involves storing data in a structure that is tailored to the types of questions that marketers ask. This means creating a central “fact” table with the information people typically request (revenues, costs, etc.), surrounded by “dimension” tables holding the ways information is typically broken out (by time, geography, product, etc.). Queries that involve the intersection of dimensions (“this product in this time period in this region”) are easier to write in SQL when the data is organized in this fashion, and even more complicated queries (“compare sales this year vs. last year”) become somewhat more manageable. Dimensional structures also lend themselves to simpler interfaces, such as pointing at bars on a graph or at rows and columns in a table. But the dimensional approach takes careful design and still requires complex SQL when the questions get really interesting.

The alternative to simplifying the problem is to hide its complexity. This is the approach taken by most of today’s advanced end-user query tools, which present the underlying data in ways that are more comprehensible non-technicians. In the systems, generally called “managed query environments”, a technically-skilled administrator might assign meaningful names to the cryptic titles of physical database columns or define simplified hierarchies to organize data elements spread across many different tables. (These systems also usually have extensive features to display data as reports, charts, crosstabs, and even multidimensional pivot tables, but that is a separate discussion).

While user-friendly naming techniques can make the underlying data easier to comprehend, they do not by themselves simplify the execution of complex queries. A more powerful approach, taken by products like BusinessObjects (Business Objects, Inc., 408-953-6000; www.businessobjects.com), is to let the administrator define business concepts, such as “credit-worthy customer”, through complex SQL statements. This way, a query that involves such a concept can easily be executed by a non-technical user, even though the answer requires complicated SQL. Similarly, these tools usually let the administrator define complex functions or calculations–say, a lifetime value calculation or model score–that can be used in queries or reports. Some of these tools also let end-users define their own stored elements, although this option is only meaningful for users who are themselves technically adept.

For many users, having an administrator set up complex elements is an adequate solution. It is roughly equivalent to having a car with a chauffeur: you can go anyplace you want, even though somebody else is driving. The problem is that most IT departments are less responsive than your typical limo driver–a more accurate analogy might be catching a New York City taxi: takes a while to get service, whoever shows up may not speak your language, and they won’t necessarily know how to reach your destination. Plus the meter is always running.

Users who would rather drive themselves do have some alternatives.

Esperant (Speedware, 800-447-2880; www.speedware.com) still requires an administrator to set up field names, complex functions, and relationships among tables. But the system is equipped with fill-in-the-blank templates to build specific classes of queries that would otherwise be too complicated for a standard SQL generator. These include queries that compare two independent sets of data, such as this year vs. last year, which involves a famously difficult task known as a correlated subquery. Because of the templates, Esperant lets users do considerably more without administrator assistance than most end-user query tools.

Esperant also prevents users from generating queries that return improper results because they involve many-to-many relationships among data tables. This is a subtle but common problem in SQL. A “merge wizard” helps users execute such projects correctly, which means creating two separate queries and then merging the results. However, Esperant does not actually tell the user when this wizard should be used. Large merges could be a problem, since the merge is done on the user’s desktop machine, which might run out of space. But the system can also run large merges on a more powerful batch server instead.

English Wizard (Linguistic Technology Corporation, 800-425-8200; www.englishwizard.com) uses the ultimate query language: plain old English. Hooked up to speech recognition software, the system literally allows users to ask questions and get back the answers. Like other managed query environments, English Wizard relies on an administrator to set up a dictionary containing names for database elements and relationships among tables. But the system already understands English syntax and contextual interpretation, so it can intelligently respond to a question like “show me their salaries” after the user has selected a group of employees. It also understands that terms like “compare” may involve a correlated subquery and will handle these without special instructions. It cannot resolve many-to-many joins, however. Users can add their own synonyms to the dictionary and administrators can define special terms that require complex processing. Although there is still considerable dependence on the technical staff, English Wizard comes tantalizingly close to letting lightly-trained users handle their own query needs. Perhaps it could help my friend spend more time with his family.

David M. Raab is a Principal at Raab Associates Inc., a consultancy specializing in marketing technology and analytics. He can be reached at draab@raabassociates.com.