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.
Target Audience:
Pre-requisites:
Before attending this course, students must have:
Familiarity with Microsoft SQL Server version 7.0 or Microsoft SQL Server 2000.
Knowledge of Transact-SQL usage in the development of online transaction processing (OLTP) systems.
At Course Completion:
After completing this 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.
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.
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:
Implementing Asynchronous Workflows
Implementing Package Transactions
Creating a Package Loop
At the end of this module, you will be able to manage package properties. This includes:
Asynchronously executing packages.
Creating package transactions.
Understanding how to implement a loop.
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.
