Analyze

Before you can use the English Language Frontend system with a given database, you'll need to let ELF scan through it, so that the program can understand both the data and the relationships you've set up. We call this process the Analysis, or the Analyze function.

In some cases, the process is as easy as clicking the Express Analysis button, and having ELF automatically read all the information it needs out of your MDB file (or attached ODBC data sources). In many cases, probably in most cases, it's not this simple. That's why it's important to understand what choices you have during, and before, the Analysis process. This will help you to get the most out of our software.

There are basically two kinds of information that you can provide to the Analyzer. One is what we could call "configuration" information; for instance, where files should be stored, or how much disk space should be reserved for the dictionary. Most of the time, the system defaults are perfectly fine, so we'll spend little time on these features here, while we're getting to know the system. Later we'll go into more detail about these.
Just to outline the configuration features, they include:
a) the VIEW NAME -- what the interface will be called
b) the SAVE TO directory -- where the interface will be stored
c) the DICTIONARY SIZE -- how much space will be reserved for data, and
d) the SAVE ANALYSIS TO USER'S MDB check-off

We include the SAVE ANALYSIS feature under the configuration heading since checking the box won't affect how ELF responds to queries, at least not at first. What it will do, though, is to preserve a recording of all the choices that VB ELF makes during the Analysis process. Later, using this table, you can snoop into the decisions that underlie VB ELF's performance; or you can make changes directly to the table and change the way it "thinks".

More important are the decisions you can make, right at the starting gate, to help VB ELF decide where to concentrate, what to evaluate, and what to ignore. You have that great asset, common sense, which no computer program can claim -- and this comes in very handy.

The first decision you'll make is whether to include queries along with the tables that VB ELF will look at. One the one hand, including queries in a View is probably the most powerful and under-used feature of our software. On the other hand, we recommend that you don't use this feature until you're fairly comfortable with the basics of building an interface. The best interfaces come about, not by checking off every table and query you think may be relevant to a future question, but by identifying the core set of information you'll need. Once you get a solid interface that meets your basic informational needs, you can move on to queries and other special features.

It's also important to understand that you're not limited to a single View for any one database. You can build any number of Views, and you can easily get to them by clicking on them from the View Tab, or by attaching a line or two of code to a form. (For instance, if you're using the VB ELF OCX, it's as simple as resetting the vbelfGrammarDir property, and calling the vbelfOpenGrammar() method.) You wouldn't try to put every calculation for your business into a single spreadsheet, and similarly you can divide up a database interface into logical parts. A particular View can help you get the answers to questions about one aspect of the database, for instance Customer Relations, while another one handles your questions about your Distributors.

Of course, our examples mostly work from the Northwind database, and for simplicity we've included the whole database in a single interface. With small tables, well-structured and consistently-named database objects, this is certainly feasible. But although the technical limit of the software is now 70 tables and/or queries in a single View, it's hard to imagine a situation where this would produce a useful query interface.

Let's talk about why you might want to exclude certain tables from an Analysis. First off, Microsoft Access databases broke with a long tradition of having each different table in a separate file; this was common during the days of dBASE. This was a brilliant innovation, but it also means that some "odd" things may be in your database container. For example, MS Access applications frequently have tables of programmatic information, such as error messages, or of users and passwords, or lists of directories and files where other related information is coming from or going to. This is the kind of information that, under normal circumstances, you won't be posing queries about. (Access itself builds this sort of table whenever you use the Switchboard Wizard.) So you should make sure that it doesn't appear in the list of selected Data Sets, where it could confuse VB ELF.

Of course, if you're working with ODBC data, you need to explicitly select each table that you want included in the interface, so you won't have the problem of choosing what to exclude.

When you de-select a table (or query) in the Custom Analysis window, you excuse VB ELF from answering any questions about it. ELF won't spend any time looking at how the fields relate to each other, and it won't store any of the text data found in the table in its own dictionary. Of course, this speeds up the Analysis process. But there may be times when you have a table which contains information you'll want to ask about, and some you don't want to ask about. If it's a significant amount of data, it may be wise to reduce processing time by selectively ignoring parts of the table. To do this, you use the right mouse-button to click on any table in the VB ELF Custom Analysis window. A list of the TEXT fields (only) in that table will appear, giving you the option of "Acknowledging" and/or "Memorizing" each one.

If you choose not to Acknowledge a field, it's similar to ignoring an entire table; VB ELF acts just as if the field isn't there, and so won't be able to answer questions about it. Now let's say you Acknowledge a field, but choose not to Memorize it. This means that VB ELF will know its type and which table it comes from, as well as many other details such as whether it participates in relationships, whether it seems to be a person's name or a place, or...well, any of the literally hundreds of things which VB ELF figures out about a field. The only thing it won't do is to save all the data entries from that particular field in its own dictionary (the fast, private dictionary we usually refer to as the "lexicon").

You may not have realized that VB ELF, by default, actually makes a copy of all the data in your database. Isn't that the job of the database?!

The reason it does this is so that it can quickly look up the source of the data, whenever you mention it specifically. To take the simplest case, if you have a customer named Smith, and ask "What is Smith's phone number?", VB ELF needs to "remember" that Smith is a value of the LASTNAME field of the CUSTOMER table. But very often, you will have tables with fields containing text data that is very unlikely to appear in a query which you, or your database users, type into an ELF interface. A typical example is a License Plate number. We can imagine that a motel might keep this information, and in fact might want to ask questions such as "How many cars have license plates from each state?" or even "What is John Smith's license number?" But unless there's a need to ask questions like "Which of our customers was LC5 N71?" -- then there's no need to keep this information in the lexicon. If it's never typed into a query, VB ELF never needs to associate it directly with a table/field combination, and so this storage space and processing time can be reclaimed.

Now that you know this, you might start thinking about it from this other angle (even with Microsoft Access databases) and ask: Which fields should I include in the Analysis, instead of which should I exclude. In fact, even if you did want to ask about a specific license plate, in the earlier example, it would still be possible even though you'd checked Memorization Off. For instance, the query Which of our customers had license plate "LC5 N71"? This should do the trick because although VB ELF will not remember that LC5 N71 is a license plate, it will remember that License Plate is a text field. Enclosing the data in quotes, and naming the License Plate field within the question, will most often be more than enough of a clue.

This distinction becomes even more important if you have significant amounts of "free-form" text data. For instance, people often write notes to themselves about database entries, or have fields which contain one or two short sentences on a given topic. While there's nothing wrong with this as a database practice, it is beyond the current ability of our language software to understand the notes and memos that people write; ELF is much stronger at handling the relations between "entities" identified by short names within well-defined categories. In fact, because of this VB ELF will automatically switch the Memorization feature OFF for any field defined as a Memo field; it knows that it's just too likely to be confused by some of the words. Of course, you can override this decision and switch the Memorization back on. If you do, you should review the use of the Lexicon Editor -- so you can help ELF along when it misunderstands a word, because of associating it with a Memo field. As an example, the [Notes] Memo entry about Northwinds employees might relate that one is "earning" a Masters Degree in English. If you frequently want to compare sales based on "earnings", you may need to delete the PNoun (ie, Data) entry for the word "earning" to block this particular association.

(To delete a word, enter it in the Lookup box, click the radio button of the type (Part-of-Speech) you want to delete, and click the DELETE button. See Lex Lookup for more details.)

So far we've considered reasons for excluding an entire table, and two different ways of excluding a given field. How about excluding a set of rows from a table? Let's say you're in charge of the Accounting department and want to create a view of the Accounting personnel, but all Employee data is kept in a single table.

This is a case where you might want to consider adding a query to the Data Sets in a VB ELF View. In fact, by substituting a query (which selects out the Accounting personnel from the Employee table) for the Employee table itself, you accomplish several things. First, you shorten the time required for the Analysis, and reduce the disk requirements. Second, you ensure that answers to your queries will never mistakenly include members of other departments, since you have pre-defined the interface to look only at Accounting personnel.

Let's review that distinction. If you include the whole Employees database, you'll wind up with lexicon entries for employees you'll never ask about; as always, the more data, the more chance there is for confusion (there could easily be a Brown in Sales, who you don't even know about, when you query about Brown, your ace accountant). What's more, you'll need to restrict each query by working "in Accounting" into the question, and sometimes, especially with complex aggregation and counting questions, the more conditions there are, the harder it is to get the results you're looking for.

In general, when you add queries into the mix of data sets, ELF doesn't add the text data from the results of the query into the lexicon. After all, the data is ultimately derived from some table in your database anyway. But -- here's the trick -- if you include a query, but exclude the table that its data comes from, then ELF will include all the data (and only the data) that is selected from the table by running the query itself.

When you mention "Brown", VB ELF will now associate this lastname with a field -- not of the Employee table -- but of the query which selects Accounting personnel. You get information only about your own department, guaranteed.

Another good reason to select the Analyze Queries checkbox would be in order to include a mathematical or date function in your interface. VB ELF beginners often try to define everything using the features of VB ELF itself, for instance using the Phrase facility to define "Age" as "Now - Birthdate". This overlooks the capabilities of SQL itself, which while not very good at natural language, is spectacularly good at handling math and date functions. What's more, it's much easier to debug a small function like the definition of "Age" when it's not combined with other questions of comprehension.

So instead of using the Phrase editor for a job like this, consider creating a query which will define "Age", then including this query in the interface. (In this example, let's assume you have Microsoft Access available to help build the query. SQL Server users have similar tools.) For instance, in the Northwind database you could open a new query, right click the grey area in the QBE grid, click Show Table and add the Employees table. Next double-click the [Employee ID], then add a second column defined as: Age: DateDiff("yyyy",[Birth Date],Now()) Let's save this query as "Personal Data" (we may want to add some other fields later).

Now let's combine this with a useful application of the Phrase facility. We'll add:
When I Type: full name     I Really Mean: first name and last name
Put these together and now VB ELF will instantly translate
Show the full name and age of the employees
into
SELECT DISTINCTROW Employees.[First Name] , Employees.[Last Name] , [Personal Data].Age , [Personal Data].[Employee ID] FROM Employees , [Personal Data] , Employees INNER JOIN [Personal Data] ON Employees.[Employee ID] = [Personal Data].[Employee ID] ;

You may notice that some queries are included in the list of selectable Data Sets, and some aren't. The rules are pretty simple. Queries that reference other queries are never included. Queries that simply select out data already contained in other tables are also kept off the list. Only queries which do math and date calculations and queries which use new column names are included in this list. So for instance, in the earlier example that culled Accounting personnel from an Employees table, it's not enough to build a query which selects [Last Name] and tests the [Department ID] against Accounting. You must also rename the field, eg. Accountant: [Last Name] (in the query grid) or [Last Name] AS "Accountant" (in the SQL).

Before you click on the OK button in the Custom Analysis window, you should make sure that all the tables you've selected for this interface have a "YES" in the Has Relation column. In order to relate information from one table to another, VB ELF needs to know which columns are the Key and Foreign Key combinations you use to make the "relational" part of your relational database system go. Now, there are several ways you can provide this information. If you're working from a Jet (Access) .mdb file, then there may already be a Relationship collection, since many Jet applications use this built-in feature of the Jet Engine. If so, select the mdb check box. If no Relations are available (perhaps your application encodes all the relationships in its program logic), then you'll need to create your own Relationship map; or, instead, choose a single query which will be used to specify all the relations in the interface.

The "Relationships From:" button on the Analysis window takes you to a Relations window that lets you define or edit Relationships. If you are working with ODBC data (excluding ODBC data accessed via OLE DB), you can still use this method of specifying relations, since you're actually only making changes to the "frontend" Jet mdb which serves as the intermediary between you and your ODBC data. In other words, you don't have to worry that any change you make to the relation design will have any effect at all on a SQL Server or Oracle database.

If you prefer not to use the Jet relationship map technique at all, you can use a second, very simple scheme to specify the relationships. This is to create a single "Master" query, which contains all the key-foreign key joins for all tables at once. Because each query needs to have at least one output field, you can select some field at random, but what's important is only the links you specify between tables. Think of this query as a "diagram" of your database relationships; in fact, with large databases you may be better off if you never run it at all, even to test it out!

Incidentally, you can combine these two technique, using both a Relationship map and a master query to spec relationships. However, this is probably not the best way to go, and may lead to confusing behavior, since everything's not available for inspection in one place. And it shouldn't be done with sets of tables that have "circular" relationships; for instance, if Table A joins to Table B which joins to Table C which relates back to Table A again -- then you'll need to specify all these relationships via EITHER one method or the other, not split the work up.

Beginning with version 2.0, you have yet a third way of telling VB ELF about relationships between tables. This is the On-The-Fly option, which allows you to design a set of relationships which are maintained within VB ELF's own database. The purpose of this new feature was to provide a way to specify relationships between tables in distinct OLE DB data sources, so as to combine them in a single query (using the Mixed OLE DB option of VB ELF). In this way you can link Access to SQL Server tables, SQL Server to Oracle, or any other combination. We've also provided a way to view, or save off, these on-the-fly relationship maps in the form of a query (or SQL Server/Oracle view), if you decide to make them more-or-less permanent.

By the way, you may have read any of several magazine articles hyping the ability of SQL Server, through its new OLE DB technology, to connect different databases in this way -- the buzzwords are UDA (or "Universal Data Access") and "heterogeneous remote" joins. Most of the articles use the same example, which must come from the bowels of Microsoft, showing how it's possible to connect a Microsoft Access database to a list of email addresses in Outlook. In this example, they show how a SQL Server 7.0 statement can JOIN from a normal table reference to a new "OpenRowset" reference, which accesses data from any OLE DB Provider. However, the folks at Microsoft seem to be learning a lesson from our competitors in the natural language area, whose motto is -- "show them one example, and they'll assume everything works." The astonishing fact is that if you try to connect THREE data sources instead of TWO, the whole thing fails because only ONE heterogeneous remote join is supported at a time. It turns out that the only way to use OpenRowset in any real, practical way is to write each query you require as a data source into SQL Server as a temporary view (with its own OpenRowset reference), and then create a top-level view or procedure which references them all -- this won't fail because the OpenRowset references are then buried in such a way that SQL Server doesn't "see" more than one at a time.

We've designed VB ELF 2.0 to do all this work automatically, so you don't have to worry about it. As far as we know, this makes VB ELF the only system which turns the promise of remote heterogeneous OLE DB data access into a practical, usable, everyday tool.

Before leaving this topic, let's review why the ability to define Views is so important. Possibly the most serious mistake to fall into when defining an ELF interface is to throw "everything but the kitchen sink" into the definition. (We may encourage this by providing the Express Analysis option which does just that.) But remember that the more database structures you include in a given interface, the higher the probability of conflicts between different uses of the same word. Now of course, if any expected query is going to require data from a number of different tables, all at the same time, then it's necessary to include them all in a given view.

Before version 2.0, you had no way of knowing whether the user would be asking questions about products, say, or customers, or shipments, at a given point in the program. You had no choice but to to include them all in one heaping mass of interface. This is no longer true. With the advent of scripting, you (or more to the point, your script!) can get access to the user's question and decide right then and there what's the topic of conversation. Since you also know who's asking the question (just consult the UserID variable) you can tailor scripts that reach a hitherto unknown level of customizability.