Why can't most managers write their own ad hoc queries to assist in information gathering and decision making? I don't think it is because Structured Query Language (SQL) is hard to learn and understand. Rather I think it is because the decision support data store is poorly designed, overly complex in terms of table structures and because IT staff are concerned managers will execute queries that are incorrect and will waste resources.

Rather than training managers to use SQL or simplifying and making more meaningful data structures including relations and metadata, we have tried to simplify the issuing of queries using pull-down menus and "natural" language interfaces. This is an improvement over having managers contact IT staff for development of ad hoc queries, but more can and should be done to increase the independence of managers and analysts.

What should we do? Try to keep the tables simple and understandable. When possible keep tables in 3rd normal form to keep queries simple. If we anticipate that complex nested subqueries will be needed, we need to rethink the design of the tables. Overall, I question the need to write complex and convoluted SQL for decision support queries. If the data store is understandable and well-designed, non IT folks can be more independent and autonomous.

Let's look at some queries from http://philip.greenspun.com/sql/complex-queries.html:

What users have contributed extensively to our discussion forum?

SELECT user_id, count(*) AS how_many
FROM bboard
GROUP BY user_id
ORDER BY how_many desc;

The result was 7348 rows selected. So we can modify the query to select more recent queries.

SELECT user_id, count(*) AS how_many
FROM bboard
WHERE posting_time + 60 > sysdate
GROUP BY user_id
ORDER BY how_many desc;

Adding the criterion in the WHERE clause reduces the result set to 1120 rows. We still have problems because the initial question was vague. Let's write a more specific and narrowed question.

What users have posted at least 30 messages in the past 60 days, ranked in descending order of volume?

SELECT user_id, count(*) AS how_many
FROM bboard
WHERE posting_time + 60 > sysdate
GROUP BY user_id
HAVING count(*) >= 30
ORDER BY how_many desc;

USER_ID HOW_MANY
---------- ----------
34375 80
34004 79
37903 49
41074 46
42485 46
35387 30
42453 30

7 rows selected.

Can some managers and analysts get this complex query figured out? YES, I am sure that some can. Is it worth the investment to train them to reduce the load on IT staff? YES, I think so.

We can teach managers and business analysts to ask clear questions and translate them into SQL!

These examples are part of SQL for Web Nerds by Philip Greenspun.

Technorati Tags: Decision Support Queries, Business Intelligence, Decision Support .