×

Uh-oh, it looks like your Internet Explorer is out of date.

For a better shopping experience, please upgrade now.

Microsoft SQL Server 2000 Resource Kit
     

Microsoft SQL Server 2000 Resource Kit

by Microsoft Press, Microsoft Corporation
 

Microsoft SQL Server 2000 is the high-end, mission-critical relational database management system for rapidly building the next generation of scalable e-commerce, line-of-business, and data-warehousing solutions. "Microsoft SQL Server 2000 Resource Kit" gives database administrators and other IT professionals the definitive technical information and tools

Overview

Microsoft SQL Server 2000 is the high-end, mission-critical relational database management system for rapidly building the next generation of scalable e-commerce, line-of-business, and data-warehousing solutions. "Microsoft SQL Server 2000 Resource Kit" gives database administrators and other IT professionals the definitive technical information and tools they need to deploy, manage, and maintain SQL Server 2000. Delivered direct from the Microsoft SQL Server 2000 product team and Microsoft Consulting Services, it includes special information about core functionality and new features of the product, plus a CD-ROM packed with unique tools and utilities to simplify SQL Server management. It also covers vital topics such as security, clustering and scalability, and Extensible Markup Language (XML). This powerhouse reference is an essential resource for every database administrator who seeks maximum performance from SQL Server 2000.

Editorial Reviews

bn.com
The Barnes & Noble Review
If you've invested in Microsoft SQL Server 2000 (or are seriously considering it), you'll find the Microsoft SQL Server 2000 Resource Kit to be an absolutely essential companion.

First, there's complete lifecycle coverage for getting SQL Server 2000 deployed efficiently and keeping it running smoothly. That includes critical planning information, guidance on Microsoft's latest DBA tools, backup/recovery techniques, and Microsoft's best wisdom on optimization, troubleshooting, and security.

You'll also find advanced availability and scalability techniques, from clustering to data center best practices, as well as coverage of SQL Server 2000's improved (and thankfully simplified) replication capabilities. There's even coverage of using VB to create merge-replication custom conflict resolvers that apply your specific data or business decision rules.

You'll find extensive guidance on building data warehouses with SQL Server 2000: designing, partitioning, data extraction, transformation, loading, and Microsoft's souped-up Analysis Services. You'll also learn how to customize internal web portals with Microsoft's latest Digital Dashboard framework.

There's a CD-ROM full of tools and resources (including a complete eBook and excerpts in Pocket PC format), plus another disk containing a 120-day SQL Server 2000 trial version. No other SQL Server 2000 resource compares. (Bill Camarda)

Bill Camarda is a consultant and writer with nearly 20 years' experience in helping technology companies deploy and market advanced software, computing, and networking products and services. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks For Dummies®, Second Edition.

Product Details

ISBN-13:
9780735612662
Publisher:
Microsoft Press
Publication date:
04/28/2001
Series:
Resource Kit Series
Edition description:
BK&CD-ROM
Pages:
1164
Product dimensions:
7.38(w) x 9.14(h) x 2.08(d)
Age Range:
13 Years

Related Subjects

Read an Excerpt

Chapter 17 Data Warehouse Design Considerations

Data warehouses support business decisions by collecting, consolidating, and organizing data for reporting and analysis with tools such as online analytical processing (OLAP) and data mining. Although data warehouses are built on relational database technology, the design of a data warehouse database differs substantially from the design of an online transaction processing system (OLTP) database.

The topics in this chapter address approaches and choices to be considered when designing and implementing a data warehouse. The chapter begins by contrasting data warehouse databases with OLTP databases and introducing OLAP and data mining, and then adds information about design issues to be considered when developing a data warehouse with Microsoft® SQL Server 2000.

Data Warehouses, OLTP, OLAP, and Data Mining

A relational database is designed for a specific purpose. Because the purpose of a data warehouse differs from that of an OLTP, the design characteristics of a relational database that supports a data warehouse differ from the design characteristics of an OLTP database.

Data warehouse databaseOLTP database
Designed for analysis of business measures by categories and attributesDesigned for real-time business operations
Optimized for bulk loads and large, complex, unpredictable queries that access many rows per tableOptimized for a common set of transactions, usually adding or retrieving a single row at a time per table
Loaded with consistent, valid data; requires no real time validationOptimized for validation of incoming data during transactions; uses validation data tables
Supports few concurrent users relative to OLTPSupports thousands of concurrent users

A Data Warehouse Supports OLTP

A data warehouse supports an OLTP system by providing a place for the OLTP database to offload data as it accumulates, and by providing services that would complicate and degrade OLTP operations if they were performed in the OLTP database.

Without a data warehouse to hold historical information, data is archived to static media such as magnetic tape, or allowed to accumulate in the OLTP database.

If data is simply archived for preservation, it is not available or organized for use by analysts and decision makers. If data is allowed to accumulate in the OLTP so it can be used for analysis, the OLTP database continues to grow in size and requires more indexes to service analytical and report queries. These queries access and process large portions of the continually growing historical data and add a substantial load to the database. The large indexes needed to support these queries also tax the OLTP transactions with additional index maintenance. These queries can also be complicated to develop due to the typically complex OLTP database schema.

A data warehouse offloads the historical data from the OLTP, allowing the OLTP to operate at peak transaction efficiency. High volume analytical and reporting queries are handled by the data warehouse and do not load the OLTP, which does not need additional indexes for their support. As data is moved to the data warehouse, it is also reorganized and consolidated so that analytical queries are simpler and more efficient.

OLAP Is a Data Warehouse Tool

Online analytical processing (OLAP) is a technology designed to provide superior performance for ad hoc business intelligence queries. OLAP is designed to operate efficiently with data organized in accordance with the common dimensional model used in data warehouses.

A data warehouse provides a multidimensional view of data in an intuitive model designed to match the types of queries posed by analysts and decision makers. OLAP organizes data warehouse data into multidimensional cubes based on this dimensional model, and then preprocesses these cubes to provide maximum performance for queries that summarize data in various ways. For example, a query that requests the total sales income and quantity sold for a range of products in a specific geographical region for a specific time period can typically be answered in a few seconds or less regardless of how many hundreds of millions of rows of data are stored in the data warehouse database.

OLAP is not designed to store large volumes of text or binary data, nor is it designed to support high volume update transactions. The inherent stability and consistency of historical data in a data warehouse enables OLAP to provide its remarkable performance in rapidly summarizing information for analytical queries.

In SQL Server 2000, Analysis Services provides tools for developing OLAP applications and a server specifically designed to service OLAP queries.

Data Mining is a Data Warehouse Tool

Data mining is a technology that applies sophisticated and complex algorithms to analyze data and expose interesting information for analysis by decision makers. Whereas OLAP organizes data in a model suited for exploration by analysts, data mining performs analysis on data and provides the results to decision makers. Thus, OLAP supports model-driven analysis and data mining supports data-driven analysis.

Data mining has traditionally operated only on raw data in the data warehouse database or, more commonly, text files of data extracted from the data warehouse database. In SQL Server 2000, Analysis Services provides data mining technology that can analyze data in OLAP cubes, as well as data in the relational data warehouse database. In addition, data mining results can be incorporated into OLAP cubes to further enhance model-driven analysis by providing an additional dimensional viewpoint into the OLAP model. For example, data mining can be used to analyze sales data against customer attributes and create a new cube dimension to assist the analyst in the discovery of the information embedded in the cube data.

For more information and details about data mining in SQL Server 2000, see Chapter 24, "Effective Strategies for Data Mining."

Designing a Data Warehouse: Prerequisites

Before embarking on the design of a data warehouse, it is imperative that the architectural goals of the data warehouse be clear and well understood. Because the purpose of a data warehouse is to serve users, it is also critical to understand the various types of users, their needs, and the characteristics of their interactions with the data warehouse.

Data Warehouse Architecture Goals

A data warehouse exists to serve its users — analysts and decision makers. A data warehouse must be designed to satisfy the following requirements:

  • Deliver a great user experience — user acceptance is the measure of success
  • Function without interfering with OLTP systems
  • Provide a central repository of consistent data
  • Answer complex queries quickly
  • Provide a variety of powerful analytical tools such as OLAP and data mining

Most successful data warehouses that meet these requirements have these common characteristics:

  • Are based on a dimensional model
  • Contain historical data
  • Include both detailed and summarized data
  • Consolidate disparate data from multiple sources while retaining consistency
  • Focus on a single subject such as sales, inventory, or finance

Data warehouses are often quite large. However, size is not an architectural goal — it is a characteristic driven by the amount of data needed to serve the users.

Data Warehouse Users

The success of a data warehouse is measured solely by its acceptance by users. Without users, historical data might as well be archived to magnetic tape and stored in the basement. Successful data warehouse design starts with understanding the users and their needs.

Data warehouse users can be divided into four categories: Statisticians, Knowledge Workers, Information Consumers, and Executives. Each type makes up a portion of the user population as illustrated in this diagram....


Click to view graphic

Statisticians: There are typically only a handful of statisticians and operations research types in any organization. Their work can contribute to closed loop systems that deeply influence the operations and profitability of the company.

Knowledge Workers: A relatively small number of analysts perform the bulk of new queries and analyses against the data warehouse. These are the users who get the Designer or Analyst versions of user access tools. They will figure out how to quantify a subject area. After a few iterations, their queries and reports typically get published for the benefit of the Information Consumers. Knowledge Workers are often deeply engaged with the data warehouse design and place the greatest demands on the ongoing data warehouse operations team for training and support.

Information Consumers: Most users of the data warehouse are Information Consumers; they will probably never compose a true ad hoc query. They use static or simple interactive reports that others have developed. They usually interact with the data warehouse only through the work product of others. This group includes a large number of people, and published reports are highly visible. Set up a great communication infrastructure for distributing information widely, and gather feedback from these users to improve the information sites over time.

Executives: Executives are a special case of the Information Consumers group.

How Users Query the Data Warehouse

Information for users can be extracted from the data warehouse relational database or from the output of analytical services such as OLAP or data mining. Direct queries to the data warehouse relational database should be limited to those that cannot be accomplished through existing tools, which are often more efficient than direct queries and impose less load on the relational database.

Reporting tools and custom applications often access the database directly. Statisticians frequently extract data for use by special analytical tools. Analysts may write complex queries to extract and compile specific information not readily accessible through existing tools. Information consumers do not interact directly with the relational database but may receive e-mail reports or access web pages that expose data from the relational database. Executives use standard reports or ask others to create specialized reports for them.

When using the Analysis Services tools in SQL Server 2000, Statisticians will often perform data mining, Analysts will write MDX queries against OLAP cubes and use data mining, and Information Consumers will use interactive reports designed by others....

Meet the Author

Founded in 1975, Microsoft Corporation (Nasdaq 'MSFT') is the worldwide leader in software for personal and business computing. The company offers a wide range of products and services designed to empower people through great software—any time, any place, and on any device.

Customer Reviews

Average Review:

Post to your social network

     

Most Helpful Customer Reviews

See all customer reviews