What's New in Version 2.1
Bug Fixes and Enhancements made in the November 1999 release.
 

What's New in Version 2.0

Wow! Where to start?

Of course, the big news is that we now support the latest data access technology from Microsoft, the OLE DB standard. That means you can now use VB ELF directly with enterprise database systems like SQL Server 7.0 and Oracle, without passing information through the Jet Engine. In other words, we've finally lived up to our promise to provide a complete natural language solution that works effectively with every database configuration, from desktop, to client-server, to Web-based data distribution.

(We've also kept and enhanced all the older functionality, so that you can continue to "attach" to ODBC databases through a Jet frontend database, if you prefer.)

There are two main differences between the older (Jet attachment) approach and the new OLE DB Provider approach. The first is that, with an OLE DB Provider, the query is passed directly to the server database for processing, and only the answer set is returned to the client application. In the Jet system, all rows from all tables related to the question were passed back to the client -- where they were filtered to provide the answer. This is the key to the efficiency of client-server systems: When you ask about "Bob in accounting", the network has to handle only information actually related to Bob -- not everyone in the Employee table.

The second main difference is that, since we no longer rely on Jet to intervene between the question and the data source, we can no longer generate a single dialect of SQL (namely Jet SQL). Instead, VB ELF must be able to generate SQL in the exact syntax that's accepted by the database in use. And there are indeed many, many differences between SQL dialects.

With Version 2.0, we think we've addressed the core issues by providing a system which can generate two versions of Jet SQL syntax (Access 97/Access 2000), as well as Microsoft SQL Server 7.0 SQL, and Oracle SQL. In addition, we've provided a "switch" (ODBC Escape Sequences) that instructs VB ELF to use the selected dialect BUT to avoid using any language-specific function (eg. date or conversion functions), substituting instead the appropriate "escape sequence" that, by ODBC conventions, can be understood by any ODBC-compliant driver.

So you can easily use VB ELF with another ODBC database, such as Sybase SQL Anywhere, by creating a .UDL file which uses the Microsoft OLE DB Provider for ODBC Drivers. In fact, you can select ANY of the dialects for use with SQL Anywhere -- since it seems to be particularly flexible in dealing with the different varieties of JOIN syntax.

A Query Builder that Works!
In Version 1.2, we still envied the Microsoft Access Query Builder, which makes it so easy to create queries by just pointing and clicking. So we decided to build a copy of this functionality into VB ELF 2.0. But a funny thing happened along the way. We realized that Microsoft's QBE couldn't represent a fraction of the queries that are generated by VB ELF; they simply have no provision for subqueries as conditions, subqueries as record sources, subqueries as SELECT clause fields, UNIONs, or dozens of other features that put the "structured" in Structured Query Language. What's worse. even simply-structured queries with complex conditions are indecipherable in Microsoft's QBE grid, because the conditions are represented as long strings of text crammed into itty-bitty boxes. What was needed, it seemed to us, was a display that showed the conditions as a tree!

So we built a Query-By-Example Builder that handles all this and more. (For example, it lets you translate between SQL dialects with a single click.) Now we think our Access friends will envy our Query Builder.

Scripting . . . and more
Version 2.0 also introduces many new features. The most important one may prove to be "scripting" capability. You can now write scripts in either VBScript or JScript and have these "mini-programs" triggered from within VB ELF. You can trigger scripts off questions (to validate users and change views based on the user and/or his question). You can intercept specific words or phrases and run scripts to change them as you like. You can intercept the SQL that's generated by VB ELF, for instance to run a security check verifying that the user is entitled to see the information generated. You can even run scripts that catch spelling errors, and use it to supply your own suggestions to our spell check window -- or just handle the spell checking yourself. (You can also intercept the feedback from our spell checker -- in case someone tries to use "misspelled" words as a way of passing proscribed terms past your Question-intercepting scripts!)

The Worksheet mode has been refined, with better formating of data and graphs, and we've added a new feature that lets you see hierarchies (such as organizational structures) at a single glance. To use this, all you do is connect two fields from the same table (for instance, [EmployeeID] and [ReportsTo]) in the Verb Mapper. Now you can click the hierarchy button to see the tree, or sum different values over the hierarchy -- for instance to see the total sales in each division, with the totals being credited upwards from employee to boss. Another use would be to see the next, or previous, occurrence of some date -- such as an anniversary or birthday. In other words, you can push a button and see what's the next birthday along every path of the organizational tree. (Sorry, "birthday" feature not implemented in Oracle.)

You can also export all of the Worksheet; selected rows; selected columns; or a selected "rectangle" of data, to Microsoft Excel with a single click. You can even export data from OLE DB data sources directly into Microsoft Access tables.

The Verb Mapper has also been extended to handle a much-requested feature -- letting you automatically link up two fields so that when one is displayed, the other will be too. Just add an entry in the Verb Mapper using the special verb "imply". The two fields can come from the same, or from different tables.

Still Faster and Yet Smaller:
Just as in Version 1.2, this time around we've again added new compression features that allow VB ELF to be used with ever larger databases -- while continuing to efficiently index all the most relevent information in your database. So now VB ELF can handles serious databases, like SQL Server and Oracle, without hogging too much disk space for its interfaces.

A few things that were missing from the last version has been added, like the ability to set the Acknowledge flag on non-text fields (right-click a tablename from the Custom Analysis window to get to this option). This lets you exclude numeric information from your analyses -- most importantly perhaps, the system fields that are generated in replicated MS Access databases.

We've also changed the way crosstab queries are triggered. You no longer have to use the "compare" keyword; using "down" and/or "across" will usually be enough to get a 3D dataset -- for instance, "Give me the total quantities, showing employee name across and product down." or "Display the average discount, with the month of order date down and customer name across."

Now, just a minute -- we hear some people saying. SQL Server doesn't support pivot tables. Oracle certainly doesn't support pivot tables. For this kind of thing, don't you need these new high-falutin' OLAP-type thingies that only a few propeller-heads understand?

Actually, no. Most of the documentation concerning OLAP presents it as a solution to an efficiency problem -- in other words, they say that you need a tool beyond SQL because the queries that answer your real, everyday business questions run too slowly against production databases. So they give you tools to create a heavily preprocessed, intermediate layer -- a "cube" -- to quickly run queries involving aggregates.

Well, this is only half-right. With the power of today's machines, the bottleneck isn't really running the query, it's creating it. Because there's no PIVOT keyword in SQL Server Transact SQL, or in Oracle PL1/SQL, until now your only choice for pivot table queries has been an OLAP tool. All that changes with VB ELF 2.0. VB ELF will instantly create pure "SELECT statement" SQL which effectively creates the same result as MS Access pivot tables -- for both SQL Server and Oracle. In fact, it goes one better than Access, by providing optional column and row totals. (To drop the totals, eg. for nicely balanced graph images, use "without totals", "without row totals" or "without column totals" in your query.) How does VB ELF do this? By running an initial query against your database to extract the data items to use as column headers, then creating a mile-long SQL statement using CASE switches to sum up the appropriate data. (Oracle doesn't have CASE statements either, but don't think we let that stop us.)

So, using the famous Northwind database ported to Oracle as an example, if you ask: "Show total quantities giving the employee name across and week of order date down during 1994", VB ELF reads the names of the employees from the database and generates:

Oh yes, in the previous What's New, we said : "VB ELF does not yet implement the user-defined forms option . . . We expect to have this feature, which has proved so popular in our Access ELF product, incorporated into VB ELF for the 2.0 release." We've come through on this as well. Which means you can point to any Microsoft Access form, within any Access database, and use it as the display form for your data -- no matter what the current data source. As one example, you could upsize your database from Access to SQL Server, and continue to view data in the way you're familiar with from your legacy MS Access app. Just choose the display form in the My Reply Style section of the Responses Tab. VB ELF will read the form, extract any required fields missing from your query, and build an exact copy of the form to present your data, on-the-fly.

What's New in Version 1.2

Version 1.2 has a new look and feel, and incorporates hundreds of changes to the rule-base, both for better comprehension and for a wider range of response types. We've even got a brand new answer format, that lets you see how a multi-part SQL statement is built-up, one statement at a time, to the query that's finally returned. (Plus split screen graphs, automatic duplicate elimination, and many more options.) We're so excited about this new release, we're getting ahead of ourselves. Let's take it one section at a time.

Standard self-installing setup:
We've moved to the installation method that's by now familiar to everyone. All required components are automatically registered on your machine in this easy, one-step process. Uninstalling is just as easy too, using the Windows Control Panel Add/Remove Programs option.

Faster and Smaller:
With the new Synchronization option (see Settings, Advanced tab) your interface will stay up-to-date, including with any new information that's been posted to the database since the last VB ELF Analysis. (This can be many times faster than running the Analysis from scratch.) Oh yes, the interfaces themselves now requires less than half the disk space of previous versions!

Ease of use features on the Custom Analysis window:
Select All/Select None buttons to help you quickly choose the Data Sets you need; the Save button has VB ELF remember your selection, and the new Defaults button lets you return to the original system selections if your current choices haven't worked out. On the Field Selection window (reached by right-clicking any table) we've added an "Acknowledge" checkbox that lets you instruct VB ELF to ignore certain fields completely.

Toolbar:
Of course, everything is now reachable by the new VB ELF toolbar. This toolbar is not resizeable, like the Access ELF version, but you can position it anywhere on the screen, or switch between horizontal and vertical by clicking on any of the four corner quadrant buttons.

Selectable Settings:
We've also centralized all the option settings on the new "Settings" window. This includes new features like the choice between Local Help and Web-based Help. (If you're reading this in your browser, I guess you found this!) The Responses tab also shows the new Worksheet selection. Note that the version 1.2 release of VB ELF does not yet implement the user-defined forms option, so this area of the window is not currently enabled. We expect to have this feature, which has proved so popular in our Access ELF product, incorporated into VB ELF for the 2.0 release.

The new Verb Mapper tab gives you a quick way to associate action words with the relationship between tables, or even between tables and queries. For instance, if you want "require" to imply the relation between Customer and Product, you can click the Add New >* record selector, and enter a new relation record for Customers and Products. Type REQUIRE into the verb box, and you're done. Now VB ELF will understand that "Who requires seafood?" means "Which customers buy seafood?"

We've also added many new Customization features. From the Advanced tab you can customize the message displayed when VB ELF can't process your query, and a default header for custom responses to off-topic or improper queries. You can define these responses themselves in the Phrase editor, by starting the definition with an exclamation point. See the Customizing topic for more on this.

Slimmer, trimmer History form:
The Query History form is now more compact and easy to use. Just double-click any panel and an old query will be rerun.

Easier lookups:
Browse panel in the Lex Lookup form makes it easy to find all the words that have a given attribute. When the new AutoBrowse feature is on, all the words that are synonyms of the selected word are displayed. Double-click on any word in the (resizable) browse list and instantly see its attributes as well.

New phrase definition capabilities:
Data-aware substitutions, data-sensitive triggers, plus techniques for "passing along" numeric values from the input to output are some of the new features. Don't worry if these sound mysterious, we've got plenty of examples in this help file and in the Tutorials that show how these features can really put a polish on your interface. (Almost forgot -- custom error messages too!)

Worksheet mode:
A tip of the hat to Boris Gendelev; in his critique of ELF for Database Programming & Design, he suggested that a step-by-step view of the execution plan might be a confidence-booster. So we built it. We'll let Boris give his opinion:

The worksheet showing the stages of the query is indeed tremendously useful. My article might have helped to identify a problem and have thrown some ideas around, but you developed a coherent original solution.

New Graph Options:
Click Zoom on the Pinnacle Graph form to zoom in on the area between the lowest and highest values. Especially useful with high-low-close stock charts, which is also a new feature.

New Graph Engine:
Version 1.1 supported the Pinnacle-BPS Graphics Server component only; with version 1.2 we've added support for Microsoft Graph 5.0. This option is currently only available from the Worksheet, and there you can switch back and forth between the two graphics engines to find the one that's best suited to your data. This version of Microsoft Graph is more advanced then the earlier version of the control used by Access ELF, and for small amounts of data it does a nice job, especially if you need a rotatable 3D view!