Microsoft SQL Server 7.0: Designing and Implementing Data Warehouses - Part 2

Course Code NGE72771

Contents

1. Contents
2. Description
3. Audience
4. Prerequisites
5. Objectives
6. Topics Include
7. Duration
8. Minimum Requirements
9. Media

Screenshot

Description

This is the second course in a five part series that will help students prepare for the Microsoft Certified Professional exam 70-019, Designing and Implementing Data Warehouses with Microsoft SQL Server 7.0. At the completion of this course, students will learn to identify the design characteristics of OLAP systems and Aggregate schemas; identify the data warehouse models; identify the Data Transformation Services (DTS) components and the components of the DTS object model; identify the methods of distributing data; and identify the replication types and techniques.

Audience

This course series is designed for IT professionals responsible for planning, implementing, and maintaining a data warehouse using Microsoft SQL Server client/server database management system version 7.0. This series will help students prepare for the Microsoft Certified Professional exam 70-019, Designing and Implementing Data Warehouses with Microsoft SQL Server 7.0. It is recommended that participants take NETg course series 72470-72474, Microsoft SQL Server 7.0: System Administration and course series 72475-72479, Microsoft SQL Server 7.0: Implementing a Database or have equivalent knowledge and have completed the first course in this series.

Prerequisites

(Currently no course prerequisite information)

Objective

• Identify the design characteristics of OLAP systems and Aggregate schemas.
• Identify the data warehouse models.
• Identify the Data Transformation Services (DTS) components and the components of the DTS object model.
• Identify the methods of distributing data.
• Identify the replication types and techniques.

Topics Include

Unit 1: OLAP Systems

• Identify the reasons why OLTP systems are inadequate for decision support.
• Identify the OLAP Services features.
• Identify the features of the OLAP Services architecture.
• Match the activities in building an OLAP Data Warehouse with the tasks involved in the activities.
• Identify the advantages of aggregation.
• Select the most appropriate table to answer a given user query.
• Identify the design issues that are relevant for the design of cubes, base tables, fact tables and dimension tables.

Unit 2: The Data Warehousing Process

• Identify the features of an enterprise data warehouse.
• Identify the situations in which you will use a data mart.
• Identify the features of an operational data store.
• Identify the features of a combination model.
• Match the logical components in a data warehouse framework with their function.
• Identify the design considerations for a data warehouse.
• Identify the activities in designing a data warehouse.
• Identify the factors that determine the frequency with which a data warehouse is refreshed.
• Identify the factors that determine the frequency with which a data warehouse is archived.
• Identify the factors that need to be considered to filter data in a data warehouse.
• Identify the features of the architectural approaches for data warehouse design.
• Identify the solution that meets the design requirements of a data warehouse for a given scenario.
• Identify the factors that determine the feasibility of implementing data warehouse architecture.
• Identify the reason based on which you will decide to use relational or multidimensional storage for a data warehouse.
• Match the approaches to data warehouse storage design with their descriptions.
• Identify the features of the elements in a star schema design.
• Identify the features of the dimensional models in dimensional modeling.
• Match each step in the dimensional schema design with the decision taken in that step.

Unit 3: Data Transformation Services (DTS)

• Identify the goals of Data Transformation Services (DTS).
• Identify the functions of a DTS package.
• Match DTS Designer objects with their functions.
• Identify the functions of DTS COM Interface.
• Identify the functions of a data pump.
• Match the objects in a DTS package with their functions.
• Identify the elements in the DTS object model.
• Identify the guidelines for moving data into a data warehouse.
• Match DTS package locations with their features.
• Identify the methods to execute a DTS package.
• Identify the advanced techniques available in DTS.
• Identify the best practices when using DTS.

Unit 4: Replication in SQL Server 7.0

• Identify the benefits of a distributed system.
• Identify the advantages of the two-phase commit method.
• Identify the advantages of the latent guaranteed consistency method.
• Identify the advantages of the synchronized consistency method.
• Match the replication types with the situations in which they are used.
• Select the replication technique that is beneficial for a given data warehousing architecture.
• Complete the spectrum that shows the best balance of site autonomy and transaction consistency.
• Identify the best practices while using replication.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently, only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media