Carga horária – 40 horas

Descrição - Este curso forncece aos alunos o conhecimento e habilidades necesárias para fazer o design de data warehouse e para popular data marts usando Data Transformation Services(DTS) no MS SQl Server 2000.

Objetivo: Conpreender os conceitos e aplicações de data warehouse, Criar data marts relacionados usando star schema, Desenvolver uma estratégia para carregar dados em um data warehouse. Usar o DTS Import/Export Wizard, Compreender o pacote de componentes DTS, Usar o DTS para copiar e gerenciar dados, Implementar uma solução Data Driven, Executar pacotes e fazer o design de segurança nos pacotes, Compreender o básico do DTS Object Model, Modificar as propriedades do pacote DTS, Implementar DTS em um cenário de carga de dados no mundo real, Aplicar técnicas para o DTS realizar a carga de dados.

•Pré-requisito
Antes de realizar este curso, os alunos precisam satisfazer os seguintes pré-requisitos:
Familiaridade com o Microsoft SQL Server version 7.0 ou Microsoft SQL Server 2000.
Curso 832, System Administration for Microsoft SQL Server 7.0, e o curso 833, Implementing a Database on Microsoft SQL Server 7.0, ou conhecimento equivalente.
Conhecer o uso de T_SQL Para desenvolvimneto de OLTP(online transaction processing )
Compreender os princípios básicos de programação(especialmente experiência com Vbscript)
Compreender o design básico de banco de dados, administração, e conceitos de implementação.

Módulos

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 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 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 Microsoft 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