PLEASE NOTE: The authors based their SQL Server 2000 articles for this issue on pre-Beta 2 versions, so you might notice some differences between Beta 2 and the behavior or interfaces we describe in this issue. In particular, please note that the indexed views feature will be available only in SQL Server 2000 Enterprise Edition. However, you can install Enterprise Edition on NT 4 Server and Windows 2000 (Win2K) Server. You don't need NT 4.0 Enterprise or Win2K Advanced Server.
When Microsoft released SQL Server 7.0 about a year and a half ago, the OLAP marketplace was due for a shake-up. Existing OLAP products were expensive and difficult to use, and they didn't interoperate. Microsoft's OLAP Services, included with SQL Server 7.0, was a breakthrough in performance and ease of use. OLAP Services made cube building easy and fast, and MDX, Microsoft's OLAP query language, is extremely powerful. Now, more than 100 third parties support OLAP Services with commercial software offerings, made possible by the rapidly adopted API standards of ActiveX Data Object, Multi-Dimensional (ADO MD), and OLE DB for OLAP, which Microsoft brought to market with OLAP Services 7.0. (See the sidebar "What Are All These Programming Standards?" page 60, for descriptions of these programming interfaces.)
After SQL Server 7.0 shipped, the OLAP Services product team set aggressive feature and performance goals for the next release, and we can now see the fruits of that effort. SQL Server 2000 includes an all-new version of OLAP Services, whose new name, Analysis Services 8.0, reflects its new capabilities. Let's preview some significant features of Analysis Services. (Figure 1 shows a more comprehensive list.) In future articles, I'll dig into the details of the features so that you can maximize the benefits of Analysis Services.
Data Mining
Let's start by looking at data mining, the most significant new feature of Analysis Services. Data mining lets you sift large amounts of data to discover patterns or trends. This technology has been around for some time but has been too complex for the average business analyst or decision maker to use. Analysis Services seeks to correct this problem by integrating datamining capabilities with the OLAP engine. The Analysis Services OLAP Manager's Mining Model Wizard lets you create a data-mining model based on OLAP cube information and view the results as a new dimension, measure, or member property, or as dimension members.
Microsoft implemented two of the many data-mining algorithms in use—clustering and decision trees—in SQL Server 2000. If you're a developer, you can use these algorithms with relational data by accessing OLE DB for Data Mining (DM), a new extension to OLE DB. You can use either OLE DB for OLAP or ADO MD to run the algorithms against multidimensional data. Fortunately, you don't have to be a programmer to use the data-mining capabilities with OLAP cubes. The new Mining Model Wizard, which Screen 1 shows, is a relatively straightforward way to create a new data-mining model. After creating your model, you can use it to create a virtual cube to browse the results. Third-party client applications available after the release of SQL Server 2000 will offer functionality similar to the Mining Model Wizard's by applying the new MDX extensions that Analysis Services provides.
Analysis Services includes a data-mining tutorial with two examples of how to use the Mining Model Wizard and Mining OLAP Model Editor. The first example shows how a food store's marketing department might discover correlations between a customer's demographic information (e.g., marital status, yearly income, number of children at home) and the membership card that the customer applied for. Finding the correlation would let the marketing department customize promotions for each type of membership card. The second example demonstrates how the same marketing department could discover various classifications of customers so that the store can target weekly coupons to each classification. The first example demonstrates the use of the decision-tree algorithm; the second uses the clustering algorithm.
Dimension Features
Microsoft implemented many new features for OLAP cube dimensions in Analysis Services. One feature, which resolves a major shortcoming in OLAP Services, is support for ragged and unbalanced dimension hierarchies. An example of a ragged dimension is one that contains a dimension member whose parent isn't in the dimension level immediately above. A common example of a ragged dimension hierarchy occurs in geographic data, as Figure 2 illustrates. Washington, D.C., is a city in the United States but isn't in a state; Washington, D.C.'s parent is the United States. Any other city in the United States has a state as a parent in the geographic hierarchy.
An unbalanced dimension is one whose leaf-level members are at different dimension levels—in other words, a tree with differing numbers of subordinate nodes. An unbalanced hierarchy occurs frequently in financial analysis situations. In the income statement example that Figure 3 shows, not only is the hierarchy unbalanced, but the intermediate nodes aren't all aggregated in the same way. In SQL Server 7.0, you specify one aggregation method, such as SUM, for a measure. This limitation is serious because many financial applications require this flexibility. Analysis Services lets you create custom rollup formulas for each dimension member—for example, Gross Margin = Net Sales - Cost of Sales.
You create unbalanced dimensions with a dimension table that stores parent-child relationships of the dimension members. Screen 2 shows how to configure this type of table in the new Dimension Wizard. In this example, the dimension will contain employees and will have an organization chart structure. Each record in the employee dimension table contains a key and a reference to the parent's key. Here, the key is employee_id and the parent key is supervisor_id.
The new parent-child dimensions also support write-back. This feature is different from write-enabled cubes, which OLAP Services 7.0 supports. Write-enabled dimensions let the user alter the dimension's structure; write-enabled cubes let the user change the cube's cell values. You can alter a dimension's structure with Analysis Services by using new MDX syntax, such as MOVE MEMBER or DELETE MEMBER. When these commands change a dimension, it doesn't require reprocessing. You can use write-enabled cubes and dimensions together or separately. Also new to version 8.0 are automatic allocations of cell write-back data. This addition lets you write back to a non-leaf-level cell value of a write-enabled cube and have Analysis Services automatically allocate values in the corresponding leaf-level cells based on an equal or weighted distribution.
Another new dimension feature is support for changing dimensions. With OLAP Services, if you recategorize members in a dimension by changing non-leaf- or non-top-level members, you have to fully rebuild that dimension and the cubes containing that dimension. This task can be a repetitive and time-consuming part of an OLAP application. With Analysis Services, you can mark a dimension as Changing, then change intermediate members and publish them with an incremental update of the dimension. Reprocessing the cubes isn't necessary. SQL Server Books Online (BOL) says that marking dimensions as Changing has a performance cost, but I haven't verified how expensive it is. The performance trade-off is between cube processing time and query time.
Security Features
OLAP Services 7.0 supports cell-level security, but its OLAP Manager has no user interface (UI) for configuring security at that level. You have to configure security programmatically through the Decision Support Objects (DSO) API. (I covered some details of how to do this in "Cell-Level Security," December 1999.) Fortunately, Microsoft has mostly resolved the lack of security features in Analysis Services, which supports cell-level and dimension-level security and gives you an easy way to configure them.
Dimension-level security is very flexible. You can use the basic configuration dialog box that Screen 3 shows, or you can create custom MDX statements to define which dimension members a role can access. The basic configuration lets you subset a dimension by specifying a top and a bottom level and which members in that region users can view. Your choices also control whether new members will be accessible.
An interesting security problem arises when you hide access to some members of a dimension. Imagine that you have a geographic dimension containing Canada and the United States, which have a common parent, North America. If you use dimension security to prohibit access to the United States, what do you show for the value of North America? If you show the sum of the United States and Canada, users can easily determine the value of the United States by subtracting Canada from North America. The new OLAP Manager deals with this problem by letting you configure this behavior on a dimension-by-dimension basis. You can specify whether the totals include hidden members. You can also specify that totals include hidden members only at a certain level in the dimension and above.
Actions
Actions, another interesting new feature in Analysis Services, let a cube builder associate information with portions of the cube meta data that a user can access. For example, you can specify a URL to be available to launch when someone accesses a customer in the Customer dimension. That URL will open a Web-based report with more information about each customer. You can present this report to an OLAP application user in a right-click menu or as an icon next to each customer name when the results of an OLAP query display. Imagine a scenario in which an OLAP user runs a query to display the top 10 customers from the past few quarters and notices that one customer in the last quarter is new to the top 10. Wouldn't it be convenient for the user to be able to right-click that customer name, select Customer Details, and have the OLAP application automatically launch a Web browser with a customer detail report? Because you define actions in the OLAP cube, as Screen 4 shows, you can present them from any and all OLAP client applications and update them in only one place. Actions can do more than just launch a URL. They can also launch a command line to another enterprise application or run another MDX command that displays associated information from the cube.
Drillthrough
Have you ever browsed an OLAP cube and found data you want to explore further, but drilling down to the dimension's leaf level still doesn't answer your question? For example, you see that sales spiked last month, so you drill down to a particular salesperson, customer, and product. But what you really want to see are the transaction details that were aggregated to form the sales amount in that cube cell. Drillthrough is the feature for you. Now you can use the OLAP Manager to enable drillthrough for a cube and configure which columns of the underlying star schema are displayed to the user, as Screen 5 shows. Drillthrough is a new MDX statement that contains the coordinates of a cell in the cube and, when executed, returns a row set with transaction details.
Analysis Services has many new features that I didn't cover this month. For example, the features that were in the OLAP Add-in Kit, including the ability to archive and restore cubes and create calculated members on a virtual cube, are now built in to the OLAP Manager. Also, PivotTable Services, the client component of Analysis Services, can now communicate with the OLAP server across an HTTP connection if you configure your Microsoft Internet Information Server (IIS) with a special ASP page. Now your remote users can access your OLAP Server over the Internet. I'll have plenty to write about in the months to come, so stay tuned. (For answers to the April MDX Puzzle, see "April MDX Puzzle Solution Revealed," page 59. For the May puzzle, see "MDX Puzzle.")
