Chapter 2 - New Features in SQL Server 2000

Microsoft® SQL Server™ 2000 extends the performance, reliability, quality, and ease-of-use of Microsoft SQL Server version 7.0. Microsoft SQL Server 2000 includes several new features that make it an excellent database platform for large-scale online transactional processing (OLTP), data warehousing, and e-commerce applications.

The OLAP Services feature available in SQL Server 7.0 is now called SQL Server 2000 Analysis Services. The term OLAP Services has been replaced with the term Analysis Services. Analysis Services also includes a new data mining component. For more information, see "Analysis Services Enhancements" in this chapter.

The Repository component available in SQL Server 7.0 is now called Microsoft SQL Server 2000 Meta Data Services. References to the component now use the term Meta Data Services. The term repository is used only in reference to the repository engine within Meta Data Services. For more information, see "Meta Data Services Enhancements" in this chapter.

This chapter contains brief overviews of the new features and provides cross-references to related chapters in this book as well as to their specific topics in SQL Server Books Online.

Relational Database Enhancements

Microsoft SQL Server 2000 introduces several server improvements and new features:

XML Support

The relational database engine can return data as Extensible Markup Language (XML) documents. Additionally, XML can also be used to insert, update, and delete values in the database. For more information, see Chapter 31, "Exposing SQL Server Data to the Web with XML," and the topics "SQL Server and XML Support" and "XML and Internet Support Overview" in SQL Server Books Online.

Federated Database Servers

SQL Server 2000 supports enhancements to distributed partitioned views that allow you to partition tables horizontally across multiple servers. This allows you to scale out one database server to a group of database servers that cooperate to provide the same performance levels as a cluster of database servers. This group, or federation, of database servers can support the data storage requirements of the largest Web sites and enterprise data processing systems. For more information, see Chapter 38, "Scaling Out on SQL Server," and "Federated SQL Server 2000 Servers" in SQL Server Books Online.

SQL Server 2000 introduces Net-Library support for Virtual Interface Architecture (VIA) system-area networks that provide high-speed connectivity between servers, such as between application servers and database servers. For more information, see "Communication Components" in SQL Server Books Online.

User-Defined Functions

Creating your own Transact-SQL functions can extend Transact-SQL programmability. A user-defined function can return either a scalar value or a table. For more information, see "SQL User-Defined Functions" in SQL Server Books Online.

Indexed Views

Indexed views can significantly improve the performance of an application in which queries frequently perform certain joins or aggregations. An indexed view allows indexes to be created on views, where the result set of the view is stored and indexed in the database. Existing applications do not need to be modified to take advantage of the performance improvements with indexed views. For more information, see "SQL Views" in SQL Server Books Online.

New Data Types

SQL Server 2000 introduces three new data types:

• bigint is an 8-byte integer type.

• sql_variant is a type that allows the storage of data values of different data types.

• table is a type that allows applications to store results temporarily for later use. It is supported for variables, and as the return type for user-defined functions.

For more information, see "Data Types and Table Structures" in SQL Server Books Online.

INSTEAD OF and AFTER Triggers

INSTEAD OF triggers are executed instead of the triggering action (for example, an INSERT, UPDATE, or DELETE statement). They can also be defined on views, in which case they greatly extend the types of updates a view can support. AFTER triggers fire after the triggering action. SQL Server 2000 introduces the ability to specify which AFTER triggers fire first and last. For more information, see Chapter 37, "Extending Triggers with INSTEAD OF," and "Triggers" in SQL Server Books Online.

Cascading Referential Integrity Constraints

You can control the actions SQL Server 2000 takes when you attempt to update or delete a key to which existing foreign keys point. To control these actions, you can use the new ON DELETE and ON UPDATE clauses in the REFERENCES clause of the CREATE TABLE and ALTER TABLE statements. For more information, see "Constraints" in SQL Server Books Online.

Collation Enhancements

SQL Server 2000 replaces code pages and sort orders with collations. SQL Server 2000 includes support for most collations supported in earlier versions of SQL Server, and introduces a new set of collations based on Microsoft Windows® collations. You can now specify collations at the database level or at the column level. In earlier versions of SQL Server, code pages and sort orders could be specified only at the server level and applied to all databases on a server. For more information, see "Collations" in SQL Server Books Online.

Collations support code page translations. Operations with char and varchar operands having different code pages are now supported. Code page translations are not supported for text operands. You can use ALTER DATABASE to change the default collation of a database. For more information, see the topics "SQL Server Collation Fundamentals" and "ALTER DATABASE" in SQL Server Books Online.

Full-Text Search Enhancements

Full-text search now includes change tracking and image filtering. Change tracking maintains a log of all changes to the full-text indexed data. You can update the full-text index with these changes, by flushing the log manually, on a schedule, or as they occur, using the background update index option. Image filtering allows you to index and query documents stored in image columns. The user provides the document type in a column containing the file name extension the document would have had if it were stored as a file in the file system. Using this information, full-text search is able to load the appropriate document filter to extract textual information for indexing. For more information, see "Microsoft Search Service" in SQL Server Books Online.

Multiple Instances of SQL Server

SQL Server 2000 supports running multiple instances of the relational database engine on the same computer. Each computer can run one instance of the relational database engine from SQL Server 6.5 or SQL Server 7.0, along with one or more instances of the database engine from SQL Server 2000. Each instance has its own set of system and user databases. Applications can connect to each instance on a computer similar to the way they connect to instances of SQL Servers running on different computers. The SQL Server 2000 utilities and administration tools have been enhanced to work with multiple instances. For more information, see "Multiple Instances of SQL Server" in SQL Server Books Online.

Index Enhancements

You can now create indexes on computed columns. You can specify whether indexes are built in ascending or descending order, and if the database engine should use parallel scanning and sorting during index creation. For more information, see the topics "Table Indexes" and "Parallel Operations Creating Indexes" in SQL Server Books Online.

The CREATE INDEX statement can now use the tempdb database as a work area for the sorts required to build an index. This results in improved disk read and write patterns for the index creation step, and makes it more likely that index pages will be allocated in contiguous strips. In addition, the complete process of creating an index is eligible for parallel operations, not only the initial table scan. For more information, see the topics "tempdb and Index Creation," "Parallel Operations Creating Indexes," and "CREATE INDEX" in SQL Server Books Online.

Failover Clustering Enhancements

The administration of failover clusters has been greatly improved to make it very easy to install, configure, and maintain a Microsoft SQL Server 2000 failover cluster. Additional enhancements include the ability to failover and failback to or from any node in a SQL Server 2000 cluster, the ability to add or remove a node from the cluster through SQL Server 2000 Setup, and the ability to reinstall or rebuild a cluster instance on any node in the cluster without affecting the other cluster node instances. The SQL Server 2000 utilities and administration tools have been enhanced to work with failover clusters. For more information, see Chapter 12, "Failover Clustering," and Chapter 15, "High Availability Options," and "Failover Clustering Architecture" in SQL Server Books Online.

Net-Library Enhancements

The SQL Server 2000 Net-Libraries have been rewritten to virtually eliminate the need to administer Net-Library configurations on client computers when connecting SQL Server 2000 clients to instances of SQL Server 2000. The new Net-Libraries also support connections to multiple instances of SQL Server on the same computer, and support Secure Sockets Layer encryption over all Net-Libraries. SQL Server 2000 introduces Net-Library support for Virtual Interface Architecture (VIA) system-area networks that provide high-speed connectivity between servers, such as between application servers and database servers. For more information, see "Communication Components" in SQL Server Books Online.

64-GB Memory Support

Microsoft SQL Server 2000 Enterprise Edition can use the Microsoft Windows 2000 Advanced Windows Extension (AWE) API to support up to 64 GB of physical memory (RAM) on a computer. For more information, see "Using AWE Memory on Windows 2000" in SQL Server Books Online.

Distributed Query Enhancements

SQL Server 2000 introduces the OPENROWSET function, which you can use to specify ad hoc connection information in a distributed query. SQL Server 2000 also specifies methods that OLE DB providers can use to report the level of SQL syntax supported by the provider and statistics on the distribution of key values in the data source. The distributed query optimizer can then use this information to reduce the amount of data that has to be sent from the OLE DB data source. SQL Server 2000 delegates more SQL operations to OLE DB data sources than earlier versions of SQL Server. Distributed queries also support the other functions introduced in SQL Server 2000, such as multiple instances, mixing columns with different collations in result sets, and the new bigint and sql_variant data types. For more information, see "Distributed Query Architecture" in SQL Server Books Online.

SQL Server 2000 distributed queries add support for the OLE DB Provider for Exchange and the Microsoft OLE DB Provider for Microsoft Directory Services. For more information, see the topics "OLE DB Provider for Microsoft Directory Services" and "OLE DB Provider for Exchange" in SQL Server Books Online.

Updatable Distributed Partitioned Views

SQL Server 2000 introduces enhancements to distributed partitioned views. You can partition tables horizontally across several servers, and define a distributed partitioned view on each member server that makes it appear as if a full copy of the original table is stored on each server. Groups of servers running SQL Server that cooperate in this type of partitioning are called federations of servers. A database federation built using SQL Server 2000 databases is capable of supporting the processing requirements of the largest Web sites or enterprise-level databases. For more information, see Chapter 38, "Scaling Out on SQL Server," and "Creating a Partitioned View" in SQL Server Books Online.

Kerberos and Security Delegation

SQL Server 2000 uses Kerberos to support mutual authentication between the client and the server, as well as the ability to pass the security credentials of a client between computers, so work on a remote server can proceed using the credentials of the impersonated client. With Microsoft Windows 2000, SQL Server 2000 uses Kerberos and delegation to support both integrated authentication as well as SQL Server logins. For more information, see Chapter 10, "Implementing Security," and "Security Account Delegation" in SQL Server Books Online.

Backup and Restore Enhancements

SQL Server 2000 introduces a new, more easily understood model for specifying backup and restore options. The new model makes it clearer that you are balancing increased or decreased exposure to losing work against the performance and log space requirements of different plans. SQL Server 2000 introduces support for recovery to specific points of work using named log marks in the transaction log, and the ability to do partial database restores. For more information, see "Backup/Restore Architecture" in SQL Server Books Online.

Users can define passwords for backup sets and media sets that prevent unauthorized users from accessing SQL Server backups. For more information, see "BACKUP" in SQL Server Books Online.

Scalability Enhancements for Utility Operations

SQL Server 2000 enhancements for utility operations include faster differential backups, parallel Database Console Command (DBCC), and parallel scanning. Differential backups can now be completed in a time that is proportional to the amount of data changed since the last full backup. DBCC can be run without taking shared table locks while scanning tables, thereby enabling them to be run concurrently with update activity on tables. Additionally, DBCC now takes advantage of multiple processors, enabling near-linear gain in performance in relation to the number of CPUs (provided that I/O is not a bottleneck). For more information, see the topics "Data Integrity Validation" and "Differential Backup and Restore" in SQL Server Books Online.

Text in Row Data

SQL Server 2000 supports a new text in row table option that specifies that small text, ntext, and image values be placed directly in the data row instead of in a separate page. This reduces the amount of space used to store small text, ntext, and image data values, and reduces the amount of disk I/O needed to process these values. For more information, see Chapter 11, "Using BLOBs," and "text, ntext, and image Data" in SQL Server Books Online.

XML Integration of Relational Data

The Microsoft SQL Server 2000 relational database engine natively supports Extensible Markup Language (XML).

You can access SQL Server 2000 over HTTP using a Universal Resource Locator (URL). You can define a virtual root on a Microsoft Internet Information Services (IIS) server, which provides HTTP access to the data and XML functionality of SQL Server 2000. For more information, see Chapter 31, "Exposing SQL Server Data to the Web with XML."

HTTP, Microsoft ActiveX® Data Objects (ADO), or OLE DB can be used to work with the XML functionality of SQL Server 2000:

• XML views of SQL Server 2000 databases can be defined by annotating XML-Data Reduced (XDR) schemas to map the tables, views, and columns that are associated with the elements and attributes of the schema. The XML views can then be referenced in XPath queries, which retrieve results from the database and return them as XML documents. 

• The results of SELECT statements can be returned as XML documents. The SQL Server 2000 Transact-SQL SELECT statement supports the FOR XML clause, which specifies that the statement results be returned in the form of an XML document instead of a relational result set. Complex queries, or queries that you want to make secure, can be stored as templates in an IIS virtual root, and executed by referencing the template name. 

• Data from an XML document can be exposed as a relational rowset using the new OPENXML rowset function. OPENXML can be used everywhere a rowset function can be used in a Transact-SQL statement, such as in place of a table or view reference in a FROM clause. This allows you to use the data in XML documents to insert, update, or delete data in the tables of the database, including modifying multiple rows in multiple tables in a single operation. 

Graphical Administration Enhancements

Microsoft SQL Server 2000 introduces the following graphical administration improvements and new features:

Log Shipping

Log shipping allows the transaction logs from a source database to be continually backed up and loaded into a target database on another server. This is useful for maintaining a warm standby server, or for offloading query processing from the source server to a read-only destination server. For more information, see Chapter 13, "Log Shipping," and "Log Shipping" in SQL Server Books Online.

SQL Profiler Enhancements

SQL Profiler supports size-based and time-based traces, and includes new events for Data File Auto Grow, Data File Auto Shrink, Log File Auto Grow, Log File Auto Shrink, Show Plan All, Show Plan Statistics, and Show Plan Text.

SQL Profiler has been enhanced to provide auditing of SQL Server activities, up to the auditing levels required by the C2 level of security defined by the U.S. government. For more information, see the topics "Auditing SQL Server Activity" and "Monitoring with SQL Profiler" in SQL Server Books Online.

SQL Query Analyzer Enhancements

SQL Query Analyzer now includes Object Browser, which allows you to navigate through and get information (such as parameters and dependencies) about database objects, including user and system tables, views, stored procedures, extended stored procedures, and functions. The Object Browser also supports generating scripts to either execute or create objects. Other enhancements include server tracing and client statistics that show information about the server-side and client-side impact of a given query.

SQL Query Analyzer includes a stored procedure debugger. SQL Query Analyzer also includes templates that can be used as the starting points for creating objects such as databases, tables, views, and stored procedures. For more information, see the topics "SQL Query Analyzer" and "Overview of SQL Query Analyzer" in SQL Server Books Online.

Copy Database Wizard

Users can run the Copy Database Wizard to upgrade SQL Server 7.0 databases to SQL Server 2000 databases. It can also be used to copy complete databases between instances of SQL Server 2000. For more information, see "Copying Databases to Other Servers" in SQL Server Books Online.

Replication Enhancements

Microsoft SQL Server 2000 introduces the following replication improvements and new features:

Implementing Replication

SQL Server 2000 enhances snapshot replication, transactional replication, and merge replication by adding:

• Alternate snapshot locations, which provide easier and more flexible methods for applying the initial snapshot to Subscribers. You can save (and compress) the snapshot files to a network location or removable media, which can then be transferred to Subscribers without using the network. 

• Attachable subscription databases, which allow you to transfer a database with replicated data and one or more subscriptions from one Subscriber to another SQL Server. After the database is attached to the new Subscriber, the subscription database at the new Subscriber will automatically receive its own pull subscriptions to the publications at the specified Publishers. 

• Schema changes on publication databases, which allow you to add or drop columns on the publishing table and propagate those changes to Subscribers. 

• On demand script execution, which allows you to post a general SQL script that will be executed at all Subscribers. 

• Pre- and post-snapshot scripts, which allow you to run scripts before or after a snapshot is applied at the Subscriber. 

• Remote agent activation, which allows you to reduce the amount of processing on the Distributor or Subscriber by running the Distribution Agent or Merge Agent on one computer while activating that agent from another computer. You can use remote agent activation with push or pull subscriptions.

• Support of new SQL Server features, which includes user-defined functions, indexed views, new data types, and multiple instances of SQL Server. 

• The ActiveX Snapshot Control, which makes programmatic generation of snapshots easier. 

• More snapshot scripting options, which support transfer of indexes, extended properties, and constraints to Subscribers.

Merge Replication

Merge replication is the process of distributing data from Publisher to Subscribers, allowing the Publisher and Subscribers to make updates while connected or disconnected, and then merging the changes between sites when they are connected. Enhancements to merge replication include:

• Greater parallelism of the Merge Agent for improved server-to-server performance. 

• Optimizations for determining data changes relevant to a partition at a Subscriber. 

• Dynamic snapshots, which provide more efficient application of the initial snapshot when using dynamic filters. 

• Vertical filters for merge publications. 

• More powerful dynamic filtering with user-defined functions. 

• The ability to use alternate synchronization partners when synchronizing data. Using alternate synchronization partners, a Subscriber to a merge publication can synchronize with any specified server that has the same data as the original Publisher. 

• Automated management of identity ranges. In merge replication topologies where a publication contains an identity column, and where new rows can be inserted at Subscribers, automated management of identity ranges at the Subscriber ensures the same identity values are not assigned to rows inserted at different subscription databases, and that primary key constraint violations do not occur. This feature is also available when queued updating is used with snapshot replication or transactional replication.

• Support for timestamp columns in published tables. 

• Improved management of merge tracking data growth. 

• Several new merge replication conflict resolvers including interactive resolvers that provide a user interface for immediate, manual conflict resolution, priority based on a column value, minimum/maximum value wins, first/last change wins, additive/average value, and merge by appending different text values. 

• New options to validate permissions for a Subscriber to upload changes to a Publisher (check_permissions) and security enhancements including code signing of conflict resolvers included with Microsoft SQL Server 2000. 

• New COM interfaces that support heterogeneous data sources as Publishers within a SQL Server replication topology. 

• Validation of replicated data per subscription or on a publication-wide basis. Validation is also available through SQL Server Enterprise Manager. 

• Reinitialization to allow uploading of changes from the Subscriber before the application of a new snapshot. 

For more information, see the topics "Merge Replication" and "Replication Options" in SQL Server Books Online.

Transactional Replication

With transactional replication, an initial snapshot of data is applied at Subscribers, and then when data modifications are made at the Publisher, the individual transactions are captured and propagated to Subscribers. Enhancements to transactional replication include:

• Concurrent snapshot processing so that data modifications can continue on publishing tables while the initial snapshot is generated. 

• Improved error handling and the ability to skip specified errors and continue replication. 

• Validation of replicated data at the Subscriber, including validation on vertical partitions. Validation is also available through SQL Server Enterprise Manager. 

• Publishing indexed views as tables. 

• The option to store data modifications made at the Subscriber in a queue (queued updating).

• The option to transform data as it is published to Subscribers (transforming published data). 

• The ability to restore transactional replication databases without reinitializing subscriptions or disabling and reconfiguring publishing and distribution. You can also set up transactional replication to work with log shipping, enabling you to fail over to a warm standby server without reconfiguring replication. For more information, see the "Strategies for Restoring Snapshot or Transactional Replication" topic in SQL Server Books Online. 

For more information, see "Transactional Replication" in SQL Server Books Online.

Queued Updating

Queued updating allows snapshot replication and transactional replication Subscribers to modify published data without requiring an active network connection to the Publisher.

When you create a publication with the queued updating option enabled and a Subscriber performs INSERT, UPDATE, or DELETE statements on published data, the changes are stored in a queue. The queued transactions are applied asynchronously at the Publisher when network connectivity is restored.

Because the updates are propagated asynchronously to the Publisher, the same data may have been updated by the Publisher or by another Subscriber and conflicts can occur when applying the updates. Conflicts are detected automatically and several options for resolving conflicts are offered.

For more information, see "Queued Updating" in SQL Server Books Online.

Transforming Published Data

Transformable subscriptions (available with snapshot replication or transactional replication) leverage the data movement, transformation mapping, and filtering capabilities of Data Transformation Services (DTS).

Using transformable subscriptions in your replication topology allows you to customize and send published data based on the requirements of individual Subscribers, including performing data type mappings, column manipulations, string manipulations, and using functions as data is published.

For more information, see "Transforming Published Data" in SQL Server Books Online.

Replication Usability

There have been several improvements in SQL Server Enterprise Manager that provide for easier implementation, monitoring, and administration of replication. Enhancements to replication usability include:

• A centralized Replication folder in the SQL Server Enterprise Manager tree, which organizes all subscriptions and publications on the server being administered. 

• The ability to browse for and subscribe to publications (when permission is allowed) using Microsoft Active Directory™ in Windows 2000. 

• The ability to see multiple Distributors in a single monitoring node in SQL Server Enterprise Manager. 

• Standard and advanced replication options separated in the Create Publication, Create Push Subscription, and Create Pull Subscription Wizards. You can choose to show advanced options in these wizards on the Welcome page of each wizard. 

• New wizards for creating jobs that create dynamic snapshots for merge publications that use dynamic filters (Create Dynamic Snapshot Job Wizard), and for transforming published data in snapshot replication or transactional replication (Transform Published Data Wizard). 

Data Transformation Services Enhancements

Microsoft SQL Server 2000 introduces the following Data Transformation Services (DTS) improvements and new features:

New Custom Tasks

The SQL Server 2000 DTS Designer and the DTS object model introduce new custom tasks that allow you to create packages that perform tasks or set variables based on properties of the run-time environment. These include:

• Importing data from and sending data and completed packages to Internet and FTP sites. 

• Running packages asynchronously. 

• Building packages that send messages to each other. 

• Building packages that execute other packages.

• Joining multiple package executions as part of a transaction.

For more information, see "Building a DTS Custom Task" in SQL Server Books Online.

Saving DTS Packages to Visual Basic Files

Saving a DTS package to a Microsoft Visual Basic® file allows a package created by the DTS Import Wizard, the DTS Export Wizard, or DTS Designer to be incorporated into Visual Basic programs or to be used as prototypes by Visual Basic developers who need to reference the components of the DTS object model. For more information, see "Saving a DTS Package" in SQL Server Books Online.

Multiphase Data Pump

DTS includes a new multiphase data pump that allows advanced users to customize the operation of the data pump at various stages of its operation. Advanced users can monitor the activity of the Multiphase Data Pump from outside of a DTS package using the techniques described in Chapter 21, "Monitoring the DTS Multiphase Data Pump in Visual Basic." Global variables can now be used as input and output parameters for queries. DTS also introduces new package-logging capabilities. For more information, see the topics "Multiphase Data Pump Functionality," "Using Parameterized Queries in DTS," and "Using DTS Package Logs" in SQL Server Books Online.

Analysis Services Enhancements

Microsoft SQL Server 2000 extends and renames the former OLAP Services component, now called Analysis Services. Many new and improved features significantly enhance the analysis capabilities of OLAP Services introduced in SQL Server 7.0. In this release, Analysis Services introduces data mining, which can be used to discover information in OLAP cubes and relational databases.

Feature	Description
Cube enhancements	New cube types and enhanced cube functionality substantially extend the scalability and functionality of Analysis Services.
Dimension enhancements	New dimension and hierarchy types, features, and improvements extend the analysis capabilities of cubes.
Data mining enhancements	Data mining is integrated into online analysis and can be used to discover information in OLAP cubes and relational databases.
Security enhancements	Security enhancements include using roles on cube cells and dimension members, additional authentication methods, and improved enforcement.
Client connectivity enhancements inPivotTable® Service	Client applications can use many new features and enhancements such as data mining, HTTP or HTTPS connections, additional dimension types, and cell allocation for writeback.
Other enhancements	Other enhancements provide a variety of new features including multiuser administration, MDX Builder, additional Multidimensional Expressions (MDX) functions, Virtual Cube Editor, support for Active Directory™, and more.

Cube Enhancements

Microsoft SQL Server 2000 Analysis Services substantially extends the scalability and functionality of OLAP cubes. You can distribute cube data across multiple servers to provide more storage capacity, create linked cubes to distribute end-user access to information without duplicating cube data, create cubes that are updated in real time as data changes, and use a number of other new features to create cubes that address your specific business needs.

Distributed Partitioned Cubes

You can create distributed partitioned cubes by using remote partitions that distribute a cube's data among multiple Analysis servers. A distributed partitioned cube is administered on a central Analysis server. For more information, see "Remote Partitions" in SQL Server Books Online.

Real-Time OLAP

Real-time OLAP provides a multidimensional OLAP view of data that is continually updated as the underlying data changes. Real-time cubes implement real-time OLAP by using ROLAP storage for partitions and dimensions, new SQL Server 2000 indexed views for aggregations, and automatic notification by the SQL Server 2000 relational engine when data changes. Real-time cubes provide the capability to develop new categories of OLAP solutions such as call-center management, stock market analysis, or campaign management. For more information, see "Real-Time Cubes" in SQL Server Books Online.

Linked Cubes

A cube can be stored on a single Analysis server and then defined as a linked cube on other Analysis servers. End users connected to any of these Analysis servers can then access the cube. This arrangement avoids the more costly alternative of storing and maintaining copies of a cube on multiple Analysis servers. Linked cubes can be connected using TCP/IP or HTTP. To end users, a linked cube looks like a regular cube. For more information, see "Linked Cubes" in SQL Server Books Online.

Indexed Views for Aggregations

Indexed views for increased performance and flexibility are used instead of aggregation tables for ROLAP partitions if the partition's source data is stored in SQL Server 2000 and if certain criteria are met. For more information, see "Indexed Views for ROLAP Partitions" in SQL Server Books Online.

Cube Processing

You can use lazy aggregations to make cube data available to end users while aggregations are being calculated. When processing cubes for which the underlying data contains dimension key errors, you can elect to stop processing on key errors, stop processing after a specified number of errors, or ignore all key errors. You can have errors logged to a file for later review. For more information, see "Processing Cubes" in SQL Server Books Online.

Calculated Cells

You can specify formulas that apply to individual cells or to sets of cells in a cube. These formulas can contain conditional calculations that compute a new value for a cell or set of cells based on values in the cell or cells, or on values in other cells in the cube. Calculated cells use Multidimensional Expressions (MDX) expressions and you can specify calculations to be performed in multiple passes. Calculated cells can be used in complex financial modeling and budgeting applications; for example, you can specify a default value such as a percentage of a parent cell if the cell value is zero, or to use the actual value if it is not zero. For more information, see "Calculated Cells" in SQL Server Books Online.

Drillthrough

Client applications that support drillthrough can now allow end users to select a cube cell and retrieve a result set from the source data for that cell. You can use roles to control user access to the drillthrough functionality. For more information, see "Specifying Drillthrough Options" in SQL Server Books Online.

Actions

Actions enable end users to act upon the outcomes of their analyses. An action is a predefined operation that an end user can initiate upon a selected cube or portion of a cube. The operation can launch an application with the selected item as a parameter or retrieve information about the selected item. A wizard is provided to help you create actions. For more information, see "Actions" in SQL Server Books Online.

DistinctCount Function

You can use the new DistinctCount aggregate function to analyze the number of unique occurrences of events or transactions in your data, such as unique users visiting a Web site. For more information, see "DistinctCount" in SQL Server Books Online.

Hidden Cube Elements

You can hide complete cubes, dimensions, levels, measures, or member properties from end users who browse cubes with client applications. The visibility of these objects is controlled by the Visible property. For more information, see the topics "Properties Pane (Cube Editor Data View)" and "Properties Pane (Dimension Editor Data View)" in SQL Server Books Online.

Named Sets

You can create, name, and save sets of dimension members or set expressions in a cube. Client applications can use a named set like a dimension by placing the named set on an axis. For more information, see "Named Sets" in SQL Server Books Online.

Default Measures

Default measures can be specified for each cube and varied by role to control default views of cubes for end users. For more information, see the topics "Properties Pane (Cube Editor Data View)" and "Custom Rules in Dimension Security" in SQL Server Books Online.

Virtual Cube Editor

This new editor for virtual cubes is similar in function to Cube Editor. Virtual Cube Editor replaces and expands the functionality provided by the Calculated Member Manager Add-in for SQL Server 7.0 OLAP Services. For more information, see "Virtual Cube Editor" in SQL Server Books Online.

Dimension Enhancements

This release of Microsoft SQL Server 2000 Analysis Services adds significant functionality to OLAP analysis with a number of new dimension types, features, and improvements.

Parent-Child Dimensions

A new parent-child dimension type supports hierarchies based on parent-child links between members in columns in a source table. Such hierarchies represent structures that include organizational charts and part assemblies. Data members can be used to provide data for nonleaf members, such as the direct commission amount for a sales department manager or the individual salaries for all members in an organizational chart. For more information, see "Parent-Child Dimensions" in SQL Server Books Online.

ROLAP Dimensions

Extremely large dimensions can now be accommodated using the ROLAP storage mode. The dimension data remains in the database table and is not subject to the size limitations of MOLAP. For more information, see "Dimension Storage Modes" in SQL Server Books Online.

Write-Enabled Dimensions

The members of write-enabled dimensions can be updated through Analysis Manager and client applications that support dimension writeback. Roles are used to control dimension write access by client applications. For more information, see "Write-Enabled Dimensions" in SQL Server Books Online.

Changing Dimensions

The new changing dimension type permits dimension members to be deleted, moved, added, or renamed without requiring the cube to be fully processed after changes. This increases the availability of cubes to client applications. For more information, see "Changing Dimensions" in SQL Server Books Online.

Dependent Dimensions

Dependent dimensions permit improved aggregation design optimization by using knowledge of nonintersecting member combinations to reduce storage requirement estimation in the design algorithm. For more information, see "Dependent Dimensions" in SQL Server Books Online.

Ragged Dimensions

Ragged dimensions have at least one member whose logical parent is not in the level immediately above the member, such as is the case of countries/regions that do not have a state or province level between the country/region and city levels. For more information, see "Ragged Dimension Support" in SQL Server Books Online.

Enhanced Virtual Dimensions

Virtual dimensions have better performance and greater flexibility than in earlier versions. They can be based directly on columns in another dimension's table and include multiple levels. Virtual dimensions are no longer limited to 760 members. For more information, see the topics "Virtual Dimensions" and "Virtual Dimensions Created in Version 7.0" in SQL Server Books Online.

Custom Rollup Formulas and Custom Member Formulas

Cube cell values associated with members can be calculated according to custom expressions rather than the aggregate functions of measures. The expressions can be applied to all the members in a level or individual members. For more information, see "Custom Rollup Formulas and Custom Member Formulas" in SQL Server Books Online.

Automatic Member Grouping

System-generated member groups can be used to accommodate members that have more than 64,000 children. This feature can also be used to provide an intermediate level for drilldown between a level with few members and one with numerous members. For more information, see "Member Groups" in SQL Server Books Online.

Default Members

Default members can be specified for each dimension and varied by role. They control end users' default views of cubes that include the dimension. For more information, see the topics "Set Default Member Dialog Box" and "Custom Rules in Dimension Security" in SQL Server Books Online.

Dimension Filters

A filter (WHERE clause expression) can be used to limit the dimension table rows that are included in the dimension. The filter is specified in the Source Table Filter property of the dimension. For more information, see "Properties Pane (Dimension Editor Data View)" in SQL Server Books Online.

Data Mining Enhancements

Data mining technology analyzes data in relational databases and OLAP cubes to discover information of interest. The data mining features of Microsoft SQL Server 2000 Analysis Services are incorporated in an open and extensible implementation of the new OLE DB for Data Mining specification. SQL Server 2000 includes data mining algorithms developed by Microsoft Research.

Relational and OLAP Data Mining

Analysis Services has incorporated data mining technology so you can use it to discover information in relational databases and in OLAP cubes in Analysis Services. You can use the results of data mining to create a dimension that you can add to a cube to further analyze your data. For more information, see "Data Mining Models" in SQL Server Books Online.

Microsoft Decision Trees

The Microsoft Decision Trees algorithm uses classification techniques to analyze data. It then constructs one or more decision trees that can be used to predict attributes or values for new data. For example, you can use this algorithm to analyze credit history data and predict the credit risk of new applicants. For more information, see "Microsoft Decision Trees" in SQL Server Books Online.

Microsoft Clustering

The Microsoft Clustering algorithm uses a nearest neighbor method to group records into clusters that share similar characteristics. Often, these characteristics may be hidden or not intuitive. For more information, see "Microsoft Clustering" in SQL Server Books Online.

Data Mining User Interface

Analysis Services provides new user interface wizards, dialog boxes, and editors to help you quickly perform data mining administrative tasks such as building data mining models and incorporating the results in OLAP cubes. You can browse a single decision tree model or the dependency network model of multiple trees produced when multiple attributes are predicted. For more information, see "Building and Using Data Mining Models" in SQL Server Books Online.

MDX Extensions for Data Mining

Multidimensional Expressions (MDX) syntax has been extended to provide data mining capabilities in connection with OLAP cubes. For more information, see "MDX" in SQL Server Books Online.

DTS Tasks for Data Mining

The Analysis Services Processing task has been enhanced to allow processing mining models, and a new Data Transformation Services (DTS) task is provided that you can use to create predictions from mining models. For more information, see "Automating and Scheduling Administrative Tasks" in SQL Server Books Online.

Data Mining in Client Applications

Client applications for Analysis Services can use data mining algorithms to discover information in OLAP cubes by creating data mining models and virtual cubes. For more information, see "What's New in PivotTable Service" in SQL Server Books Online.

Security Enhancements

This release of Microsoft SQL Server 2000 Analysis Services includes features that provide more flexibility in controlling access to cube data, additional methods for authentication of users, and enhanced enforcement of roles.

Dimension Security

You can use roles to control end-user access to dimensions. For each role, you can limit access to individual dimensions, levels, and members, and you can set various read and read/write permissions. For more information, see "Dimension Security" in SQL Server Books Online.

Enhanced Cell Security

Analysis Manager now includes dialog boxes that enable you to define cell security by setting role options to control end user access to cube cells. You can limit a role's access to any combination of a cube's cells and you can vary the read and read/write permission of the role. For more information, see "Cell Security" in SQL Server Books Online.

Additional Authentication Methods

You can use HTTP authentication methods for client connections to Analysis servers. Analysis Services also supports Windows 2000 negotiated protocol authentication. For more information, see "Server Security and Authentication" in SQL Server Books Online.

Enhanced Role Enforcement

Role specifications are enforced on Analysis servers installed on either file allocation table (FAT) or NTFS file systems.

Client Connectivity Enhancements in PivotTable Service

Enhancements to PivotTable Service provide new connection options and support new cube and security features introduced in this release of Microsoft SQL Server 2000 Analysis Services. Additional new functionality in PivotTable Service can be used by client applications when connected to an Analysis server or when working offline. For more information, see "What's New in PivotTable Service" in SQL Server Books Online.

Connecting to the Analysis Server

Client applications can communicate with the Analysis server through Microsoft Internet Information Services (IIS) using HTTP or HTTPS. Third-party security providers can also be used.

Allocated Writeback

You can write data to aggregation cells in a cube and, using one of four different allocation formulas, automatically distribute the data to the lowest-level members. For more information, see "Using Writebacks" in SQL Server Books Online.

Managing Local Cubes

Many of the new features for server cubes, such as new dimension types, custom rollups, and calculated members, can also be used in local cubes.

Data Mining

PivotTable Service supports the advanced data mining and analysis techniques that are available in SQL Server 2000 Analysis Services. You can create and use data mining models to analyze data in server cubes or local databases.

Other Enhancements

This release of Microsoft SQL Server 2000 Analysis Services incorporates various enhancements to improve usability, processing performance, and MDX functionality.

Multiuser Administration

Multiple users can administer an Analysis server using Analysis Manager. Locking is applied only to the objects being edited and their dependent objects. For more information, see "Analysis Manager" in SQL Server Books Online.

Integration of Add-ins

The Archive and Restore Databases Add-in and the Copy and Paste Objects Add-in originally created for SQL Server 7.0 OLAP Services are upgraded, installed, and fully integrated in this release. You can now use Analysis Manager to archive and restore SQL Server 2000 Analysis Services databases and to copy and paste objects in the Analysis Manager tree pane. For more information, see the topics "Archiving and Restoring Databases" and "Copying and Pasting Objects" in SQL Server Books Online.

MDX Enhancements

A number of new functions have been added to the Multidimensional Expressions (MDX) syntax that you can use for calculated members and increased analytical evaluation. For more information, see "MDX" in SQL Server Books Online.

MDX Builder

A new MDX Builder tool helps you create MDX expressions using drag-and-drop techniques. The MDX editing text box has been enhanced to provide color coding of keywords and functions, indication of parentheses balance, and smart tips that show function syntax. For more information, see "MDX Builder" in SQL Server Books Online.

Table Aliases

In Cube Editor and Dimension Editor you can specify table aliases. For more information, see the topics "Schema Tab (Cubes Editor Schema View)" and "Schema Tab (Dimension Editor Schema View) in SQL Server Books Online.

Active Directory Support

Analysis Services supports Active Directory, the directory service that is included with Microsoft Windows 2000. You can enable an Analysis server so that it publishes Active Directory entries when the server starts, and updates the entries when the server status changes. For more information, see "Using Active Directory with Analysis Services" in SQL Server Books Online.

Performance Monitor Counters

A number of performance counters are now available for Analysis server that can be used with Performance Monitor in Microsoft Windows NT® 4.0 or System Monitor in Windows 2000. For more information, see "Monitoring Analysis Services Performance" in SQL Server Books Online.

Meta Data Services Enhancements

Microsoft SQL Server 2000 Meta Data Services extends and renames the former repository component known as Microsoft Repository. Meta Data Services extends repository technology by introducing a new browser for viewing data in a repository database, new Extensible Markup Language (XML) interchange support, and new repository engine features.

Feature	Description
Meta Data Browser enhancement	Meta Data Browser is a new tool you can use to browse a repository database.
XML Encoding enhancements	XML Encoding supports a new implementation of meta data interchange in Meta Data Coalition (MDC) Open Information Model (OIM) XML.
Repository engine programming enhancements	Programming enhancements detail new and better ways for programming against an installed information model.
Repository engine modeling enhancements	Modeling enhancements support new definitions that you can include in an information model.

Meta Data Browser Enhancement

Meta Data Browser is a new tool you can use to browse the contents of a repository database. This tool is introduced in Microsoft SQL Server 2000 Meta Data Services. You can run Meta Data Browser when you select Meta Data Services. Meta Data Services is available for each copy of SQL Server you install. For more information, see "Using Meta Data Browser" in SQL Server Books Online.

XML Encoding Enhancements

Microsoft SQL Server 2000 Meta Data Services now uses Extensible Markup Language (XML) encoding in native mode. You can import, export, and publish repository meta data in a format that more closely matches your information model.

XML encoding supersedes the XML Interchange Format (XIF) that was part of previous versions of the software. For more information, see the topics "XML in Meta Data Services" and "Using XML Encoding" in SQL Server Books Online.

New COM Interfaces

XML IExport Interface

XML IImport Interface

For more information, see the specific interface topics in SQL Server Books Online.

Repository Engine Programming Enhancements

Repository engine version 3.0 is backward compatible with version 2.0 functionality and interfaces. You can use version 3.0 with no change to the databases. In this case, only the version 2.0 features will work. You can upgrade the database version 2.0 to the database version 3.0 format by passing the REPOS_CONN_UPGRADE flag when you open the repository database. Another way to upgrade a repository database is through Meta Data Browser, during repository database registration. Upgrading allows you to use all the features of version 3.0. After you upgrade; however, you cannot open the upgraded database using a version 2.0 engine.

Microsoft SQL Server 2000 Meta Data Services introduces the following new repository engine features in version 3.0. These features extend the ability to program against an information model that resides in a repository database.

View Generation

View generation provides a way to automatically generate relational views based on classes, interfaces, and relationships in an information model. You can use the relational views that you define to simplify repository database queries. To generate views, four new interfaces have been exposed to the repository engine. For more information, see "Generating Views" in SQL Server Books Online.

New COM Interfaces 

IViewClassDef Interface

IViewInterfaceDef Interface

IViewPropertyDef Interface

IViewRelationshipDef Interface

For more information, see the specific interface topics in SQL Server Books Online.

Performance Hints

New performance hints described in SQL Server Books Online can be used to optimize engine performance. In addition to the existing list of optimization techniques, you can adjust cache aging to vary aging for different row types, enable atomicity of operations, and preload object collections for each repository object. The new performance enhancements can be implemented through IReposOptions. For adjustable cache aging, set one or more of the AGEOUT options. For atomicity of operations, set OPT_ATOMICMODE. For preloading object collections, set OPT_EXPORT_MODE or OPT_PRELOAD_COL_MODE to specify the maximum number of objects in each collection.

New COM Interfaces 

IReposOptions Interface

For more information, see the specific interface topic in SQL Server Books Online.

Property Extensions for BLOBs and Large Text Fields

Property extensions have been exposed to the repository engine to handle large properties or binary large objects (BLOBs) and give access to the meta data about the object. For more information, see "Programming BLOBs and Large Text Fields" in SQL Server Books Online.

New COM Interfaces 

IReposProperty2 Interface

IReposPropertyLarge Interface

IRepositoryObject2 Interface

For more information, see the specific interface topics in SQL Server Books Online.

Collection Filters

Collection filters now support SQL selection criteria through the repository API. In this version of the repository engine, you can specify selection criteria for any target object at run time to precisely select objects of interest. To set criteria, use the GetCollection method of the IReposQuery interface.

New COM Interfaces 

IReposQuery Interface

For more information, see the specific interface topic in SQL Server Books Online.

Integration with MS DTC

The repository engine is integrated with Microsoft Distributed Transaction Coordinator (MS DTC), so that an application that is using the repository engine can execute a transaction that spans multiple database systems. For more information, see "Integration with Distributed Transaction Coordinator" in SQL Server Books Online.

New COM Interfaces 

IRepositoryTransaction2 Interface

For more information, see the specific interface topic in SQL Server Books Online.

Version Propagation

Version propagation behavior has been extended to relationships through two new flags. You can set COLLECTION_NEWDESTVERSIONADD to specify that an origin object always link to the latest version of a destination object. This eliminates manual versioning of an origin object in response to a new versioned destination object. The second new flag, COLLECTION_NEWDESTVERSIONPROPAGATE, has the opposite effect. It can be set to expand propagation behavior deeper into a chain of relationships. In cases where an object is both an origin and a destination, setting this flag creates a reverse-cascade versioning effect. A new destination object version causes the creation of a new origin object version, repeating this behavior until the engine reaches an unfrozen object. Setting this flag automates a task that developers previously had to handle in application code. For more information, see the topics "Propagating Versions" and "CollectionDefFlags Enumeration" in SQL Server Books Online.

Repository Engine Modeling Enhancements

Microsoft SQL Server 2000 Meta Data Services introduces the following new repository engine features in version 3.0 of the engine. These features extend the ability to create an information model that can take advantage of new features added to this version of the repository engine.

Scripting Support

Scripting support provides the ability to validate properties and implement methods through ActiveX scripts. Model creators can assign scripts to methods that are defined in an information model. With scripting support, you no longer need to use aggregation to validate properties and implement methods. For more information, see "Defining Script Objects" in SQL Server Books Online.

New COM Interfaces 

IClassDef2 Interface

IInterfaceMember2 Interface

IInterfaceDef2 Interface

IScriptDef Interface

For more information, see the specific interface topics in SQL Server Books Online.

Interface Implication

Interface implication enables a client to define an implication between two interfaces in an information model. Using IInterfaceDef2, you can simulate some of the functionality of multiple inheritance, which is not allowed in COM. For more information, see "Interface Implication" in SQL Server Books Online.

New COM Interfaces 

IInterfaceDef2 Interface

For more information, see the specific interface topic in SQL Server Books Online.

Member Delegation

Member delegation extends the engine to support the delegation of derived members on one interface to base members on another interface. This delegation can be used to support relationship inheritance. Two interfaces support this new mapping capability: IInterfaceMember2 and IInterfaceDef2. For more information, see "Member Delegation" in SQL Server Books Online.

New COM Interfaces 

IInterfaceMember2 Interface

IInterfaceDef2 Interface

For more information, see the specific interface topics in SQL Server Books Online.

Sharing Model Information

Model dependency allows model developers to define dependencies between information models, to support the creation of more modular, integrated models. To support model dependency, use the new DependsOn collection of IReposTypeLib2. For more information, see "Define Dependencies Between Type Libraries" in SQL Server Books Online.

New COM Interfaces 

IReposTypeLib2 Interface

For more information, see the specific interface topic in SQL Server Books Online.

Naming Semantics for Objects

Object naming semantics have been extended to support more consistent naming. In this version of repository engine, you can set the COLLECTION_OBJECTNAMING flag of CollectionDefFlag to specify an object name automatically when creating names for subsequent collections and relationships. For more information, see "CollectionDefFlags Enumeration" in SQL Server Books Online.

Parameter Support

Parameters can now be defined in models. Version 3.0 supports method parameter definitions so model creation can include complete object descriptions. You can now use the Meta Data Services Software Development Kit (SDK) to generate fully descriptive Interface Definition Language (IDL) files from an information model. For more information, see "Defining a Parameter" in SQL Server Books Online.

New COM Interfaces 

IMethodDef Interface

IParameterDef Interface

For more information, see the specific interface topics in SQL Server Books Online.

Enumeration Definition

Repository enumeration definition allows you to specify a property as an enumeration that has a fixed set of constant strings or integer values that correspond to real-world concepts. To use this feature, specify an EnumerationDef object and associated EnumerationValue objects and associate these with PropertyDef objects. For more information, see "Repository Enumeration Definition" in SQL Server Books Online.

New COM Interfaces 

IEnumerationDef Interface

IEnumerationValueDef Interface

IPropertyDef2 Interface

For more information, see the specific interface topics in SQL Server Books Online.

Type Information Aliasing

Classes, interfaces, and relationships can be referred to by a second name (a synonym). You can implement type information aliasing through new interfaces.

New COM Interfaces 

IReposTypeInfo Interface

IReposTypeInfo2 Interface

IInterfaceMember2 Interface

For more information, see the specific interface topics in SQL Server Books Online.

Version Labeling

Version labeling allows users to set or retrieve the version comments properties. Use IVersionAdminInfo2 to implement version labeling.

New COM Interfaces 

IVersionAdminInfo2 Interface

For more information, see the specific interface topic in SQL Server Books Online.

Virtual Members

Virtual member support enables you to define nonpersistent members by setting the VIRTUAL_MEMBER flag on the InterfaceMemberFlags property. If this flag is set and the member is a property, the engine does not allocate a column for it in the interface's table. For more information, see the topics "Virtual Members" and "IInterfaceMember Flags Property" in SQL Server Books Online.

English Query Enhancements

English Query contains the following new features:

• Microsoft® Visual Studio® Integration 

• Graphical Authoring 

• SQL Project Wizard 

• Analysis Services Integration (formerly OLAP Services) and OLAP Project Wizard 

• Semantic Modeling Format (SMF) and Authoring Object Model 

• New Regression Features 

• Full-Text Query Support 

For more information, see Chapter 32, "English Query Best Practices."

Visual Studio Integration

The English Query authoring tool is now integrated into the familiar Visual Studio version 6.0 development environment.

Graphical Authoring

A diagramming tool is provided that displays the entities and relationships in the English Query model you are creating. You can also drag entities onto one another to automatically create relationships between them.

SQL Project Wizard

You can use the SQL Project Wizard to automatically create entities and relationships for all tables in the database. For each entity, it automatically builds name and trait relationships, plus other relationships, resulting in automatic creation of the majority of the entities and relationships needed in a model.

Analysis Services Integration (formerly OLAP Services) and OLAP Project Wizard

The OLAP Project Wizard provides an even higher percentage of automatic entity and relationship creation for OLAP databases. When connected to Microsoft SQL Server 2000 Analysis Services and a model authored for an OLAP cube, Multidimensional Expressions (MDX), rather than SQL, are generated.

Semantic Modeling Format (SMF) and Authoring Object Model

SMF is an XML-based language that is used for persisting English Query model information (entities and relationships and their ties to the database). Combined with SMF, the Authoring object model provides programmatic authoring of English Query models.

New Regression Features

Regression test output can now be promoted to serve as the new regression text file. Structured results output can be used as input to other tools.

Full-Text Query Support

This version integrates the SQL Server full-text feature, which allows the searching of both structured and unstructured textual data within English Query applications.

Two-Click Deployment

You can deploy an English Query application to the Web and to Analysis Services simply by clicking twice and selecting several commands from the Project menu.

Enhanced Authoring by Example

During the testing process, you can see suggested entities and phrasings that need to be defined to answer questions. The new version provides multiple suggestions and simplifies the clarification process by providing a single form in which you can work.

Oracle Database Support

English Query can generate SQL for Oracle.

Graphical Question Builder

A querying interface is now provided for the end user of English Query applications. Question Builder uses the English Query model behind the application to provide easy question building and quick answers from the database. End users can drag entities onto other entities to display all available relationships.

Documentation Enhancements

The following enhancements have been made to the documentation for Microsoft SQL Server 2000:

F1 Help Integrated with SQL Server Books Online

The F1 Help for SQL Server 2000 has been integrated with SQL Server Books Online. When you select F1 Help, a related topic in SQL Server Books Online is displayed.

SQL Server Books Online Supports Subsets

SQL Server Books Online allows you to define subsets of the entire SQL Server Books Online, against which you can perform a search. You can define your own subsets; however, a set of default subsets is also provided. The subsets are targeted at specific audiences, and each subset covers multiple topics. Subsets make it easier to search for information related to your activities by minimizing the number of search hits.

Thumbnail Art

Some diagrams in SQL Server Books Online now appear as thumbnail diagrams. The full diagram can be viewed by clicking Enlarge diagram. The full diagram appears when the topic is printed.

Glossary Improvements

Glossary terms appear as colored, underlined text. When a term is clicked, a pop-up window appears with the term definition.

Easier to Access Related Topics

Many topics have a list of related topics, called "See Also," at the end. SQL Server Books Online adds a See Also icon that displays the list of related topics when you click it.