In the follow-up question, English Query restates the query exactly as you typed it and responds to this question with the following MDX query:
SELECT {} ON Columns,
extract(filter([Customers].[Name].members*top-
count([Product].[Product Name].members, 10,
Measures.[Unit Sales]), NOT isempty(Measures.[Unit
Sales])), [Customers]) ON Rows
FROM [Sales]
English Query remembers the criteria to find the top 10 products and uses them instead of the phrase these products.
The resulting MDX query isn't for the faint of heart. It starts by doing a cross join (as the shorthand notation asterisk specifies) of customer name members with the top 10 product name members based on unit sales. The query then filters out the empty elements and reverses the cross join by extracting only the customer name members.
This query's complexity underscores a danger of using the English Query component: If English Query creates the wrong MDX query, you might not recognize the problem even when you're looking at it. Questions worded in English can be ambiguous, which can result in more than one answer. But don't dismiss the technology because of this concern. English recognition is still a quick way to ask many types of complicated questions. When you've found something significant in your data, be sure to look at the restatement and closely inspect the generated MDX query.
Integrating the Model into OLAP with VB
Now that this English Query model works, you can integrate it into your OLAP application with VB. But before you exit the English Query development environment, you need to compile the model into an English Query Domain (.eqd) file. To compile the model, pull down the Build menu and select Build. If nothing happens, it's OK because you already built the model when you tested it with sample questions. Now you can exit the development environment and start VB.
To start a new VB project with English Query, you need to add a project reference to the English Query type library. (If you downloaded the code sample for this column, it loaded the project reference for you.) Drop down VB's Project Menu, and select References to add the project reference. Then find and check the box labeled Microsoft English Query Type Library 2.0. Click OK. Then you're ready to use the model in VB.
The English Query development environment Test window lets the user type in an English query. The VB sample program has similar functionality, as Screen 6 shows. Listing 1, page 68, shows the source code for the program. Click Go, and you'll see the restatement of the question and the resulting MDX query. For this sample program, change the file name and the directory to match the location of your English Query Domain file (sales.eqd).
This VB sample program doesn't execute the MDX, but adding this capability wouldn't be too difficult to do. Refer to Mastering OLAP, "Writing Applications with ADO MD" (November 1999), for information on how to use ADO MD to execute MDX statements.
In Listing 1, the first step in using the English Query programming model with this sample program is to create a Session object and initialize it with a domain file. Call the InitDomain method with a file path to the compiled domain file. A more fully featured program can build its own model, but this isn't necessary because the English Query development environment is available for building domain files. If you're developing a program that lets you enter multiple sequential English questions, you'll want to create the English Query Session object once, then reuse it for each question. For brevity in this example, create the session for each question, then set the ClarifySpellingErrors property to False. This setting tells English Query to correct spelling errors without asking you first.
Next you parse the English question and get the response object. The English Query component uses the response object to return the information associated with the question. The program handles the situation in which the English query is successfully converted into an MDX query and an error occurs.
Another response type that the sample program doesn't handle is nlResponse UserClarify. The English Query component uses this response type to ask the user questions to clarify the query. For example, if you set ClarifySpellingErrors to True and a misspelled word is present, you might get an nlResponseUserClarify response. This response object will contain several possible spellings for the user to choose from. The program can then prompt the user for the correct spelling, notify English Query, and continue parsing the English request. See the Help file included with English Query for more details about how to implement this feature.
If the response is nlResponse Com-mand, English Query parsed the English request successfully and generated a command. So, in this example, you'll find an MDX command. In some cases, English Query will generate SQL in addition to or instead of MDX. If English Query generates SQL, then you need to use the SQL query against the star schema tables that built the OLAP cube. To determine whether the query is SQL, check the value of the DBMSType property of the QueryCmd object. The DBMSType will be equal to nlDBMSSQLServer if the query is SQL and will be equal to nlDBMSMSOlap if the query is MDX. SQL can answer some English questions more efficiently than MDX. For example, MDX can answer a question such as What customers bought drink products? only with a large non-empty cross join. English Query will recognize this type of question and return both SQL and MDX, but English Query will return the SQL answer first in the list of QueryCmd Objects. Some questions English Query can answer only in SQL, in which case English Query won't generate MDX. English Query answers in SQL when your English question references a column in your star schema that doesn't exist in the OLAP cube.
Although this is a whirlwind tour of English Query, I hope it gives you a good starting point for using it in your applications. English Query is a great way to interact with OLAP Services. Even if you don't make English Query available to your end users, you might want to use it to generate MDX (an endless source of good sample MDX queries). If you have ideas or questions for future columns, please send email to olapmasters@sqlmag.com.
End of Article
Prev. page
1
[2]
next page -->
