Achieving ease-of-use without sacrificing power
Bill is End-User Computing Manager at Marvin F. Poer & Company. He can be contacted at [email protected].
Most organizations use generic query tools to give end users a means of developing simple queries to access complex databases. In this article, I present a custom query tool that makes query creation more accessible for users. The query tool I present is a Microsoft Access database (available electronically; see "Resource Center," page 5) that queries an AS400 database and returns the results in an Excel spreadsheet. However, the design is generally applicable for other systems as well.
In general, query tools:
- Let users enter information describing the query.
- Let users save and reuse queries.
- Build SQL statements based on the query description.
- Execute the SQL Query.
- Return data to users in some format.
The hard part in implementing this is in hiding database complexities and SQL syntax without losing much of the expressive power of SQL.
Most end-user query tools are designed to create almost any possible SQL statement. On the other hand, you probably don't want to give users the power to create cross-product joins of your client and address files with 10 zillion records in the query. Consequently, when evaluating query tools to make things easier for users, ask yourself:
- Does the tool have a dictionary separate from the underlying database dictionary? The tool I present here uses a separate dictionary so that users only see meaningful table names and field names that you define in a dictionary, not the cryptic table names and field names we often use in databases. Also, users see only the tables and fields you want them to see—not all the tables and fields in the database.
- Do users have to figure out how to join tables together? Most tools require that users create their own joins either by entering join expressions or by graphically connecting tables. Users have difficulty doing this correctly without assistance and often create incorrect cross-product joins when they try. With my tool, all the joins are defined in the dictionary so users only have to choose the join they want and aren't allowed to choose cross-product joins.
- Do users have to work with algebraic expressions in the selection criteria? I sometimes talk with users over the phone about creating queries, and they always have trouble figuring out which query expression to write and dealing with syntax details. My tool uses the unique approach of having users choose from tests defined in the dictionary. In this article, I use the term "test" to refer to the components of the selection criteria for a SQL query. For example, there are simple tests of one field against one value (for example, Status = 'A') and combined tests of one field against multiple values (Status in ('A', 'R')).
Selecting Tables
Users need to select the tables that contain the data fields they need, then join those tables together into a single query. In my system, users can use only tables and table joins that have been defined in the dictionary; see Table 1. Tables are joined in the sequence they are added to the query with no nesting. Both inner and outer joins are supported. Outer join means that the query should include results from the main table, even if it can't find a matching record in the joined table. I used a "Select If No Match?" flag on table joins to indicate outer joins, but you may have better terminology.
To build queries, users first add a table to start the query definition, then add available linked tables one at a time. If there is more than one valid link between the two tables, then a pop-up window asks which link the user wants to pick. Restrictions to this process include:
- Once a logical table is added to a query, you cannot add a another table link using that same table. This isn't much of a limitation since a physical database table can appear in the dictionary more than once with different logical names.
- If a one-to-many table join is added to a query, then you can only add another one-to-many join to the "many" side of the last one-to-many join. This prevents users from mistakenly creating cross-product joins.
Selecting Fields
After the tables have been added to a query, users need to select the fields to include in the query. Since users can only pick fields defined in the dictionary, there is no support for user-defined calculated fields. The dictionary can contain calculated fields, although this is limited to calculations based on fields in a single table.
The system supports sorting and grouping in queries by having users specify optional "sort sequence" and "group and totals" attributes for each field. For total queries, the system uses the groups and totals flag to put the various selection criteria into the appropriate "Having" or "Where" clause. There is also a "hide field" attribute in case a field is needed for selection, sorting, or grouping but shouldn't appear in the output.
Entering the Selection Criteria
Instead of typing in expressions like "Field= Value," users select a test from a list of available options. There is a default set of tests depending on the field type, and you can specify the tests available for specific fields. If you don't specify the value in the dictionary, users are prompted to choose a value when they build the query. For coded fields, the dictionary can include a separate test for each valid code with an associated description so that users don't have to know the codes.
The tool supports several types of tests: Equal, Not Equal, Greater Than, Greater Than or Equal, Less Than, Less Than or Equal, Starts With, Contains, Ends With, and Is Null. The system automatically combines a list of individual tests against a single field into more complex field test expressions using "And," "Or," and "In (x, y, z)" where appropriate.
To date, I haven't developed a good structured model to handle complicated "And" and "Or" clauses with nested parentheses so, by default, query tests for different fields are combined with an "And." If the default wasn't what they wanted, I numbered the clauses to let users enter expressions like: "1 and (2 or 5) and (3 or 4)." Hopefully, you can devise a better solution.
Returning Data to Users
Instead of formatting reports, my tool simply puts results into an Excel spreadsheet for users to manipulate. The system does some formatting, but doesn't attempt to create a polished presentation spreadsheet.
To help with the formatting of the spreadsheet data, each field is assigned a logical data type, such as: Text, Numeric, Upper Case, yyyyddd, Quantity, Dollar, Phone, and Zip Code. The yyyyddd data type lets the system automatically convert Julian dates into regular date fields in the spreadsheet. The dictionary includes a format table that lets you customize the default formatting rules for particular fields.
User Interface
The tool uses a simple UI based on choosing items from lists:
- A main screen listing the previously created queries with options to add, delete, or open the queries.
- A query definition screen with buttons to Save and Run the query and four tabs: General Information, Tables in Query, Fields in Query, and Selection Criteria (see Figure 1).
- Pop-up windows for adding or changing field tests.
The general pattern is to have a list of valid choices on the left and selected choices on the right with buttons to make changes to the selected choices. For example, the pop-up window for adding field tests has "available field tests" and "selected field tests" list boxes, and the buttons: Add Test, Add Not Test, Delete Test, Start Over, Change Test Value, Toggle Not, Move Test Up, Move Test Down, Save New Test, and Cancel New Test.
Implementation Details
I implemented the system in Microsoft Access with most of the processing logic contained in the following set of Visual Basic classes.
- AS400 Query, the top-level class describing a query.
- QueryTable, a particular table contained in a query.
- QueryTables, a collection of all the tables contained in a query.
- QueryField, a particular field contained in a query.
- QueryFields, a collection of all the fields in a query.
- FieldTest, a single test of one field against one value.
- FieldTests, a combined test of one field against multiple values.
- QueryTest, a single complex test in the query.
- QueryTests, a collection of all the tests in the query.
The methods in these classes can be categorized as follows:
- Methods to update the query definition.
- Methods to generate the SQL statement.
- Methods to help with user-interface tasks such as creating list boxes.
- Methods to save and load query definitions to and from the database.
- Methods to generate the spreadsheet.
- Methods to run the query and get the data.
The design and programming of most of the methods is not complicated. The logic for generating the SQL statement gets tricky, but the work is divided up by having an AS400SQL method for each class. The most complicated method to program was QueryTest.AS400SQL, which scans through all tests for a single field and turns them into a single clause to be included in the Where statement. That method has about 200 lines of Visual Basic code.
Maintaining the Dictionary
The query system also needs to include the ability to update the data dictionary. Because dictionary maintenance needs to be done by the technical staff, you don't have to worry as much about user friendliness here. The dictionary UI has the following components:
- A main Corporate Database Table form with three tabs: General Information, List of Fields, and Links to Other Tables.
- A form to display Field details with tabs for General Information and Field Tests.
- A form to display and update field tests.
- A pop-up window to define the SQL for linking between two tables.
If you have a sizable database, creating the dictionary will take a fair amount of effort and you should provide any automated support that you can. For example, my tool includes logic to read the AS400 catalog and create an initial set of Field entries for tables.
Conclusion
While database concepts can be arcane to many nontechnical people, the system I present here has successfully increased the number of users who create queries. That said, I'm still probably the biggest user of the application, using it frequently to respond to ad hoc requests from end users for AS400 data. In practice, the limitations on the queries that can be created hasn't been much of a problem, and it is easier to use this tool than the generic query tool provided by IBM for the AS400. Also, it saves times when responding to user requests since the data is automatically loaded into an Excel spreadsheet that can be sent to users.
DDJ
