Skip Navigation Links

Kursbeschreibung: MOC 2092A

Populating a Data Warehouse with SQL Server 2000 Data Transformation Services (5 Tage)

  • 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.

5 Tage


Database developers, database administrators, and database architects who design and implement data warehouse decision support systems.

  • Familiarity with SQL Server 2000.
  • MOC 2072 (Verwalten einer SQL Server 2000-Datenbank) und MOC 2073 (Programmieren einer SQL Server 2000-Datenbank)
  • 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 Visual Basic Scripting Edition or JScript development software).
  • Understanding of basic database design, administration, and implementation concepts.

Module 1: Using DTS in a Data Warehouse

  • Defining Data Transformation Services
  • Identifying DTS Applications
  • Defining the Data Warehouse System
  • Applying DTS to the Data Warehouse

Module 2: Defining Data Warehouse Structures

  • 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 Structures

  • 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 Wizard

  • Defining the Import/Export Wizard
  • Copying Objects Between Heterogeneous Databases
  • Copying Tables from Access 2000 to SQL Server
  • Creating a Prototype Package
  • Loading the Employee_dim Dimension
  • Loading the Product_dim Dimension

Module 5: Understanding DTS Package Elements

  • 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 Data

  • 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 Transformations

  • 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 Transformations

  • Building ActiveX Script Transformations
  • Creating Advanced Transformations
  • Using Lookup Queries
  • Implementing SQL Solutions
  • Using the Multiphase Data Pump

Module 9: Implementing Data Driven Query Solutions

  • 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 Metadata

  • Understanding Package Versions
  • Storing DTS Packages
  • Securing DTS Packages
  • Storing Metadata
  • Tracking Data Lineage

Module 11: Executing Packages

  • Defining Package Executions
  • Executing Packages Interactively
  • Using Package Execution Utilities
  • Creating Package Execution Logs
  • Executing Moduleal Packages
  • Scheduling Packages

Module 12: Managing Package Properties

  • Reviewing DTS Package Elements
  • Understanding Disconnected Edit
  • Using the Dynamic Properties Task
  • Managing Connection Properties

Module 13: Building Advanced Workflows

  • Implementing Asynchronous Workflows
  • Implementing Package Transactions
  • Creating a Package Loop

Module 14: Applying Best Practices

  • 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 Star

  • Defining the Shipments Star
  • Populating the Shipments Star
  • Migrating the Shipments Star
Die Schulungsunterlage ist nur auf Englisch verfügbar!
Home | Kontakt | Impressum
©2006 i.d. datenverarbeitung GmbH | Version: 0.0.1