What are examples of routine decision support queries?
by Dan Power
Editor, DSSResources.com
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.
References
Bordoloi, B. and D. Bock, Oracle SQL, Upper Saddle River, NJ: Prentice Hall, 2004.
Last update: 2009-07-22 11:04
Author: Daniel Power
Print this record
Show this as PDF file
You cannot comment on this entry
