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

Target Audience
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

Pre-Requisites
Before attending this course, students must have the following pre-requisites:Familiarity with Microsoft SQL Server version 7.0 or Microsoft SQL Server 2000.Course 832, System Administration for SQL Server 7.0, and 833, Implementing a Database on 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

Purpose
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

Course outline

1. Understanding the Microsoft Data Warehouse Architecture
2. Designing Data Warehouse Structures
3. Populating the Data Warehouse
4. Using the Data Transformation Services Wizard
5. Understanding DTS Package Elements
6. Copying and Managing Data
7. Performing Insert Based Transformations
8. Implementing Data Driven Query Solutions
9. DTS Package Execution and Security
10. DTS Programming
11. Managing DTS Package Properties
12. Real World Data Load Scenarios
13. Optimizing DTS Data Loads

Module 1:Understanding the Microsoft Data Warehouse Architecture

• Understand data warehousing concepts and applications.
• Describe characteristics, goals, and applications of a data warehouse.
• Explain the relationship between data marts and a data warehouse.
• Describe reasons for implementing relations and/or multidimensional data marts to meet decision support needs.
• Describe tools to manage a data warehouse implementation.

Module 2:Designing Data Warehouse Structures

• Build relational data marts by using star schemas.
• Describe a process for designing data warehouse systems.
• Create conceptual data models for star schemas.
• Design star schema dimensions and fact tables.
• Explain common design issues for dimensions and fact tables.
• Physically implement data models.

Module 3:Populating the Data Warehouse

• Develop a data warehouse load strategy.
• Design specificiations for data extract and load programs.
• Describe data load design requirements for data warehouses.
• Determine the usage of staging, development, and test environments.
• Identify the main SQL Server data movement tools.
• Explain the unique data transfer functionality of DTS

Module 4:Using the Data Transformation Services Wizard

• Use the DTS Import/Export Wizard.
• Access the DTS Designer Interface.
• Create a basic DTS package by using the DTS Import/Export Wizard.
• Set up source and destination data sources.
• Define source to destination column mappings.
• Execute a package.
• View package results.

Module 5:Understanding DTS Package Elements

• Understand DTS package components.
• Create a package by using the DTS Designer.
• Set up connections for data sources and destinations.
• Configure Universal Data Link Files.
• Set up package tasks.
• Define package workflow by using precedence constraints.
• Create global variables.
• Design package storage: SQL Server, Repository, File, and Visual Basic.
• List best practices for designing packages.

Module 6:Copying and Managing Data

• Use DTS to copy and manage data.
• Move objects by using the Transfer Objects Task.
• Describe when to use the Copy Database Wizard Tasks.
• Use the Bulk Insert Task to load files into SQL Server.
• Use the Execute SQL Task to issue queries, execute stored procedures, and to populate global variables.
• List best practices for applying the Execute SQL, Bulk Insert, and Transfer Objects tasks.

Module 7:Performing Insert Based Transformations

• Design insert based transformations by using the Transform Data Task.
• Describe how and when to use the Transform Data Task.
• Implement column data transformations.
• Use input parameters to the Transform Data Task.
• Implement SQL solutions with the Transform Data Task.
• Design ActiveX solutions with the Transform Data Task.
• Control the execution of the Data Pump by using DTSTransformStat constants.
• Configure commit batches for the Transform Data Task.
• List best practices for designing data transformations.

Module 8:Implementing Data Driven Query Solutions

• Implement a Data Driven Query solution.
• Determine when and how to use the Data Driven Query task.
• Perform inserts, updates, deletes, and user-defined queries by using the Data Driven Query Task.
• Use the Data Driven Query to perform incremental updates.
• List best practices for designing Data Driven Query solutions.

Module 9:DTS Package Execution and Security

• Execute packages and design package security.
• Execute packages interactively, programmatically, and via the command line.
• Describe how to automate and schedule packages.
• Use the Execute Package task to execute child packages within a master package.
• Set up package transactions.
• Pass global variables between packages.
• Enable package and step logging to the msdb database.
• Use the MSMQ task to set up asynchronous package processing.
• Configure package security.
• Determine how DTS security integrates with SQL Sever and SQL Server Agent Security.

Module 10:DTS Programming

• Understand the basics of the DTS object model.
• Describe DTS functionality accessible via the Object Model that goes beyond what is offered in the DTS Designer GUI.
• Identify key objects and properties of the DTS Object Model.
• List DTS package, tasks, and transform customization options.

Module 11:Managing DTS Package Properties

• Modify DTS package properties.
• Modify package properties by using the Dynamic Properties task.
• Use Disconnected Edit to perform off-line package modifications.
• Use the Execute SQL Task to populate global variables that store package property values.
• Use Global Variables as command line parameters to modify values for a single package execution.
• List best practices for modifying package properties

Module 12:Real World Data Load Scenarios

• Implement DTS in specific real world data load scenarios.
• Loop through all of the files in a source directory and load them into a destination table by using Microsoft ActiveX® scripts.
• Implement data lineage by using the Repository.
• Split one source record into many destination records by using ActiveX scripts.
• Implement error handling by using the multi-phase data pump.
• Perform in memory lookups by using global variables, ADO recordsets, and the multi-phase data pump.

Module 13:Optimizing DTS Data Loads

• Apply tuning techniques to DTS data loads.
• List DTS load testing practices.
• Describe how to scale DTS to move large datasets.
• Describe techniques for implementing indexes and primary key/foreign key constraints for small and large data loads.
• Review best practices for designing and managing packages.