Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem


Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Tham khảo tài liệu 'mastering sql server 2000- p22', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả

Chủ đề:

Nội dung Text: MASTERING SQL SERVER 2000- P22

  1. 1070 CHAPTER 28 • ANALYSIS SERVICES FIGURE 28.15 Selecting training data in the Mining Model Wizard Click Next to move to the Create Dimension and Virtual Cube panel. This panel allows you to capture the results of the data-mining process for further analysis. In this example, you’ll name the dimension Customer Analysis and the virtual cube Mined Cube. A virtual cube, you’ll recall, is a cube that contains information from other cubes—in this case, the Sales_1998 cube and the mined data. Click Next to move to the finish panel of the Mining Model Wizard. Here you must assign a name to the model. You’ll choose Customer Analysis Model as a name. Select Save and Process Now, and click Finish to create the data-mining model. After the model has been processed, you can right-click it in Analysis Manager and choose Browse to see the results of the analysis. Figure 28.16 shows a data- mining model open in the Data Mining Model Browser. The different shadings used in the model indicate how strongly the input data correlates with the factor being predicted. Analysis Services automatically arranges factors to show you the most sig- nificant ones first. In this particular model, whether the customer had better than a partial high school education is the most significant factor in predicting yearly income. You can use the Content Navigator in the upper-right-hand corner of the Browser to drill down to increasingly less important factors.
  2. OLAP FROM THE CLIENT 1071 FIGURE 28.16 Browsing a data-mining model OLAP from the Client So far, all of the OLAP we’ve looked at has been done directly at a server running Microsoft SQL Server Analysis Services. However, there are alternatives that will PA R T retrieve OLAP information from a client. In this section, we’ll show you two of these alternatives. The first is an older technique that doesn’t use Analysis Services at all: VI T-SQL includes two operators, CUBE and ROLLUP, that let you perform some OLAP analysis without any software beyond SQL Server itself. The second technique we’ll demonstrate is that of using Microsoft Excel as a way to browse data that’s stored in Analysis Services cubes. Advanced Topics
  3. 1072 CHAPTER 28 • ANALYSIS SERVICES CUBE and ROLLUP Queries For quick cube analysis of data stored on SQL Server, you can use the CUBE and ROLLUP operators in a SELECT statement: SELECT GROUP BY expression WITH CUBE SELECT GROUP BY expression WITH ROLLUP Either CUBE or ROLLUP can be used with the full spectrum of clauses in the SELECT statement that you saw in Chapter 6: FROM, WHERE, ORDER BY, and so on. The exception to this rule is that you can’t use DISTINCT with an aggregate clause and either CUBE or ROLLUP. If you include, for example, COUNT(DISTINCT CustomerID) and CUBE in the same SQL statement, SQL Server will return an error message. CUBE and ROLLUP can be used only as part of a GROUP BY clause. They add addi- tional rows to the result set beyond those normally generated by the GROUP BY clause. If you specify WITH CUBE as part of a GROUP BY clause, each possible group- ing level is summarized in all possible combinations. If you specify WITH ROLLUP in a GROUP BY clause, a hierarchical set of summary rows is introduced. Some examples will make this clear. First, consider this query (performed on data from the Northwind sample database) without CUBE or ROLLUP: SELECT OrderDate, ShipCountry, EmployeeID, COUNT(OrderID) AS TotalOrders FROM Orders GROUP BY OrderDate, ShipCountry, EmployeeID ORDER BY OrderDate, ShipCountry, EmployeeID Figure 28.17 shows the results of running this query in SQL Server Query Analyzer. Each row in the result comes directly from taking one combination of the GROUP BY fields. For example, the first row shows that there was one order on July 4th shipped to France taken by employee number 5.
  4. OLAP FROM THE CLIENT 1073 FIGURE 28.17 Simple GROUP BY query If you add the WITH CUBE operator to this query, the SQL changes only slightly: SELECT OrderDate, ShipCountry, EmployeeID, PA R T COUNT(OrderID) AS TotalOrders FROM Orders GROUP BY OrderDate, ShipCountry, EmployeeID WITH CUBE VI ORDER BY OrderDate, ShipCountry, EmployeeID Figure 28.18 shows the results of this new SELECT statement. The first row of these results shows that there are 830 total orders. The second shows that 123 were taken Advanced Topics by employee number 1. Row 11 in the result set shows that 16 orders were shipped to Argentina. Note that the NULL values can appear in any column of a WITH CUBE query. WITH CUBE summarizes the results along all possible axes.
  5. 1074 CHAPTER 28 • ANALYSIS SERVICES FIGURE 28.18 GROUP BY WITH CUBE On the other hand, you can also add the WITH ROLLUP operator to the original query: SELECT OrderDate, ShipCountry, EmployeeID, COUNT(OrderID) AS TotalOrders FROM Orders GROUP BY OrderDate, ShipCountry, EmployeeID WITH ROLLUP ORDER BY OrderDate, ShipCountry, EmployeeID The results of this final query are shown in Figure 28.19. In this query, the loca- tions of the NULL values are constrained to be at the end of the hierarchy of ROLLUP columns. For example, you can have a NULL in EmployeeID alone, or in ShipCountry and EmployeeID, but not in ShipCountry alone. A WITH ROLLUP query is most use- ful for providing the information necessary for a report with subtotals, rather than the multidimensional analysis of a WITH CUBE query or a true cube produced with Analysis Services.
  6. OLAP FROM THE CLIENT 1075 FIGURE 28.19 GROUP BY WITH ROLLUP Using Excel to Retrieve Data from Analysis Services PA R T You can also use the full power of Analysis Services from client applications. This is made possible by the Microsoft PivotTable Service, a client-side implementation of VI Analysis Services. The PivotTable Service is included with Microsoft Excel 2000. New in SQL Server 2000 Analysis Services is the ability to connect the PivotTable Service to an analysis server using HTTP as the protocol—that is, to use the Internet to retrieve data from an analysis server. In this section, we’ll show you how to set this up Advanced Topics and display information on a client across the Internet. First, you need to set up some prerequisites: 1. Internet Information Server must be running on the same computer as the analysis server. 2. You need to copy the msolap.asp file, installed by Analysis Services in the Program Files\OLAP Services\bin directory, to the Inetpub\wwwroot directory so that the file is accessible to Internet Information Services.
  7. 1076 CHAPTER 28 • ANALYSIS SERVICES 3. You must install the SQL Server 2000 Client Tools on the computer where you will design and display the Excel worksheet. Then, to display data from a remote analysis server on an Excel 2000 worksheet via HTTP, follow these steps: 1. Launch Microsoft Excel 2000 with a new, blank worksheet. 2. Select Data ➣ PivotTable and PivotChart Report. This will launch the PivotTable and PivotChart Report Wizard. 3. Select External Data Source as the source of the data. Click Next. 4. Click Get Data to open the Choose Data Source dialog box. 5. Select the OLAP Cubes tab of the Choose Data Source dialog box. 6. Select and click OK. 7. Assign a name to your new data source. 8. Select Microsoft OLE DB Provider for OLAP Services 8.0 as the OLE DB provider to use. Be sure to select the provider with the 8.0 version number. 9. Click Connect to open the Multidimensional Connection dialog box, shown in Figure 28.20. Enter the Web address of the computer that’s running Analysis Ser- vices. You can use either the http://servername form or the http://IP Address form of the server address. You don’t need to enter authentication information if the Internet Information Server accepts anonymous connections. FIGURE 28.20 Multidimensional Connection dialog box
  8. OLAP FROM THE CLIENT 1077 10. Click Next. Select the Analysis Services database that contains the cube with the information that you wish to display and click Finish. 11. Select the cube that you wish to display and click OK twice to return to the PivotTable and PivotChart Wizard. 12. Click Next, select a location for the PivotTable, and click Finish. The result of this process will be an Excel PivotTable that’s connected to a cube on the analysis server via the HTTP protocol. You can drag fields from the PivotTable field well (the list of fields on the PivotTable toolbar) to the worksheet to define the display of the cube, just as if the data had originated in Microsoft Excel. Figure 28.21 shows a PivotTable based on an Analysis Services cube. FIGURE 28.21 Displaying a cube in Excel PA R T VI Advanced Topics Summary In this chapter, you learned about online analytical processing (OLAP) and the tools that SQL Server 2000 provides to do this sort of analysis. The primary tool is Microsoft SQL Server 2000 Analysis Services, a full-featured OLAP analysis product.
  9. 1078 CHAPTER 28 • ANALYSIS SERVICES You learned the basic terminology of OLAP and saw how to use Analysis Services to extract aggregate information from a large amount of data. You also saw how Analysis Services performs data mining and learned how to display OLAP results in client applications. In the next chapter, we’ll introduce another product that ships as part of the SQL Server 2000 package: Microsoft English Query, which allows you to pose questions in everyday language instead of the formal language of T-SQL.
  10. CHAPTER 29 Microsoft English Query F E AT U R I N G : What Is English Query? 1080 English Query Components 1081 Creating an English Query Application 1084 Deploying an English Query Application 1098 Summary 1100
  11. O ne of the problems that end users have with SQL Server is the need to use the T-SQL language when asking for information from a database. Many hours of development effort have been invested in coming up with inter- faces to hide the details of this process from the users. SQL Server 2000 includes a tool named Microsoft English Query (completely overhauled from the tool of the same name that was shipped as part of SQL Server 7) that’s designed to make interacting with databases simpler. By creating an English Query application, you can make it possible for your end users to extract information from a database by using plain English instead of SQL queries. In this chapter, we’ll explain the basic concepts of English Query and show how you can use it to enable natural language querying for a database. What Is English Query? English Query is a tool that builds specialized applications based on a relational data- base (the database may be stored on either SQL Server or Oracle). These applications allow the user to pose questions in plain English instead of in SQL. For example, instead of submitting the query SELECT * FROM Customers WHERE State = ‘Vermont’ an English Query user would just type the question Who are the Customers in Vermont? Of course, English Query isn’t magic. English Query applications are constructed in the Model Editor, a tool that’s hosted in the familiar Visual Studio shell. This may indicate that future versions of Visual Studio will ship with English Query, although Microsoft has made no announcement to that effect yet. The Model Editor includes Wizards that do most of the work of building an application based on reasonable assumptions. It’s your job as developer to fine-tune the results. Once your English Query model is complete, you use the Model Editor to create a compiled version of the model. This compiled version can be used together with the English Query runtime files and (of course) the original database to answer the user’s questions. The compiled model can be accessed in a variety of ways, including from a dedicated application written in a language such as Visual Basic or from a set of Web pages. Later in this chapter, you’ll see how to deploy an English Query application to an IIS-based Web site.
  12. ENGLISH QUERY COMPONENTS 1081 NOTE English Query can also work with Microsoft Analysis Services to create a natural language interface to an OLAP model. We won’t cover this advanced capability in this book. For more information, refer to the English Query help file under the heading “Analy- sis Services in English Query.” English Query Components English Query consists of a number of interrelated components. These include: • The English Query model, which captures the semantic information from your database in a form that English Query can understand • The Question Builder, a control that lets you integrate English Query into other applications • The English Query runtime, a set of files that you can redistribute when you need to make use of English Query In this section, we’ll briefly describe each of these components. English Query Models There is a great deal of knowledge about the English language already built into Eng- lish Query. For example, it knows that customers buy items and that employees work for companies. However, what it doesn’t know is how these concepts connect with your database: whether there are customers, items, employees, and companies in your database and, if so, where they are stored. The job of an English Query model is to capture the structure of your database in a form that makes it useful to English Query. An English Query model consists of both database objects and semantic objects. PA R T Database objects are the familiar schema objects from your SQL Server (or Oracle) data- VI base: tables, fields, joins, datatypes, keys, and so on. Semantic objects hold information that connects these database objects with English Query’s knowledge of the language. There are three main types of semantic object: Entity: An entity is a noun represented by a database object. This might be a Advanced Topics person such as a customer, a place such as a city, a thing such as an inventory item, or an idea such as a schedule. Entities typically map directly to tables and fields.
  13. 1082 CHAPTER 29 • MICROSOFT ENGLISH QUERY Relationship: A relationship is a phrase expressing the connection between two entities. For example, customers purchase tickets would express the relationship between customer entities and ticket entities. Phrasing: A phrasing is a way of expressing a relationship in English. A single relationship might give rise to multiple phrasings. For example, customers purchase tickets and tickets are sold to customers are two phrasings for the same relationship. The more phrasings you include in your English Query model, the better that model will be at answering questions phrased in English. Question Builder The Question Builder is an ActiveX control that can be used to integrate an English Query application with any ActiveX host language: Visual Basic, Visual C++, ASP pages, and so on. The Question Builder is new in the version of English Query that’s shipped with SQL Server 2000 and is designed to help users determine the types of questions that they can ask an English Query application. Figure 29.1 shows the Question Builder in action (here connected to an application based on the Northwind sample database). The leftmost pane of the Question Builder lists all of the entities and relationships in the current English Query model. The cen- ter pane is a drag-and-drop target. The user can drag entities and drop them here to see the relationships between those entities. The rightmost pane suggests typical questions that can be answered using the selected entities. FIGURE 29.1 The Question Builder ActiveX control The Question Builder can help you avoid one of the typical problems with natural language applications. It’s sometimes difficult for users of such applications to deter- mine just what “natural” language the application understands. This results in frus- tration and, ultimately, a refusal to use the application. By suggesting appropriate
  14. ENGLISH QUERY COMPONENTS 1083 terms and questions, the Question Builder can help make users more comfortable with your English Query application. The box at the bottom of each entity becomes a combo box when the user clicks it, listing possible values for that entity. If the user selects a value, the proposed ques- tions change to include that value. Figure 29.2 shows this process in action. FIGURE 29.2 Asking questions about a particular order The English Query Runtime Depending on how you deploy your English Query application, you may need to redistribute the English Query runtime files. If you’re shipping a standalone English Query application written in C++, Visual Basic, or another programming language, you need to make sure that all of the users of the application have these libraries installed: • Mseqole.dll • Mseqbase.dll • Mseqsql.dll PA R T • Mseqmsg.dll VI • Mseqconn.dll • Mseqcore.eqd English Query installs these files by default in the Program Files\Common Advanced Topics Files\System\EQ80 folder on your development computer. You can copy them to client computers from that folder. Be sure to use regsvr32 to register Mseqole.dll: Regsvr32 Mseqole.dll If you’re using a Web server for deploying your application, those libraries need to be installed only on the Web server.
  15. 1084 CHAPTER 29 • MICROSOFT ENGLISH QUERY If your application uses the Question Builder, you also need to make sure your users have the appropriate ActiveX control installed. For stand-alone applications, you can install and register Mseqgrqb.ocx to deliver this control. For Web applica- tions, you should include in the Web application. This file contains the ActiveX control and the help file, and will automatically be downloaded by the user’s browser when they load a page that uses the control. WARN ING You must make sure that every user of an English Query application has a SQL Server client access license. Creating an English Query Application In this section, we’ll walk through the process of creating a typical English Query application, using the Northwind sample database from SQL Server 2000 as the underlying database. We’ll cover five steps in this process: 1. Preparing the database for English Query 2. Creating an English Query project 3. Adding synonyms to the English Query model 4. Adding relationships to the English Query model 5. Testing the application Each of these steps is covered in more detail in the remainder of this section. Preparing Your Database for English Query Although you can use English Query to develop a natural language interface for any SQL Server or Oracle database, you’ll get the best results from the English Query Wiz- ards if you put some effort into preparing your database before running the Wizards. To get the best results from English Query, you need to make sure your database is properly normalized (refer to Chapter 4 if you need a refresher on normalization). In particular, you should check these points: • Is each entity represented as only one row in a table? • Does each column remain constant in meaning throughout each table? • Does each table represent only one entity?
  16. CREATING AN ENGLISH QUERY APPLICATION 1085 • Are individual entities represented as individual rows rather than columns? • Are individual entities represented as individual rows rather than tables? • Do all joins use equality between primary and foreign keys? • Are tables joined with primary and foreign keys? If there are problems with your database from the standpoint of English Query, there are two ways that you can proceed. First, you can renormalize your tables so that they meet the requirements of English Query. Alternatively, you can create nor- malized views and base the English Query application on the views rather than the base tables. Let’s look at each of these potential problems in a bit more detail. Each Entity a Single Row Sometimes it’s tempting to store multiple rows referring to different states of the same entity in a single table. For example, you might define a table of inventory that stores information on both quantity on hand and quantity on order, as shown in Table 29.1. In this case, the State column contains the value H for quantity on hand or O for quantity on order. TABLE 29.1: MULTIPLE ROWS FOR A SINGLE ENTITY InventoryItem State Quantity Bat H 40 Bat O 75 Ball H 5 Ball O 100 PA R T The problem with this schema is that a single item can appear on multiple rows in VI the table. English Query can’t handle this situation properly. You can fix the problem (from English Query’s point of view) by defining a pair of views: CREATE VIEW CurrentInventory Advanced Topics AS SELECT InventoryItem, Quantity FROM Inventory WHERE State = ‘H’
  17. 1086 CHAPTER 29 • MICROSOFT ENGLISH QUERY CREATE VIEW InventoryOnOrder AS SELECT InventoryItem, Quantity FROM Inventory WHERE State = ‘O’ With this redefinition, English Query can understand both CurrentInventory and InventoryOnOrder as separate entities. Each Field Constant in Meaning A similar issue to storing multiple entities in a single table is using codes within a single column to store information. For example, the Inventory table shown in Table 29.2 uses the convention that a positive quantity represents inventory on hand, while a negative quantity represents a quantity on order. TABLE 29.2: TABLE THAT USES CODING WITHIN A COLUMN TO VARY MEANING InventoryItem Quantity Bat 52 Ball –35 To make this scheme intelligible to English Query, you can once again create a pair of views to break out the two different types of information stored in the Quantity column: CREATE VIEW InventoryOnHand AS SELECT InventoryItem, Quantity FROM Inventory WHERE Quantity >= 0 CREATE VIEW InventoryOnOrder AS SELECT InventoryItem, Quantity FROM Inventory WHERE Quantity < 0
  18. CREATING AN ENGLISH QUERY APPLICATION 1087 One Entity per Table Sometimes database designers attempt to cut down on the number of tables in a data- base by lumping multiple entities into a single table. For example, consider the table of vehicles shown in Table 29.3. TABLE 29.3: MULTIPLE ENTITIES IN A SINGLE TABLE VehicleID VehicleType Wingspan IsConvertible 1 Plane 150 2 Car Yes 3 Car No The problem with this table, from the English Query point of view, is that it allows the user to ask nonsensical questions. For example, “Which cars have a wingspan of 100 feet?” is a question that this table might attempt to answer. As you can probably guess, the way to handle this situation is to use views to split the table up based on the Type field: CREATE VIEW Planes AS SELECT VehicleID, VehicleType, Wingspan FROM Vehicles WHERE VehicleType = ‘Plane’ CREATE VIEW Cars AS SELECT VehicleID, VehicleType, IsConvertible PA R T FROM Vehicles VI WHERE VehicleType = ‘Car’ Entities in Rows Rather Than Columns A common mistake in database design is to use repeating columns in a table. Advanced Topics Table 29.4 shows an example of this problem.
  19. 1088 CHAPTER 29 • MICROSOFT ENGLISH QUERY TABLE 29.4: REPEATING COLUMNS IN A TABLE OrderID ItemID1 ItemID2 ItemID3 1 2 3 4 2 2 5 7 3 4 1 2 The difficulty with this design is that there’s no way for English Query to know that all three ItemID columns contain the same information. This prevents English Query from answering such simple questions as “Show me all of the orders that include Item ID 2.” You can solve this problem by using a union query to change the multiple columns to multiple rows: CREATE VIEW OrderRows AS SELECT OrderID, ItemID1 FROM OrderItems UNION SELECT OrderID, ItemID2 FROM OrderItems UNION SELECT OrderID, ItemID3 FROM OrderItems Entities in Rows Rather Than Tables You may also run across a database that stores the same entity in multiple tables. Typi- cally this is the case when older information is saved in an archival table. For example, you might have a database that contains the two tables shown in Tables 29.5 and 29.6. TABLE 29.5: CURRENT ORDERING TABLE Item TotalQuantity Bat 256 Ball 576
  20. CREATING AN ENGLISH QUERY APPLICATION 1089 TABLE 29.6: HISTORIC ORDERING TABLE Item TotalQuantity1999 Bat 777 Ball 82 Given this design, English Query is unable to answer questions such as “How many balls were ordered in total?” Once again, the answer is to use a union query, in this case to stack the two tables into one: CREATE TABLE OrderQuantities AS SELECT Item, TotalQuantity FROM CurrentOrderQuantities UNION SELECT Item, TotalQuantity1999 FROM HistoricOrderQuantities All Joins Should Use Equality Another issue that can cause a problem for English Query is a bit more obscure. SQL Server supports the use of nonstandard joins between tables. That is, you can join two tables with operators such as < or >=, in addition to joining the tables with strict equality. English Query doesn’t know what to do with such a join, and you must use a view to translate it to a standard join using equality. This condition is rarely encoun- tered in practice, though. Joins Should Be Made Explicit PA R T Sometimes databases do not have explicit primary- and foreign-key relationships between tables. This is often the case, for example, in older databases that have been VI migrated to SQL Server 2000. If this is the case in your database, you should consider adding primary- and foreign-key information to your tables before creating your English Query project. This will allow English Query to answer questions involving multiple tables, instead of only questions involving a single table. Advanced Topics Figure 29.3 shows a database diagram for the Northwind sample database with the addition of explicit joins between tables. This is the version that we’ll use for our sample project in this chapter.
Đồng bộ tài khoản