SQL Server Magazine

Using English-language sentences to interact with computers isn't a new idea. About 20 years ago, I used an English-language recognition program called Eliza, a small BASIC program that posed as a psychoanalyst. Eliza kept up a dialog and made people sort out their problems by expressing their feelings. You typed in an English statement, and Eliza reformulated it as a question. Eliza dissected common sentence structures, identified verbs and nouns, and turned statements into questions. When Eliza couldn't reformulate a sentence as a question, it responded with a general question, such as "And how do you feel about that?" Although Eliza fooled many people into thinking that they were communicating with a computer, it didn't understand the English semantics; it understood only simple syntactical rules.

English recognition has come a long way since Eliza. You can use English-language recognition with the SQL Server English Query component to generate a Multidimensional Expression (MDX) query. Also, with a Visual Basic (VB) 6.0 application, you can integrate English-language recognition into OLAP applications. (See Web Dev, "The Amazing English Query Tool," April 1999, for more about English Query.)

To follow this article's demonstration, you can install the SQL Server English Query component (a beta version) from the Microsoft Developer Network (MSDN) CDs, February 2000 edition. If you have an MSDN subscription, you can also download the SQL Server English Query component from the MSDN Web site http://msdn.microsoft.com/subscriptions/ resources/subdwnld.asp. You need to use a beta version because the English Query version included with SQL Server 7.0 can't generate MDX. If you don't have a subscription to MSDN, you'll need SQL Server 2000, which is a beta version at the time of this publication and will probably be available this summer. You also need SQL Server 7.0 or SQL Server 2000 OLAP Services running on your system.

The combination of English Query and OLAP is almost a marriage made in heaven. OLAP's promise is to easily and effectively bring business information to decision-makers. OLAP's limitation is that business decision-makers who need OLAP often don't have the experience or inclination to use complicated query tools to perform functions such as filtering, sorting, and time series functions. Imagine your chief financial officer (CFO) saying to a computer, "Give me the year-to-date sales for all products sold in Europe broken down by month," and the computer responding by displaying the correct grid of numbers. In a few more years, this feat will be possible, and with some limitations, it's possible today.

Why Use OLAP and English Query
When you apply English Query and other technologies, such as data mining, multidimensional (OLAP) databases have an advantage over relational databases. You use business rules and terminology to structure multidimensional databases; by looking at an OLAP cube's structure, you can discover how a business organizes its entities. In fact, the OLAP cube's meta data contains business terminology. For example, you can see a fiscal calendar's structure, how product families break down into products, and how sales regions are organized. Such information is difficult or impossible to determine by analyzing an equivalent OLTP database. A good relational data model can represent the structure of business entities; it's the structure of business entities that isn't inherent in a relational model.

English Query uses the OLAP cube structure to create an appropriate vocabulary of entities and relationships. English Query requires entities and relationships to make sense of the English questions you ask it. To make an English Query vocabulary model, let's use the standard FoodMart Sales cube that comes with SQL Server 7.0. If you're using SQL Server 2000, the FoodMart 2000 Sales cube will also work for this demonstration. First, run the Microsoft English Query program in the SQL English Query folder in the Start menu. When the English Query development environment starts, it will prompt you for a new project, as Screen 1 shows. Type in Sales for the project name, pick a new or an empty folder on your hard disk, and type its path name for the location. Select the folder on the left that's labeled English Query Projects and the icon on the right that's labeled OLAP Project Wizard. When you click Open, SQL Server prompts you for an OLAP Server and database. Here you type in the name of the machine that is running OLAP Services or LOCALHOST if you're running OLAP Services on your local PC. Select FoodMart or FoodMart 2000 database.

The next window prompts you to choose the OLAP cubes to include in your English Query project. Select Sales, and click the right arrow to move Sales into the list box on the right, as Screen 2 shows. When you click OK in this window, English Query will read in information about the Sales cube and present the entities and relationships it discovers. The entities appear in a tree view that you can expand to view the relationships English Query discovered. As you can see in Screen 3, many of the relationships aren't checked. The project wizard relies on you to check which relationships make sense. For this example, I suggest you check all the relationships for all entities, then click OK to enter the English Query design mode.

To view a portion of the model, you can drag an entity, such as customer, to the main window, right-click it, and select Explode to view an entity-relationship of the customer entity, as Screen 4 shows.

English Query does an excellent job of determining basic entities and relationships in a cube. However, it can't determine how two dimensions are related through a measure. For example, nothing in the FoodMart cube's meta data says that customers buy products, so you need to add this relationship to the English Query model. To do so, first close the Customer entity by right-clicking on it in the main window and selecting Implode. Then drag the Product to the main window. You'll see three boxes in the main window: Customer, Time, and Product. One relationship, Customers have products, should appear between the boxes. To add the new relationship, drag Customer to a position on top of Product, which brings up a New Relationship window. Next click Add, which is to the right of the empty Phrasings list. In the Select Phrasing window, select Verb Phrasing, and click OK. In the next Verb Phrasing window, drop down the Sentence type combo box and select Subject Verb Object. Then fill in the Subject with customers, the Verb with buy, and the Direct Object with products, as Screen 5 shows. Click OK. With the addition of this sentence type, English Query will now recognize other forms of the verb buy and several synonyms such as acquire, accept, and approve of. English query knows these synonyms because it includes a dictionary of thousands of English words with their synonym relationships. You can also modify the dictionary in the English Query development environment.

Compiling and Using the Model
Let's try the model. In the model's design mode, click Start (green triangle) on the toolbar; the SQL Server English Query component will compile the model, then let you type in English queries. English Query will analyze and restate each query as it understands it. With the restated query, English Query displays the resulting MDX query. For example, type in the following statement, and press Submit:

List the ten products with the most unit sales.

English Query restates this English sentence as Show the 10 products with the highest total unit sales and responds with the MDX query:

SELECT {Measures.[Unit Sales]} ON
Columns,topcount([Product]
.[ProductName].members, 10,
Measures.[Unit Sales]) ON Rows
FROM [Sales]

To make sure the MDX query accurately states what you're asking for, click on the toolbar icon that looks like a grid. You'll then see what you suspected all along; Special Wheat Puffs is the top-selling product. This result leads to a follow-up question. Type in:

What customers bought those products?

   Prev. page   [1] 2     next page