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. 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. Click their links to view these topics in more detail.
Just to outline the configuration features, they include:
View Name -- what the interface will be called
Save To directory -- where the interface will be stored
Dictionary Size -- how much space will be reserved for data, and
Save Analysis check-off

We include the Save Analysis feature under this 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 Access ELF makes during the Analysis process. Later, using this table, you can snoop into the decisions that underlie Access 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 Access 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 Access 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 their name in the View Tab, or by attaching a line of code or a macro to a form. You wouldn't try to put every calculation for your business into a single spreadsheet, and similarly you shouldn't expect every question about your business to be handled by a single View. Instead, 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 these kinds of tables don't appear in the list of selected Data Sets, where they might confuse Access ELF.

When you de-select a table (or query) in the Custom Analysis window, you excuse Access ELF from answering any questions about it. ELF won't spend any time looking at how its fields relate to each other, and won't store any of the text data found within that table in its own dictionary. Of course, this speeds up the Analysis process. But there could be times when you have a table containing some 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 Custom Analysis window's Data Set list. A listing of the fields in that table will appear, giving you the option of "Acknowledging" (Ack) and/or "Memorizing" (Mem) each one.

If you choose not to Acknowledge a field, it's similar to ignoring an entire table; Access 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 Access 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 Access 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").

This helps explain why all fields, and not just Text fields, are listed in the Access ELF Field Selection window. Since only Text data is entered into the lexicon, only Text fields allow you the option of checking/unchecking their Memorize box. On the other hand, you might want to completely exclude a field containing just about any type of information from an interface. (For instance, it might be advisable to prevent employees from using Access ELF to browse through salary information.)

You may not have realized that Access ELF actually makes a copy of all the text data in your tables. Isn't that the job of the database system?!

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?", Access ELF needs to remember that Smith is a value of the LASTNAME field of the CUSTOMER table. But very often you will have text data that is unlikely to appear in a query. In other words, neither you nor your database users, are likely to mention examples from this database field in a natural language question. 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, Access ELF never needs to associate it directly with a table/field combination, and so this storage space (and analysis time) can be reclaimed.

Now that you know this, you might start thinking about it from the other angle, 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, and even though you'd checked Memorization Off, it would still be possible. For instance, the query Which of our customers had license plate "LC5 N71"? should do the trick. Although Access ELF will not remember that LC5 N71 is a license plate, it will know that License Plate is a text field. Enclosing the data item in quotes, and naming the License Plate field within the question, should be more-than-enough of a clue.

The Memorize switch 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's 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 we recommend that you switch the Memorization feature OFF for all Memo fields. It's just too easy to confuse Access ELF with long text passages. You can override this decision later by using the Synchronize Lexicon button to re-include the field. But if you do, you should review the use of the Lexicon Editor -- so you can help ELF along when it misunderstands a word.

(To delete a word, enter it in the Lookup box, click Lookup to double-check the Parts-Of-Speech listed, click the radio button of the POS you want to delete, and click the Delete button. See How do I "break" an association 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 an Access 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. Naturally, the more data, the more chance there is for confusion. (After all, there could 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 inserting "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 expect.

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", Access 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.

The Northwind database is an example of a case where queries are used in the opposite way; not to restrict the information provided (Accountants vs Employees) but to augment the information provided. For example, Northwind ships with a query called [Order Subtotals]. The purpose of [Order Subtotals] is to sum up the actual monetary value of each order, by adding the charges for each line item of the order, as well as calculating the value of each product's contribution to the total, based on price, quantity and discount. This kind of calculation is a "business rule" which is just as important to understanding the information in your database as the data itself. While primitive natural language systems (such as Microsoft's English Query) don't allow you to use this vital information, ELF has built-in tools for making it a seamless part of your query interface.

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 selected 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).