Course 2092A:

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

Length:5 Days
Published:February 09, 2001
Language(s):English
Audience(s):IT Professionals
Level:300
Technology:Microsoft SQL Server 2000
Type:Course
Delivery Method:Instructor-led (classroom)
About this Course
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.
Course OutlineModule 1: Using DTS in a Data WarehouseLessons
  • Defining Data Transformation Services
  • Identifying DTS Applications
  • Defining the Data Warehouse System
  • Applying DTS to the Data Warehouse
Module 2: Defining Data Warehouse StructuresLessons
  • Defining the Polaris Data Warehouse
  • Identifying Source and Destination Structures
  • Defining Dimension Tables
  • Defining Fact Tables
  • Implementing the Star Schema
Module 3: Populating Data Warehouse StructuresLessons
  • 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
Module 4: Using the DTS Import/Export WizardLessons
  • 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
Module 5: Understanding DTS Package ElementsLessons
  • 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
Module 6: Copying and Managing DataLessons
  • 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
Module 7: Performing Data TransformationsLessons
  • 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
Module 8: Extending TransformationsLessons
  • Building Microsoft ActiveX Script Transformations
  • Creating Advanced Transformations
  • Using Lookup Queries
  • Implementing SQL Solutions
  • Using the Multiphase Data Pump
Module 9: Implementing Data Driven Query SolutionsLessons
  • 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
Module 10: Storing DTS Packages and MetadataLessons
  • Understanding Package Versions
  • Storing DTS Packages
  • Securing DTS Packages
  • Storing Metadata
  • Tracking Data Lineage
Module 11: Executing PackagesLessons
  • Defining Package Executions
  • Executing Packages Interactively
  • Using Package Execution Utilities
  • Creating Package Execution Logs
  • Executing Moduleal Packages
  • Scheduling Packages
Module 12: Managing Package PropertiesLessons
  • Reviewing DTS Package Elements
  • Understanding Disconnected Edit
  • Using the Dynamic Properties Task
  • Managing Connection Properties
Module 13: Building Advanced WorkflowsLessons
  • Implementing Asynchronous Workflows
  • Implementing Package Transactions
  • Creating a Package Loop
Module 14: Applying Best PracticesLessons
  • Defining the Data Load Scenario
  • Developing Packages
  • Choosing Tasks
  • Designing Transformations
  • Defining Workflows
  • Storing and Executing Packages
  • Managing Packages
Module 15: Case Study - Populating the Shipments StarLessons
  • Defining the Shipments Star
  • Populating the Shipments Star
  • Migrating the Shipments Star
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.
Have Questions? For advice about training and certification, connect with peers: For questions about a specific certification, chat with a Microsoft Certified Professional (MCP): To find out about recommended blogs, Web sites, and upcoming Live Meetings on popular topics, visit our community site:
Course index

Find Training Near You

Location:
Eg: Seattle, WA or Paris, France

Related Services