Lesson 3: Data Warehousing Features of Microsoft SQL Server 7.0

SQL Server 7.0 provides a set of tools to create data warehouses. This lesson highlights the Microsoft tools that can be used to implement the solutions discussed in this and later chapters. Because Microsoft has built the online analytical processing (OLAP) Services of SQL Server 7.0 on open industry standards, data warehousing solutions can be implemented through any combination of

• Microsoft components and end-user tools such as Office 2000
• Custom-built analysis tools made from development tools such as Microsoft Visual Basic
• Tools created by other vendors

This lesson focuses on the components included as part of OLAP Services of SQL Server 7.0. Later chapters examine custom-built analysis tools and tools created by other vendors.

After this lesson, you will be able to:

• Describe some of the Microsoft SQL Server version 7.0 tools for data warehousing

Estimated lesson time: 25 minutes

Microsoft Data Warehousing Framework

Data warehousing can be viewed in the context of a framework of components and capabilities that are all needed to implement a system. The software components contribute to the process of building, using, and managing the data warehouse system. Figure 1.1 shows the components of the Microsoft Data Warehousing Framework.

Microsoft is developing a framework to make the deployment of data warehouse systems less complex and more efficient. In this section we discuss the elements Microsoft provides and the important contributions made by alliance partners.

From a software foundation perspective, these systems will

• Build or populate data warehouse databases on SQL Server
• Provide efficient access to many kinds of data via OLE DB APIs
• Share metadata among the system components
• Manage the system with tools such as the Microsoft Management Console (MMC)

Some of these key topics are discussed individually.

Figure 1.1 Microsoft Data Warehousing Framework

SQL Server 7.0 Data Warehousing Tools

SQL Server 7.0 includes a wealth of powerful data warehousing and decision support tools. These tools are unique because of the unprecedented power available in a package in its price range. Available services include those discussed in the following sections.

Data Transformation Services

Data Transformation Services (DTS) allow a user to move data from multiple, heterogeneous data sources into one or more targets. You can manipulate the data as you move it, making it consistent. DTS can also create summarized values from the base data.

OLAP Services

OLAP Services build multidimensional cubes for online analytical processing (OLAP). The tools used to build these cubes can analyze the dimensions and aggregations and recommend what values should be stored and what values can be calculated dynamically. As a result, you can choose the speed of queries versus the amount of disk space needed.

Microsoft English Query

Microsoft English Query allows you to query the data warehouse using plain English language instead of SQL statements. Therefore, users without knowledge of joins, field names, and SQL syntax can ask questions of the warehouse and receive data.

PivotTable Service

The PivotTable Service enables analysis on the client machine. Cube subsets can be stored on the client machine, so analysis is faster and the client does not have to go back to the server for data. The client machine can even be disconnected from the network so that the cube can be taken off line for analysis.

Advanced Database Support

SQL Server 7.0 includes advanced support for very large databases (VLDBs). SQL Server now runs almost all functions in parallel, which allows for better performance, giving rapid response even with terabyte-sized databases.

Sophisticated Query Processing

The query processor in SQL Server 7.0 has been enhanced to handle numerous sophisticated strategies for the types of queries required by data warehousing.

Exercise: Using OLAP Services Product Documentation

In this exercise, you will use OLAP Services product documentation to retrieve information on OLAP Services.

To view the contents of OLAP Services product documentation

In this procedure, you will view the contents of OLAP Services product documentation and familiarize yourself with the conventions used in the documentation.

1. If you have not already done so, log on to your computer.
2. From the Start menu, point to Programs, point to Microsoft SQL Server 7.0, and then in the OLAP Services group, click Product Documentation.

Notice the layout of the interface. On the left side, you will see the navigation pane that displays topics in a series of hierarchies. The navigation pane has four tabs along the top (Contents, Index, Search, and Favorites) that give users different ways to navigate the topics provided in the documentation. The pane on the right side, the topic pane, is used for displaying detailed information for each topic provided by the documentation.

3. In the navigation pane, review the organization of the product documentation.
4. Click the + sign next to Getting Started to expand the selection. Click Documentation Conventions, and then review the contents of the topic pane.
5. In the navigation pane, click Finding Information on the Internet, and review the contents of the topic pane.

To find a topic using the Index and add a topic to Favorites

In this procedure, you will use the Index to find a topic quickly. You will then add the topic to Favorites so that it is easy to find when you next want to refer to it.

1. Click the Index tab.
2. In the keyword text box, type cubes.
3. In the navigation pane, you should see a list of topics under the word cubes. Double-click the topic "defining," and review the contents of the topic pane.
4. Click the Favorites tab.
5. Click the Add button to add the topic to your list of Favorites topics.
6. Click the Index tab and then double-click the topic "building" to change to another topic.
7. Click the Favorites tab, and then double-click the Defining Cubes topic to display it again.

To search the documentation for a word or phrase

In this procedure, you will search the documentation for a particular topic.

1. Click the Search tab.
2. In the text box, type Data Warehousing and OLAP, and then click List Topics.
3. In the navigation pane, select the topic Data Warehousing and OLAP, and then click the Display button (alternatively, you can just double-click the topic).
4. Close the Microsoft SQL Server OLAP Services documentation.

Lesson Summary

SQL Server includes components that support all aspects of the data warehousing process, from transforming data through querying and cube generation.