The Data Warehouse Development Life Cycle
Oracle Data Warehouse Design
Creating An SQL Generating Front End
After setting the constraints, the
user selects which fields will be displayed in the query results.
The next screen, shown in Figure 4.19, adds a tremendous amount of
flexibility to the front end, allowing end users to create an almost
infinite variety of custom reports. The screen determines the
objects of the SQL SELECT and ORDER BY statements.
Figure 4.18 Choosing the display values and sort order.
By interrogating the values from the
screen, the appropriate mainframe Job Control Language (JCL) and SQL
are created and shipped to the internal reader of the IBM mainframe.
Users are notified that JOB xxx HAS BEEN SUBMITTED, and they
are notified of the completion by using the NOTIFY JCL
statement. Users are then free to browse or print the results of
their SQL statement. As is true with most decision support systems,
the answer to one query often stimulates additional questions. Users
can swap screens, thereby viewing output on one screen while
creating a new query on another.
REXX routines are very simple to
generate, and an understanding of the structure of SQL allows for
very quick development of an SQL generator that can display any of
the data items in the desired form or sequence. The screens are
generated very quickly with SDF in ISPF, and, as, mentioned, the
REXX routines are very straightforward.
It is not enough to have SQL used
only as an access tool. End users demand simple, check-the-box
access to their data, and they are often unwilling to use ad hoc SQL
tools such as QBE. By creating this simple front end for SQL, the
best possible outcome for the IS project is achieved. Listing 4.2
shows the REXX code used to generate the SQL. It is in three
sections: the SELECT portion, the FROM portion, and
the WHERE clause. Note that this system has two levels of
aggregate details, and the SQL generator points to either detail or
summary tables depending on the type of end user query.