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
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
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
Some of these key topics are discussed individually.
Figure 1.1 Microsoft Data Warehousing Framework
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 (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 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 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.
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.
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.
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.
In this exercise, you will use OLAP Services product documentation to retrieve information on OLAP Services.
In this procedure, you will view the contents of OLAP Services product documentation and familiarize yourself with the conventions used in the 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.
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.
In this procedure, you will search the documentation for a particular topic.
SQL Server includes components that support all aspects of the data warehousing process, from transforming data through querying and cube generation.