Remember the scene from Star Trek IV: The Voyage Home when Scotty was trying to persuade a manufacturer to build a container to transport a twentieth-century whale into the future? Because Scotty had no money, he proposed to show the twentieth-century manufacturer how to make "transparent aluminum" in exchange for the work. Sitting down at the CAD screen, Scotty tried addressing the computer verbally. Confused, one of the others in the room handed him a keyboard, to which Scotty responded, "How quaint."
The future might not be far away. English Query (EQ), a product that Microsoft includes with SQL Server 6.5 and later, can take your users very close to the technology Scotty expected. EQ is a development environment and runtime engine that takes a question or statement written in English and converts it to a SQL query. Using the Pubs sample database, EQ can take an input question such as "List the authors who have written books about business," evaluate the question in terms of the semantic model you've built, and return a SQL statement:
SELECT firstname, lastname FROM authors ,titleauthor, titles WHERE authors.au_id = titleauthor.au_id AND titleauthor.title_id = titles.title_id
You can then submit the query to SQL Server and display the results in a table. Imagine an application whose main form is a text box into which you simply type a request for information, such as "I'd like a list of all products manufactured by CompanyA." Many application developers will simply add a text box to the bottom of existing forms, letting the user query through the form as usual or type a question in the text box.
Also, current voice-recognition technology almost has the functionality and performance necessary for general use; some industry pundits say it's ready now. If you put text-to-speech capability into your EQ application and add a microphone, you can type or speak your question to the application. Without stretching much further, you could put that speech interface on a smart phone or handheld Personal Digital Assistant (PDA) with wireless Internet capability.
I believe that EQ, along with voice-recognition software and wireless access, will soon be an integral part of some user-friendly systems. All the components to create this kind of application seem to be maturing at the same time. Several Web sites have already implemented EQ. Microsoft's EQ Web site (http://www.microsoft.com/sql /productinfo/eqmain.htm) has white papers, code samples, examples, and a live demo, which can give you a feel for the product. Also, a case study describes how InAir, a company that sells airplane components, used EQ to provide a flexible query interface that replaced many reports and complex forms.
To get EQ 2000, you can install the SQL Server 2000 beta 2 version, which is available at http://www.microsoft.com/sql.
Basic Model Building
Put simply, using EQ has two main steps. First, you use the EQ Project Wizard to build a project. To begin a new model, run EQ, which presents the dialog box that Screen 1 shows. Type the project name and directory. The Project Wizard is already highlighted. Select Open, and the wizard helps you set up a database connection and lets you select which tables you want to include in the project.
Second, build and test the model. You can build the project from the menu or select the blue arrow on the menu bar, as Screen 2 shows. Selecting the blue arrow will build the project automatically and display the testing dialog box, which Screen 3 shows. In this dialog box, you can test EQ by asking a question and checking the results. You'll spend most of your test time working in this dialog box, asking questions that your users might ask. Selecting the grid icon on the menu bar, as in Screen 3, sends the query to your SQL Server and displays the results.
Developing the EQ model involves the back end of your EQ application only; you still need to create the front end. That topic is beyond the scope of this article, but you can get started by looking in the EQ /install directory under Samples. Microsoft's EQ Web site also has some sample code.
Entities and Relationships
The Project Wizard will discover all the entities in your database, but to understand EQ, you need to know more than just how to run the Project Wizard. EQ uses the word entity in the same way database modelers do. An entity is a person, place, or thing that you want to represent in a model. The Project Wizard makes a logical model of the database, which you can see on the SQL tab in EQ's Visual Studio (VS) development environment; this model is simply a mapping of the tables, columns, and joins from your database.
The Project Wizard also does much of the work on the semantic model, which is more comprehensive than the logical model. Although EQ understands English, it has no inherent knowledge of your business or database structures. The semantic model you build is the container for this business knowledge. An entity in EQ contains one or more English words that can refer to the tables, columns, and OLAP objects in your database.
First, you need to import the database tables into the model. Then, map the entities to the database objects. You can do this manually or let the Project Wizard do most of it. For the Northwind database, you might define a products entity that maps to the products table. And you can add synonyms, so that the words goods and stock also refer to the products entity. You might define an entity called price, which refers to the UnitPrice column in the products table, then add the synonyms charge and cost to the price entity. Add any words that people will likely use when they make queries. Repeat this process for the entire database.
If you're mapping the entities manually and you test the model now with the statement "List the products and their prices," EQ's response is "What prices do products have?" Although you've defined both entities, you haven't told EQ the relationship between them. If you use the Project Wizard, EQ can answer the question immediately because the Project Wizard automatically creates this relationship and many others. Or, you can manually define relationships between entities. For example, you can create a relationship between the price entity and the product entity to tell EQ that products have prices. This phrasing is called trait phrasing, one of six phrasings that EQ supports. (The others are name/ID, subset, adjective, verb, and command.) A phrasing is a way of expressing a relationship in English. One relationship can have many phrasings. Asking EQ for a list of products and their prices now produces the following query:
SELECT dbo.Products.ProductName AS "ProductName", dbo.Products.UnitPrice AS "UnitPrice" FROM dbo.Products
EQ will also be able to answer "Which product has the largest cost?" but not "Which product is the most expensive?" Defining "expensive" requires that you describe the relationship between product and price by adding an adjective phrasing. After you (or the Project Wizard) have defined the relationship products have prices, you need only to add another phrasing to that relationship. On the Semantic tab of the EQ VS development environment, scroll through the relationships, right-click products-have-prices, and select Add Phrasing then Adjective Phrasing.
In the resulting dialog box, which Screen 4 shows, select the adjective type Measurement. The subject is products, and prices contains the measurement. You can also associate the adjective with particular values. For example, you can associate the adjective expensive with prices more than $200 and the adjective cheap with prices less than $100. When you enter the first adjective, select the ellipsis (...) button to the right of the adjective field. You'll see many synonyms for this adjective, which you can also select. EQ uses the Microsoft Word 2000 thesaurus to provide this information. If you leave the measurement value blank, EQ will prompt each user for a definition of expensive at runtime. Leaving the value blank is a great way to customize because each person's idea of expensive will vary. Click OK, and you've added a second phrasing to the relationship between products and prices.
After you rebuild the model, EQ will be able to understand such questions and statements as "List the products that are expensive" and "What is the most expensive product?" You can ask similar questions about cheap products, and you can refer to products and prices by their synonyms.
By adding an additional phrasing, you can answer many questions. Think of the questions your users might ask, and test them against your model. The purpose is twofold: to satisfy your users' needs and to expose any unmodeled relationships. The goal is to model all your business relationships.
What's New in SQL Server 2000 EQ
In the EQ versions for SQL Server 7.0 and 6.5, you must manually define all entity and relationship definitions. The version of EQ that Microsoft will release with SQL Server 2000 greatly reduces the work required to create an EQ application. The Project Wizard automatically defines an entity for each table and defines relationships among entities. The wizard can define relationships between every table and its columns. If your database includes primary-key and foreign-key relationships that you've implemented through foreign-key constraints, the wizard can also relate tables.
An EQ 2000 application for Internet deployment might contain the pieces that Figure 1 shows. After you compile the EQ project into a model, a question can come in from the Web browser to the EQ runtime engine, which accesses information from the model. The engine returns a SQL query, an answer, or a request for clarification. If the engine returns a query, the Active Server Pages (ASP) code sends the query to SQL Server, then formats and displays the results.
SQL Server 2000's EQ is integrated into the VS development environment, so most VS programmers will feel right at home. The One-Click Web Site Deployment feature--used with Microsoft IIS 4.0 or later with Windows Script Host (WSH) installed--lets you deploy an EQ application to your Web server with one click. The feature runs setup.vbs to copy files, create directories, and set parameters. The VS environment also includes the capability to add relationships by dragging an entity onto another entity in a canvas pane. And you can drag an entity from the left pane onto the canvas to work with graphically. Besides drag-and-drop relationship creation, the canvas pane gives a graphical representation of the model. Screen 5, page 38, shows the canvas pane with the graphical representation of the products have prices relationship.
As you enhance your model by adding relationships or change the model to reflect changes in the underlying database, you introduce the possibility that these new changes might disrupt the application. As with any software, making a change in one place could negatively affect some other capability. Regression testing is the testing you do to ensure that the application works correctly after you make changes. EQ can save your test questions, so you can retest and compare the results of a current test with the answers returned from a previous test. The ability to do this regression testing will make it easier to confirm that you don't introduce bugs as you change the EQ model. SQL Server 2000 EQ includes an editor that lets you view and edit the regression test file. You can also run the regression test results through the SQL Profiler's Index Tuning Wizard.
The new SQL Project Wizard will import your database schema and generate entities and relationships. The wizard can define up to 70 percent of the entities and relationships for SQL databases. EQ 2000 also integrates with Analysis Services and includes an OLAP Project Wizard. The OLAP Project Wizard will work with both SQL and OLAP databases, defining up to 90 percent of the entities and relationships. Also, EQ can output MDX queries against the OLAP-enabled entities.
By saving your EQ models in Semantic Modeling Format (SMF), an XML-based language, you can export and import EQ models. SMF and the Authoring Object Model let you create and update EQ domains. Moreover, you can create and modify models programmatically.
EQ models that you develop against full-text indexed tables will be able to generate SQL code that takes advantage of the CONTAINS() and FREETEXT() functions. Another new feature, the graphical question builder, is an end-user query interface that exposes the model and helps to generate questions. Users can drag entities onto other entities to display all existing relationships.
More to Come
In future articles, I'll cover advanced EQ model building, Analysis Services integration, and other EQ topics. If you're already using EQ and want to share what you've learned, please send your comments to me and I'll be sure to incorporate them in upcoming articles.