|
FoxProFoxPro Developer's Conference '94 |
Session
215
Turning
Data Into Information Using FoxPro and Foxfire!
Alan Schwartz
Micromega Systems, Inc.
The 'Paradox' of Data vs Information
DBMS' (including FoxPro) can accumulate data at an astonishing rate. Management's appetite for information (from which business decisions are made) frequently appears insatiable. Database developers are sometimes characterized as being obsessed with whiz-bang data entry techniques, while end users and management clamor for more ways to organize all that data into meaningful information. The inclusion of true ad hoc reporting tools in FoxPro represents a significant attempt to relieve what has become a traditional bottleneck in applications development.
With FoxPro's incredible optimizer, high-performance indexing capability, and very fast raw data I/O, it can effectively manage data sets far larger than any other alternative on the PC platform -- often by orders of magnitude. This makes it a candidate for very low-cost data warehousing, and a target for decision support and other management reporting functions.
To a developer, the information contained in a group of .DBF files on disk may seem as accessible as the ON switch on your TV set. Just one click and the information starts flowing. But to the end user, the process is more obstructed. This tends to induce "tunnel vision", limiting their sights to what they are already familiar with, rather than what they need, or could benefit from if they knew it was available.
This session looks at what is needed to support ad hoc query and report generation, how FoxPro's built in facilities perform the task, and how a data driven approach can remove obstacles for novice end users to get meaningful information from databases without requiring a programmer to do it for them. It will be useful to Info Center support staff, IS Managers, developers new to FoxPro, and developers who are called upon to provide support and facilities for end-user reporting from datasets in their domain.
What steps are needed to produce an ad hoc report?
Two Roads Diverging...
FoxPro presents two alternate strategies for producing ad hoc reports: the RQBE screen, which generates a SQL SELECT command, and the View Window, a visual representation of the traditional Xbase data environment. Both represent effective ways to accomplish the first three steps required in many common ad hoc reports.
The end result of an RQBE session is a FoxPro Query (.QPR file), a procedure which extracts information from a group of records from a data file, orders them, and formats them into a meaningful display. It is always intended to READ data only, never to change it.
The View Window allows an end user to open up one or more data files, establish index orders, and set relationships through the interface, without writing any programs. A .VUE file, the saved result of a View Window, is just one part of what is needed to define an ad hoc report, however.
The biggest problem isn't what these tools do or don't do individually, its that there are TWO of them. An end user's first question should be "How do I get my job done?" In this case, however, it has to be "Which approach to take?"
Unfortunately, although both approaches will produce most common detail reports, there is no one simple answer. Although RQBE appears to take the non-technical user farther than the View Window, it's not the choice for all ad hoc queries.
Let's examine both these methods for query and report creation.
Basic RQBE navigation
 |
Common uses for RQBE:
Step up to the window and walk away with your results
Teach yourself the basics of SQL SELECT in an hour or less
Rough in SQL SELECT commands which you can cut-and-paste into programs for further modifications.
Pipelining a query into a report (or other output) :
The OPTIONS box in the main RQBE window only becomes available if the REPORT/LABELS output selection is chosen. This is the route to 'hooking' a query and a report or label set together into a complete output package through RQBE.
Selecting Report/Labels as the output option and checking the options box brings forth a mini-report dialog which allows attaching a report or label set directly to a query.
 |
Overview of the Process:
 |
Quick Report is a direct route to results, but it's limited to a one-way street.
Data Summarization Options:
Aggregating Functions
SQL Data Summarization (Aggregating Functions) available through Functions popup on field selector.
RQBE's Cross Tab Options:
There is a limited Cross Tab capability build into RQBE, which allows the production of simple cross tab reports. You must select 3 and ONLY 3 fields for output, one of which becomes the X axis, another becomes the Y axis and the third becomes the cell value. Selecting Graph as the output option (in FoxPro Windows) will give you a look at FoxPro's GRAPH WIZARD and allow you to iteratively 'tweak' your graph until it fits the data.
Developer Tips & Traps:
Aliasing fields with SQL 'AS' keyword in Field Picker Dialog.
The SQL 'AS' qualifier allows you to 'alias' fields during selection.
This feature is useful for differentiating between like named fields in separate tables, or referencing the same field repeatedly with different criteria.
Example: (Generated by RQBE):
SELECT Cars.stockno,
DEALERS.dlrname AS PURCHFROM,;
DEALERS_A.dlrname AS SOLDTO;
FROM Cars, Dealers, DEALERS Dealers_a;
WHERE Dealers.dlrid = Cars.purchid;
AND Dealers_a.dlrid = Cars.saleid;
...etc.
Problem: The RQBE can't "read" the AS clause in a QPR, so the saved query can't be re-edited. The AS clause is dropped.
Setting Default Expression Builder Preferences:
 |
Whenever the Expression Builder dialog is the current focus, the Preferences option from the Edit pulldown contains preferences specific to the expression builder. These are GLOBAL defaults for Expression Builder operation. (Creates record in Foxuser resource file)
The Importance of Optimization
When data file sizes are small (5000 records or less) and queries do not involve many multi-table joins, optimization is a convenience -- saving you a few seconds here and there. But when files grow very large (perhaps 1,000,000 records or more?) or multi-way joins are required to produce results, optimization can be the difference between results in seconds or results in hours, or perhaps no results at all. On a network, the implications are even more significant: un-optimized queries slow down not only the user waiting for results, they slow down the entire network, spewing out streams of network traffic paralyzing other users trying to accomplish unrelated tasks.
Review of FoxPro's optimization mechanism.
Indexing:
Compact and efficient, FoxPro supports more indexing, and more flexible index key creation, than any other Xbase product. Developers moving from other Xbase products are often reluctant to use as much indexing support as they wish, fearful that it will kill performance. Generally there's no problem. Exception: Bulk appending into a heavily indexed file.
For Simple Queries:
Matching Retrieval Expressions to Index Keys is required for optimization to occur:
Once FoxPro sees a retrieval expression (SET FILTER, FOR clause, or SQL WHERE clause) it attempts to match the expression to an index key. It applies no heuristics at this level -- logic doesn't apply. Its a simple character string matching process.
For Example:
Consider the CARS sample file. It has an index tag on MAKE + MODEL in this sample:
| Index Tag Expression | Selection Expression Says | Optimizable |
| MAKE + MODEL | MAKE = "FORD" | NO |
| MAKE + MODEL | MAKE + MODEL= "Ford" | YES (but no finds) |
| MAKE + MODEL | MAKE+MODEL="FordMustang" | YES (but no finds) |
| MAKE + MODEL | MAKE+MODEL="Ford Mustang" | YES |
| MAKE + MODEL | make+model="FORD" | YES (expression is standardized for spacing, capitalization and aliasing) |
| MAKE + MODEL | UPPER(MAKE+MODEL) = "FORD" | NO |
| MAKE + MODEL | CARS.MAKE + CARS.MODEL | YES (alias ignored) |
| CARS.MAKE | CARS.MAKE = "Ford" | NO (aliases in index keys defeat optimization |
Once FoxPro knows what indexes are available to speed the retrieval, it follows these steps:
For Complex Queries:
In multi-table queries, the strategy for fastest retrieval becomes much more complex. Its easy to generalize that the fastest results are achieved by subsetting the query to the smallest subset as quickly as possible. But how best to do this? The developer doesn't know the composition of the data. And its content is subject to change over time.
FoxPro's optimizer uses the simple query logic described above, referencing all relevant indexes to support rapid retrieval. In addition, it applies heuristics based on the statistics it sees about the query. In other words, it looks at all the available information, then devises a strategy to get the fastest result.
Good news:
You don't have to write procedural code to get the fastest route to most general purpose relational queries
Bad news:
There's no indication of whether optimization is in effect or not for a given command.
RQBE strengths
RQBE Weaknesses
View Window
The FoxPro View Window allows a graphical look at the Xbase data environment. This is the familiar mindset of the Xbase developer, a perspective taken from "inside" the database, rather than the SQL "request from outside". Without programming, relatively sophisticated database environments can be constructed and navigated to produce query results.
Basic VIEW Window Navigation
Much of the table oriented work in the VIEW Window takes place in the SETUP dialog:
 |
Table Creation
CREATE TABLE (SQL)
CREATE
Structure Modification (adding/removing/renaming fields)
MODIFY STRUCTURE
Creating Indices
INDEX ON __<expr>__ TAG __<name>__
Setting Index Orders
SET ORDER TO
Setting Relations (different from a SQL 'join' condition)
SET RELATION TO
Establishing 1 to many relationships
SET SKIP TO
Selecting fields for output
SET FIELDS TO
Creating Filtered Views
SET FILTER TO
Developer Tips
The View window writes commands in the FoxPro Command window interactively as actions are taken. These commands can be cut and pasted into a simple program file for use in re-creating frequently used 'data scenarios', a time saving testing and de-bugging feature.
Use the view window as a graphical teaching tool to show end-users the relationships in their data.
The .VUE file is the medium that saves the setup in the View Window.
Beware of the limitations of .VUE files. Not all FoxPro SET commands are saved. In addition, hard coded drive and path qualifiers are saved in .VUE files which potentially make them non-transportable. Worst, you can't open and edit the contents of a .VUE file -- its impossible to "see inside" them to determine what's missing, or to edit them.
Recommendation: Don't depend on .VUE files programmatically for anything more than a simple STORE environment/RESET environment.
When in doubt, use the View Window to establish the desired environment and 'cut and paste' commands into a program file as outlined above.
There is no system menu option to create a .VUE file using File New. You must use File Save As while in the VIEW Window or create a .VUE from the current environment by typing CREATE VIEW <viewname> in the Command Window.
Why have two tools that provide overlapping capabilities?
FoxPro's power was greatly enhanced with the addition of SQL capabilities. RQBE is a SQL only tool that provides a non-procedural way to take advantage of this power. FoxPro's View Window is an Xbase language tool which provides a non-procedural way to create and modify tables, indexes and relational settings, operations not possible in RQBE. Each tool has distinct uses and strengths not addressed by the other.
The biggest problem with the View window is the immediate split in the conceptual model presented to end-users: which approach to choose for the current problem. Unfortunately, the answer isn't clear unless you have a firm understanding of the strengths and weaknesses of both options. The end-user is left in a quandary about how to proceed.
| SQL/RQBE | View Window |
| Fast Column Selection for one-shot query | Field Selection issues an awkward SET FIELDS command which doesn't work for multi-table queries, and must be reset so unselected fields can be accessed again. |
| Read-only results. Better separation of editable from non-modifiable user environments |
Resulting subset can be
edited -- user's choice BROWSE button produces editable view of the data |
| Automatic full inner join | Inner join requires awkward and slow filter |
| No partial outer join (parent with no child does not appear in output) | Easily and efficiently accomplishes partial outer join. |
| Easy aggregating results | Requires programming except for 1-file TOTAL command |
| Cross-tabbing capability | Not available |
| Won't run Reports with embedded aliases | Won't run multi-table reports created by RQBE: no aliases. |
Unfortunately, in neither environment is there any indication or control of whether queries are optimized or not.
FoxPro Report Writer
FoxPro's Report Writer makes creating a new report from scratch very easy, even if you've never created one before. There is a 'mini-wizard' included with the report writer called a 'QUICK REPORT', which allows you to get started in report design with any table or SQL result set.
| Feature | Good | Not so hot |
| Report Preview | Fastest feedback for hand-eye skill building. | Code generation unavailable -- if you can't achieve a result with the report builder, you're dead ended. The only alternative is to hard-code the output. |
| Fonting/Styling | Full complement of Windows features | Lot of tweaking and
dinking needed. Tools lack degree of polish. (replicating styles is time consuming, properties at-a-glance needed) |
| Include UDF's right on report | Unprecedented flexibility. No limits on what can be brought into a report. Good workaround for sibling file reporting. | If you change the data
environment, be sure you put everything back where you
found it. Can't debug UDF's in a report. Best to assure they're clean at the command line or in a program prior to inserting them in a report form. |
| Calculations | Lots of non-procedural flexibility | Too many ways to accomplish the same thing. (Calculations vs Report Memvars) |
| Report Break Options | No limit on levels. Visually intuitive. Lots of good options for control of breaks. | Differences between DOS and Windows; all features aren't directly transportable. |
The Data Driven Alternative
By examining the metadata requirements of end-user querying and reporting, it is possible to devise a FoxPro code-based alternative to the surfaces described above. Ideally, the user should have to identify what they want to see, and not have to worry about how the system retrieves the results.
Foxfire! represents an attempt to achieve this goal in 100% FoxPro source code -- a tool we need for ourselves and to deliver to our customers in nearly every custom application we write.
"Drill down" is OK to make changes, but not helpful just to find out what a query is designed to do.
Dictionary-driven column headers, data formats, and calculations appear on the report automatically
Fit-to-page options and printing controls to make the task drive output, not the other way around
I can never remember how to handle delimiters in filter value entries.
In this task, users often can't envision what they really need until they see what they can get. This is a common behavior pattern. Relatively few people can envision the abstractions of information contained within data. The trick is to make the "price" of noodling around very low. This gets users up on their feet to the point where they can become more independent in resolving their information needs.
Turning Data into Information Using FoxPro and
FoxFire!
(c)1994 Alan C. Schwartz