Course Syllabus
Designing and Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services

Course 2092 • Five days • Instructor-led


This course provides students with the knowledge and skills necessary to design a data warehouse and to populate data marts by using Data Transformation Services (DTS) in Microsoft® SQL Server™ 2000.

At Course Completion

At the end of the course, students will be able to:

§          Understand data warehousing concepts and applications.

§          Build relational data marts by using star schemas.

§          Develop a data warehouse data load strategy.

§          Use the DTS Import/Export Wizard.

§          Understand DTS package components.

§          Use DTS to copy and manage data.

§          Design insert based transformation by using the Transform Data Task.

§          Implement a Data Driven Query solution.

§          Execute packages and design package security.

§          Understand the basics of the DTS Object Model.

§          Modify DTS package properties.

§          Implement DTS in specific real-world data load scenarios.

§          Apply tuning techniques to DTS data loads.

Microsoft Certified Professional Exams

There is no MCP exam associated with this course.

Prerequisites

Before attending this course, students must have:

·    Familiarity with Microsoft SQL Server version 7.0 or Microsoft SQL Server 2000.

·    Course 832, System Administration for Microsoft SQL Server 7.0, and Course 833, Implementing a Database on Microsoft SQL Server 7.0, or the equivalent Microsoft SQL Server 2000 courses.

·    Knowledge of Transact-SQL usage in the development of online transaction processing (OLTP) systems.

·    Basic understanding of programming principles (especially experience with a scripting language such as Microsoft Visual Basic® Scripting Edition or Microsoft JScript® development software).

·    Understanding of basic database design, administration, and implementation concepts.

The course materials, lectures, and lab exercises are in English. To benefit fully from our instruction, students need an understanding of the English language and completion of the prerequisites.

Course Materials and Software

The course materials are yours to keep.

You will be provided with the following software for use in the classroom:

·    Microsoft SQL Server 2000

Module 1: Using DTS in a Data Warehouse

The following topics are covered in this module:

·    Defining Data Transformation Services

·    Identifying DTS Applications

·    Defining the Data Warehouse System

·    Applying DTS to the Data Warehouse

At the end of this module, you will be able to explain how Data Transformation Services is used. This includes:  

·    Describing the functionality of DTS.

·    Listing applications of DTS.

·    Describing components of a data warehouse system.

·    Describing how you can use DTS in a data warehouse.

Module 2: Defining Data Warehouse Structures

The following topics are covered in this module:

·    Defining the Polaris Data Warehouse

·    Identifying Source and Destination Structures

·    Defining Dimension Tables

·    Defining Fact Tables

·    Implementing the Star Schema

At the end of this module, you will be able to describe the data structures and storage of the data warehouse used in the class. This includes:  

·    Describing the polaris data warehouse initiative.

·    Describing a data warehouse star schema.

·    Defining dimension tables.

·    Identifying components of fact tables.

·    Describing how to implement the star schema.

Module 3: Populating Data Warehouse Structures

The following topics are covered in this module:

·    Reviewing the Star Schema Data Load

·    Defining the Dimension Data Load

·    Defining the Fact Table Data Load

·    Implementing Staging Tables

·    Applying Data Transformation Services

·    Using DTS to Populate the Sales Star

At the end of this module, you will be able to populate tables and use staging tables. This includes:  

·    Describing how to implement the star schema.

·    Populating dimension tables.

·    Populating fact tables.

·    Describing how to use staging tables.

·    Defining DTS packages.

·    Identifying the components of DTS packages.

Module 4: Using the DTS Import/Export Wizard

The following topics are covered in this module:

·    Defining the Import/Export Wizard

·    Copying Objects Between Heterogeneous Databases

·    Copying Tables from Microsoft Access 2000 to SQL Server

·    Creating a Prototype Package

·    Loading the Employee_dim Dimension

·    Loading the Product_dim Dimension

At the end of this module, you will be able to perform simple data transfers. This includes:  

·    Describing how the DTS Import/Export Wizard can apply to various data load scenarios.

·    Using the DTS Import/Export Wizard to copy tables and views.

·    Copying tables from Access to SQL Server 2000 by using the DTS Import/Export Wizard.

·    Using the DTS Import/Export Wizard to create a prototype DTS package.

·    Loading the employee_dim dimension of the polaris data warehouse by using the DTS Import/Export Wizard.

·    Loading the product_dim dimension of the polaris data warehouse by using the DTS Import/Export Wizard.

Module 5: Understanding DTS Package Elements

The following topics are covered in this module:

·    Learning Package Components

·    Using DTS Package Designer

·    Defining Package Connections

·    Defining Package Tasks

·    Defining Package Steps

·    Storing and Executing Packages

·    Adding a Parallel Data Load to Product_dim

At the end of this module, you will be able to set up and configure package connections, tasks, and steps. This includes:  

·    Describing package components.

·    Starting DTS Package Designer and designing a package by using DTS Package Designer.

·    Setting up connections for data sources and destinations.

·    Setting up package tasks.

·    Defining package workflow by using precedence constraints.

·    Designing package storage and executing a package.

·    Creating a parallel data load.

Module 6: Copying and Managing Data

The following topics are covered in this module:

·    Identifying DTS Tasks That Copy and Manage Data

·    Using the Bulk Insert Task

·    Loading Staging Tables

·    Using the Execute SQL Task

·    Using the Copy SQL Server Objects Task

At the end of this module, you will be able to use the Bulk Insert task, the Execute SQL task, and the Copy SQL Server Objects task. This includes:  

·    Describing the group of tasks that copy and manage data.

·    Using the Bulk Insert Task to load files into SQL Server.

·    Using format files with the Bulk Insert task.

·    Using the Execute SQL task to execute parameterized SQL statements.

·    Copying objects by using the Copy SQL Server Objects task. 

Module 7: Performing Data Transformations

The following topics are covered in this module:

·    Performing Transformations in DTS

·    Defining the Transform Data Task

·    Setting Up the Source and Destination

·    Creating Transformations

·    Configuring Error Handling

·    Optimizing for SQL Server Destinations

At the end of this module, you will be able to use the Transform Data task. This includes:  

·    Describing how the Data Transformation Services data pump processes data.

·    Defining the functionality of the Transform Data task.

·    Setting up the source and destination for the Transform Data task.

·    Creating data transformations.

·    Setting up error handling.

·    Configuring data load settings for SQL Server destinations.

Module 8: Extending Transformations

The following topics are covered in this module:

·    Building Microsoft ActiveX® Script Transformations

·    Creating Advanced Transformations

·    Using Lookup Queries

·    Implementing SQL Solutions

·    Using the Multiphase Data Pump

At the end of this module, you will be able to design extended transformations by using the Transform Data task. This includes:  

·    Designing ActiveX script transformations for the Transform Data task.

·    Using the DTSTransformStat constants in advanced transformations.

·    Defining how to incorporate lookups in ActiveX script transformations.

·    Implementing SQL solutions with the Transform Data task

·    Describing the functionality of the multiphase data pump.

Module 9: Implementing Data Driven Query Solutions

The following topics are covered in this module:

·    Using the Data Driven Query Task

·    Building a Data Driven Query Task Solution

·    Maintaining Slowly Changing Dimensions

·    Refreshing the New_product_dim Table

·    Learning Best Practices for the DDQ

At the end of this module, you will be able to use and create data driven queries. This includes:  

·    Understanding when and how to use the Data Driven Query task.

·    Building a Data Driven Query task solution.

·    Conditionally processing data by using the Data Driven Query task.

·    Implementing a Type 1 slowly changing dimension solution.

·    Listing best practices for designing Data Driven Query task solutions.

Module 10: Storing DTS Packages and Metadata

The following topics are covered in this module:

·    Understanding Package Versions

·    Storing DTS Packages

·    Securing DTS Packages

·    Storing Metadata

·    Tracking Data Lineage

At the end of this module, you will be able to store DTS packages, implement package passwords, and track package metadata and data lineage. This includes:  

·    Describing how DTS manages package versions.

·    Listing package storage modes.

·    Securing DTS packages by using package passwords.

·    Storing database and package metadata in Meta Data Services.

·    Implementing data lineage for DTS data loads.

Module 11: Executing Packages

The following topics are covered in this module:

·    Defining Package Executions

·    Executing Packages Interactively

·    Using Package Execution Utilities

·    Creating Package Execution Logs

·    Executing Moduleal Packages

·    Scheduling Packages

At the end of this module, you will be able to execute packages in several ways. This includes:  

·    Describing package execution behavior.

·    Executing packages interactively.

·    Usign package execution utilities.

·    Creating package execution logs.

·    Using the Execute Package task.

·    Describing how to automate and schedule packages.

Module 12: Managing Package Properties

The following topics are covered in this module:

·                            Reviewing DTS Package Elements

·                            Understanding Disconnected Edit

·                            Using the Dynamic Properties Task

·                            Managing Connection Properties

At the end of this module, you will be able to manage package properties. This includes:  

·    Describing package elements.

·    Viewing and changing package properties by using the Dynamic Properties task.

·    Modifying package properties by using the Dynamic Properties task.

·    Listing best practices for managing connection properties.

Module 13: Building Advanced Workflows

The following topics are covered in this module:

·    Implementing Asynchronous Workflows

·    Implementing Package Transactions

·    Creating a Package Loop

At the end of this module, you will be able to create complex package workflows. This includes:  

·    Asynchronously executing packages.

·    Creating package transactions.

·    Understanding how to implement a loop.

Module 14: Applying Best Practices

The following topics are covered in this module:

·    Defining the Data Load Scenario

·    Developing Packages

·    Choosing Tasks

·    Designing Transformations

·    Defining Workflows

·    Storing and Executing Packages

·    Managing Packages

At the end of this module, you will be able to apply best practices to designing and implementing packages in DTS. This includes:  

·    Describing how to define a data load strategy for creating packages.

·    Explaining the criteria used to choose a package design method.

·    Describing which tasks are best for different data load scenarios.

·    Listing best practices for implementing data transformations.

·    Describing best practices for designing workflows.

·    Listing best practices for storing and executing packages.

·    Describing how to best manage package elements.

Module 15: Case Study – Populating the Shipments Star

The following topics are covered in this module:

·    Defining the Shipments Star

·    Populating the Shipments Star

·    Migrating the Shipments Star

At the end of this module, you will be able to demonstrate the ability to apply DTS design and implementation concepts. This includes:  

·    Defining the components of the shipments star.

·    Loading the dimensions and the fact table in the shipments star by creating and executing DTS packages.

·    Managing and maintaining the shipments star packages.

© 2001 Microsoft Corporation. All rights reserved.

© 2001 OLAP Train, LLC. All Rights Reserved. Developed by OLAP Train LLC, for Microsoft Corporation and distributed under license.

Some elements of this course syllabus are subject to change. This syllabus is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, ActiveX, Jscript, and Visual Basic are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.

  700