Custom Search
Home > American IT > Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services
Provided by: American IT

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

Unfiled
Training Provided by American IT

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.


This is primarily ilt training
instructor led trainingThis class may be available at a classroom in Boulder, CO,
Contact American IT for more information
Duration:5 days
Training Presented in:English
Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services

Program Outline



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.

    Related Exams

    Student Pre-Requisites

    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 development software).

  • Understanding of basic database design administration and implementation concepts.

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


    Student Materials

    The course materials are yours to keep.

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

  • Microsoft SQL Server 2000


    Program Goals

    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.


    • About The Training Provider: American IT
    American IT - American IT represents a select group of IT Training partners located throughout the United States.  These partners are all, at a minimum,  Microsoft Certified Partners for Learning Solutions, however many of our partners have achieved Microsoft Gold Certified Partner status, distinguishing themselves as one of the premier IT solutions providers in the industry. Our commitment, to our...
    Click here for more information or to take this course
    Home > American IT > Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services
    Advertise With Us
    Are you a microsoft trainer?
    This page was last updated on sb5- 09/08/08 at 21:25:08 - 18:37:12