A Small Class Size is the Best Learning Environment*
Only F1 has no more than 6 Delegates in a Class
Everyone has Individual Attention from the Trainer

View Recent History

Course:(8911) Installation and Deployment in Microsoft Dynamics CRM 4.0 - Microsoft Training Courses in London and South West
Course:(F1024) What's New in Microsoft CRM 3.0 - Applications - Microsoft Training Courses in London and South West
Course:(2732) Planning Deploying and Managing an Enterprise Project Management Solution - Microsoft Training Courses in London and South West
Course:(2505) Deploying Microsoft Office XP - Microsoft Training Courses in London and South West
indexes/Certification_courses_index.aspx Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(2289) Deploying Business Desktops with Microsoft Windows XP and Microsoft Office 2003 - Microsoft Training Courses in London and South West
Course:(2830) Designing Security for Microsoft Networks - Microsoft Training Courses in London and South West
Course:(80054) Project Managing Microsoft Dynamics CRM Implementations with Microsoft Dynamics Sure Step - Microsoft Training Courses in London and South West
Course:(F1008) Developer Reskilling: Visual FoxPro to Visual Basic - Microsoft Training Courses in London and South West
Course:(F1002) Visual FoxPro 6.0 Enterprise - Microsoft Training Courses in London and South West
Course:(5060) Implementing Windows SharePoint Services 3.0 - Microsoft Training Courses in London and South West
Course:(8529) Microsoft CRM 3.0 Administration - Microsoft Training Courses in London and South West
Course:(2030) Creating Reporting Solutions using Microsoft SQL Server 2000 Reporting Services - Microsoft Training Courses in London and South West
Course:(2731) Deploying and Managing Microsoft Identity Integration Server (MIIS) 2003 - Microsoft Training Courses in London and South West
Course:(4009) Setting Up Projects for Success Using Microsoft Office Project 2003 - Microsoft Training Courses in London and South West
Course:(8522) Microsoft CRM 3.0 Marketing Automation - Microsoft Training Courses in London and South West
Course:(F1028) Introduction to Microsoft Dynamics CRM 4 for Small and Medium Businesses - Microsoft Training Courses in London and South West
Course:(F1023) What's New in Microsoft CRM 3.0 - Technical - Microsoft Training Courses in London and South West
Course:(1131) SQL Server 7.0 Upgrade and Migration - Microsoft Training Courses in London and South West
Course:(6294) Planning and Managing Windows 7 Desktop Deployments and Environments - Microsoft Training Courses in London and South West
Course:(2093) Implementing Business Logic with MDX in Microsoft SQL Server 2000 - Microsoft Training Courses in London and South West
Course:(2071) Querying Microsoft SQL Server 2000 with Transact-SQL. - Microsoft Training Courses in London and South West
Exchange Server 2010 Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(8526) Microsoft CRM 3.0 Installation and Configuration - Microsoft Training Courses in London and South West
Visual Studio 2008 Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(2840) Implementing Security for Applications - Microsoft Training Courses in London and South West
Course:(2074) Designing and Implementing OLAP Solutions with Microsoft SQL Server 2000 - Microsoft Training Courses in London and South West
Course:(4005) Beyond the Spreadsheet: Managing Financial Information Using Microsoft Office Access 2003 - Microsoft Training Courses in London and South West
Course:(5061) Implementing Microsoft Office SharePoint Server 2007 - Microsoft Training Courses in London and South West
Course:(80003) Workflow in Microsoft Dynamics CRM 4.0 - Microsoft Training Courses in London and South West
Visual Basic .NET Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(5056) First Look: Windows Vista for IT Professionals - Microsoft Training Courses in London and South West
Course:(80002) Administration in Microsoft Dynamics CRM 4.0 - Microsoft Training Courses in London and South West
Course:(2015) Enabling Business Processes using XML Smart Documents and Smart Solutions within the Microsoft Office System - Microsoft Training Courses in London and South West
Visual Studio .NET Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(8524) Microsoft CRM 3.0 Service Scheduling - Microsoft Training Courses in London and South West
Application Architecture Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(F1032) Fast Track to Microsoft Dynamics CRM 4.0 Certification: Become a CRM Power User in 3 Days Flat! - Microsoft Training Courses in London and South West
Course:(8912) Customisation and Configuration in Microsoft Dynamics CRM 4.0 - Microsoft Training Courses in London and South West
Course:(2014) Customizing Microsoft Office SharePoint Portal Server 2003 - Microsoft Training Courses in London and South West
Course:(4002) Forecasting and Trend Analysis Using Microsoft Office Excel 2003 - Microsoft Training Courses in London and South West
Course:(2151) Microsoft Windows 2000 Network and Operating System Essentials - Microsoft Training Courses in London and South West
Windows Server 2008 Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(8913) Applications in Microsoft Dynamics CRM 4.0 - Microsoft Training Courses in London and South West
Course:(2092) Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services - Microsoft Training Courses in London and South West
Course:(8521) Microsoft CRM 3.0 Sales Management - Microsoft Training Courses in London and South West
Course:(F1033) Fast Track to Microsoft Dynamics CRM 4.0 Certification: Become a CRM Developer in 5 Days Flat! - Microsoft Training Courses in London and South West
Course:(F1004) FoxPro 2.6 Intensive - Microsoft Training Courses in London and South West
Course:(4008) Building Better Microsoft Office Word 2003 Documents in Less Time - Microsoft Training Courses in London and South West
Course:(6064) Planning, Implementing, and Managing Windows Mobile 6 - Microsoft Training Courses in London and South West
Course:(8530) Microsoft CRM 3.0 Data Migration - Microsoft Training Courses in London and South West
Course:(6292) Installing and Configuring Windows 7 Client - Microsoft Training Courses in London and South West
Course:(6438) Implementing and Administering Windows SharePoint Services 3.0 in Windows Server 2008 - Microsoft Training Courses in London and South West
Course:(8525) Microsoft CRM 3.0 Customization - Microsoft Training Courses in London and South West
Course:(6289) First Look: Windows 7 Beta for IT Professionals - Clinic & Hands-on Lab - Microsoft Training Courses in London and South West
Course:(80141) Integrating Microsoft Dynamics CRM 4.0 and Microsoft Office SharePoint Server 2007 - Microsoft Training Courses in London and South West
Course:(6291) Updating Your Technology Knowledge of Microsoft Windows XP to Windows 7 Beta - Microsoft Training Courses in London and South West
Course:(6067) Developing Native Applications for Microsoft Windows Mobile 6 - Microsoft Training Courses in London and South West
Course:(2554) Designing, Planning, and Managing a Windows SharePoint Services and SharePoint Portal Server 2003 Infrastructure - Microsoft Training Courses in London and South West
Course:(8910) What's New in Microsoft Dynamics CRM 4.0 - Microsoft Training Courses in London and South West
Course:(2013) Optimizing Microsoft SQL Server 7.0 - Microsoft Training Courses in London and South West
Course:(2810) Fundamentals of Network Security - Microsoft Training Courses in London and South West
Course:(6066) Developing Managed Applications for Microsoft Windows Mobile 6 - Microsoft Training Courses in London and South West
Course:(1140) Microsoft SQL Server 7.0 Overview - Microsoft Training Courses in London and South West
Course:(F1030) Advanced Microsoft Dynamics CRM 4 for Small and Medium Businesses - Microsoft Training Courses in London and South West
Course:(4003) Summarizing Microsoft Office Excel 2003 Data to Make Better Business Decisions - Microsoft Training Courses in London and South West
Visual Studio 2010 Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(2439) Scripting Microsoft Windows Management Instrumentation - Microsoft Training Courses in London and South West
Course:(2088) Designing a Highly Available Web Infrastructure (IIS) - Microsoft Training Courses in London and South West
Course:(2264) Exam Preparation for the MCDST Certification - Microsoft Training Courses in London and South West
Course:(2381) Planning Collaborative Solutions With Microsoft Office XP Technologies - Microsoft Training Courses in London and South West
Course:(8879) Using the Microsoft Dynamics Sure Step Methodology for Microsoft Dynamics CRM - Microsoft Training Courses in London and South West
Course:(F1034) Fast Track to Microsoft Dynamics CRM 4.0 Certification: Become a CRM Expert in 7 Days Flat! - Microsoft Training Courses in London and South West
Office (inc SharePoint) Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(2657) Programming Microsoft Access 2002 - Microsoft Training Courses in London and South West
Course:(4006) Time and Task Management Using Microsoft Office Outlook 2003 - Microsoft Training Courses in London and South West
Course:(F1037) Customisation of xRM/CRM for Microsoft Partners and Customers - Microsoft Training Courses in London and South West
Application Centre Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(6331) Deploying and Managing Microsoft System Center Virtual Machine Manager - Microsoft Training Courses in London and South West
Visual Basic 6.0 Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(7197) Managing Enterprise Desktops Using the Microsoft Desktop Optimization Pack - Microsoft Training Courses in London and South West
Visual Studio 2005 Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(8969) Extending Microsoft Dynamics CRM 4.0 - Microsoft Training Courses in London and South West
Course:(2300) Developing Security-Enhanced Web Applications - Microsoft Training Courses in London and South West
Course:(2016) Bringing Data into Desktop Programs using the Microsoft Office 2003 Editions Research and Reference Technologies - Microsoft Training Courses in London and South West
Course:(F1029) Intermediate Microsoft Dynamics CRM 4 for Small and Medium Businesses - Microsoft Training Courses in London and South West
Course:(6290) First Look: Windows 7 Beta for IT Professionals - Hands-on Lab - Microsoft Training Courses in London and South West
Course:(3373) First Look: Getting Started with Windows SharePoint Services 3.0 Hands-On Lab - Microsoft Training Courses in London and South West
Mobile Internet Toolkit Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(4004) Managing Critical Business Information Using Microsoft Office Access 2003 - Microsoft Training Courses in London and South West
Course:(2052) Using Microsoft Visual Studio Tools for the Microsoft Office System - Microsoft Training Courses in London and South West
Course:(2433) Microsoft Visual Basic Scripting Edition and Microsoft Windows Script Host Essentials - Microsoft Training Courses in London and South West
Course:(8531) Extending Microsoft CRM 3.0 - Microsoft Training Courses in London and South West
Course:(F1001) Visual FoxPro 6.0 Intensive - Microsoft Training Courses in London and South West
Course:(80047) Using Microsoft Dynamics Sure Step - Microsoft Training Courses in London and South West
Course:(F1035) Fast Track to Microsoft Dynamics CRM 4.0 Certification: Become a CRM Guru in 10 Days Flat! - Microsoft Training Courses in London and South West
Course:(3087) First Look: Microsoft Windows Vista for Developers - Microsoft Training Courses in London and South West
Knowledge Management Training Course Index - F1Computing Microsoft Training Courses London South West UK
Microsoft Dynamics CRM Training Course Index - F1Computing Microsoft Training Courses London South West UK
Course:(2018) Creating and Customizing Team Folders - Microsoft Training Courses in London and South West
Visual Studio 6.0 Training Course Index - F1Computing Microsoft Training Courses London South West UK

Compare Recently Viewed Course Dates


Exclusive
SQL Server

Populating a Data Warehouse with Microsoft SQL Server 2000 Data Transformation Services
(Microsoft Training Course: 2092) - 5 days - £2640 exc VAT

We currently do not have any dates scheduled for this course.
Please contact us to arrange a closed course for your company or add your details to courses in waiting.
(Remember F1 has smaller classes)
Ask a Question ?Print 2 Page PDF


> 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
> 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.
top
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.
top
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
top
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.
top
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.
top
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.
top
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.
top
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.
top
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.
top
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.
top
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
top
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.
top
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.
top
> 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 [[{http://www.F1Comp.Co.UK/sql/833.html}course 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
> EXAMS
There are no exams directly associated with this course