What are examples of routine decision support queries?
by Dan Power
In the mid-1980s, the introduction of powerful relational database management systems (RDBMS) like Oracle and IBM DB2 encouraged managers to expect they could get answers directly from the company database. The idea was that managers would write their own English-like questions using structured query language (SQL). That hope was not fulfilled. Writing even simple queries can be challenging and a poorly constructed query can slow other computer processing. Let's examine SQL, pronounced see-quel or "S" "Q" "L", and some of the routine decision support questions a manager might ask using an RDBMS.
SQL is a non-procedural, declarative language where the person who writes a query specifies what they want, but a detailed procedure of how to obtain the result is not needed. That sounds easy, but the query writer must understand the keywords and syntax of SQL to write meaningful questions. The keywords in SQL are English words with similar meanings, but the grammar is much more structured than English.
Why is it difficult for managers to write their own queries using an RDBMS? The structure of tables in the database are closely related to the design of SQL queries. So the first problem for managers is that they must understand the tables and relationships in the database they want to use. Databases can have many tables. The good news is that tables are reasonably fixed and static and managers may only use a subset of the tables. Having unique field names in the tables can also help.
The second problem is actually writing the question as a structured query. Let's examine some routine decision support queries that managers might want to execute. Each is a SELECT query. We are selecting data from one or more tables. In some cases we must aggregate data. What are some routine decision support queries?
Who was the top salesperson last month?
When did salesperson Joe Smith join the organization?
What salespeople have birthdays next month?
Who was our largest customer last month?
How many hours have been devoted to project X in the most recent month?
How many people have worked on the project?
What is the average time spent last week by employees assigned to project X?
What did our largest customer buy?
What products had the largest gains in sales last quarter?
You can make some assumptions about the design of the tables and the field names and try to write these queries. Sadly the following queries don't work.
What were yesterday's sales?
SELECT daily sales from sales where time = yesterday
How do last week's sales compare to the prior week's sales?
SELECT weekly sales from sales where time = last week and calculate change from prior week
What was the best selling item last month?
SELECT item from sales where item is > all other items AND time = last month
What products are currently out of stock?
SELECT products from inventory where quantity = 0
So these queries won't work. Why? They violate syntax and in some cases tables need to be joined and subqueries must be used.
Is it possible to make SQL more English-like and smarter and more robust. Probably. The good thing about a standard is the increased portability and the greater transferability of a person's skill. It is a long process to change and expand language standards. Business intelligence (BI) tools have made it easier to query a relational database without understanding SQL. A graphical interface takes structured user query information and "writes" and executes the SQL query. Basically BI tools add a layer on top of SQL for helping people write queries. Can managers work directly with BI tools? Some can, but even the BI tools are usually difficult to learn and use.
Bordoloi, B. and D. Bock, Oracle SQL, Upper Saddle River, NJ: Prentice Hall, 2004.
Last update: 2009-07-22 11:04
Author: Daniel Power
You cannot comment on this entry