Populating a Data Warehouse With Microsoft SQL Server 2000 Data Transformation Services

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