Esperant is Software AG's end-user query and reporting tool, and is best known for its ability to prevent users from building SQL queries that return incorrect or invalid results, even though the SQL syntax may not generate an error. Esperant has several other rare and useful query features that make it easy for end users to perform complex queries. Esperant is designed for Windows 3.1, and Software AG will release a Windows 95 version in mid-1996.
Version 3.0 is the first major upgrade of the original public version (2.1), which shipped in August 1994 (see DBMS, January 1995, page 75). Major enhancements include support for heterogeneous joins, live data formatting while designing reports, integrated charts and graphs, a batch scheduler, an "Executive Desktop" for accessing frequently used queries represented as icons, the ability to define groups and users for security purposes, and performance improvements.
Esperant comes in two versions: one for administrators and one for end users. The Administration System builds DataViews that give end users a customized view of a physical database. The Query System lets users create queries without using SQL, and then format the results into reports. The new Executive Desktop and Batch Server also appear as icons in the Esperant Program manager group.
In addition to ODBC support, Esperant provides native drivers for Oracle and Sybase or Microsoft SQL Server. It also uses Software AG's Entire Access or TechGnosis SequeLink (now owned by Intersolv) middleware to connect to Software AG's ADABAS, Oracle 6 and Oracle7, DB2, SQL Server, and Informix. The demo database uses the Watcom SQL engine.
Esperant's AutoBuild feature makes building a DataView less laborious, but administrators will still want to rename items and reorganize columns into categories that are easier for end users to grasp. You may also want to create text files containing pick-list values from which users can choose when building queries. Although you can hide tables and columns, there is no way to define a global filter in a DataView.
Esperant warns against running both the Administration and Query systems simultaneously because of possible sharing violations if both systems open the same DataView. (DataViews are stored in DOS files.) I also encountered repeated pesky warnings about low resources. The inability to run both programs easily at the same time makes testing DataViews less efficient than it should be.
Users should begin new queries by activating the Query Assistant, which is a series of dialogs that prompts for each element in a query. The Query Assistant is Esperant's heart and soul because it protects users from building invalid queries. It does this by disabling choices that do not make sense. For example, if you include two items such as customer name and customer balance to appear in the result set, the buttons to create an aggregate calculation are enabled only when you highlight the numeric item, so you cannot compute the average or total of the customer name item. It's not rocket science, but many query tools do not bother to insulate users from possible mistakes.
As users make selections in Query Assistant dialogs, Esperant echoes the query into another window using Esperant's English-like query language. When the users run the query, Esperant translates it into SQL, displays the equivalent SQL in a SQL window (you can hide this window if you like), runs the query, and displays the results. (See Figure 1.)
Thanks to the Query Assistant, the "what percent of . . .have," "compare against," and "show as percent of" queries enable end users to pose common business analysis queries that would require advanced SQL skills if they were written by hand. Esperant decomposes many of these queries into multiple SQL statements with nested SELECTs and intermediate temp tables.
The Query Assistant makes building queries very easy, but it takes time to proceed through all the dialogs that ask you to make one decision after another. You can type queries into the Esperant Query window or into the SQL window, but if you sidestep the Query Assistant, Esperant does not check the validity of the query.
Although Esperant excels at many tasks, there is still some room for minor improvements. For example, Esperant does not let you customize messages for prompted queries, and the default prompt is sometimes inadequate. A prompt for a numeric field does not reveal the operator (equal to, more than, less than, and so on) used in the query.
Esperant's query performance is quite snappy, even when it is accessing very large tables. It displays an initial batch of results without waiting for the entire result set. It also returned control promptly after I canceled a query and before all results were brought to my workstation.
The query formatter is good, but it has some limits. For example, you can create only one aggregate calculation, such as total or maximum in a group. When you need more flexibility, you can transfer the results -- including formatting -- to the reporter, which is a separate executable that you launch from within the Query System. The banded report writer is not bound by a grid structure, so you can place fields or text items anywhere, add new calculated fields, and place graphics or OLE objects on a report.
The reporter also provides a modest extension to Esperant's analytical features, but it is still weaker than programs that specialize in analytical processing. You can create crosstabs, but you cannot pivot or manipulate them interactively like you can in BrioQuery (see the following review of BrioQuery 3.5). You can place several varieties of 2D and 3D charts in group headers or footers, or in the report summary area. I was also impressed by the Reporter's ability to merge the results of two or more queries into one report. After selecting additional queries and specifying how records from separate queries should be related, you can insert fields manually from additional queries brought into the report. This is not a run-of-the-mill feature.
In addition to tabular reports and crosstabs, you can also create mailing labels using four-dozen Avery layouts, or reports that arrange fields vertically. I created two labels using the Helping Hand, Esperant's wizard, but both results were not immediately usable because the fields were all much too narrow. I was able to resize and rearrange them manually so that the labels displayed all the data, but this is one helping hand that needs help itself.
