|
Click on a date to register
|
 |
Live@DL |
SQL-445 -
4 days -
Instructor-led (classroom)
Enterprise ETL with SQL Server 2005 and 2008 Integration Services
Price: 2,795
Table of Contents
Introduction
Audience
At Course Completion
Prerequisites
Microsoft Certified Professional
Exams
Student Materials
Course Outline
Introduction - 4 Day Course
During this 4-day instructor led training, you will learn the ins and outs of SSIS and how to apply its enterprise functionality to data warehouse ETL systems. At the completion of this course, you will understand how to design, develop, deploy, and operate SSIS solutions from source systems extractions, to data integration and transformation, to business intelligence system loading including how to processing SQL Server Analysis Services (SSAS) cubes.
This course will demonstrate a fully working ETL solution, including extensive demos, sample projects that will not only teach how to use the new technology, but also illustrate business intelligence ETL and data warehouse best practices.
Technologies Covered
Primary focus: SQL Server 2008 Integration Services
Integration with: SQL Server 2008 Database Engine
Integration with: SQL Server 2008 Analysis Services
Return to Top
Audience
While this course is targeted at database professionals with some experience
with data warehouse solutions, this workshop requires no prior experience with SQL
Server 2000 DTS
Return to Top
At Course Completion
Upon completion of this course, the student will be able to:
- Learn SSIS Features and Functionality: the initial seminar modules will dive deep into the core feature set of SSIS to lay the foundation for leveraging the tool for data processing requirements.
- Understand and Apply ETL Concepts in SSIS: building on the SSIS knowledge, the focus will turn to understanding relational data warehousing principles� applying efficient SSIS processing techniques for dimensions and fact table ETL. Additionally, the seminar will consider processing architectures for loading SSAS dimension and cubes.
- Deploy and Administer SSIS in Production: the final modules cover getting ETL solutions over the goal line and maintaining sanity once there�focusing on deployment and ongoing administration.
Return to Top
Prerequisites
This course is targeted at database professionals and developers with some experience in business intelligence solutions and SQL Server. This workshop requires no prior experience with SQL Server DTS or SSIS.
Return to Top
Microsoft Certified Professional Exams
No MCP exam currently exists for this course.
Return to Top
Student Materials
The student kit includes a comprehensive workbook and other necessary materials
for this class.
Return to Top
Course Outline
Section A: SSIS Overview and Core Features
Module 01: SSIS Design Environment and Overview
The role of ETL in Business Intelligence, SQL Server 2008 tools, SSIS ETL features, ETL Architecture, Visual Studio Design Environment, Creating Solutions and Projects Working with SSIS Projects, Properties Windows and viewers, Data Sources, Data Source Views, Connections
LAB 01: Creating an SSIS Project and Packages
Module 02: SSIS Control Flow Objects and Features
Management Tasks, Standard Tasks, Execute SQL Task and parameters, Script Task, Sequence Container, Looping Containers, Standard and Expression based Constraints
LAB 02: Workflow in the Control Flow
Module 03: Applying Data Flow Transformations and Adapters
Data Flow source and destination adapters, Data expressions and conversion transformations, Data association and correlation transformations (IE: Lookup, Merge), Filtering, Data Paths and Data Viewers
LAB 03: Working with Data in the Data Flow
Section B: Data Warehousing ETL Processing in SSIS
Module 04-Extr: Data Extraction Methods and Destination Optimizations
Heterogeneous sources, Data quality, Full and incremental source extraction, Using Data Source Views effectively, Dynamic source queries, Flat file sources, Source lineage tracking
LAB 04-Extr � Putting It Together: Control Flow, Data Flow and Data Extraction
Module 04-Trsfm: Advanced Data Preparation and Transformations
Data profiling, data cleansing (Fuzzy transformations), Script Component Sources and Transformations
LAB 04-Trsfm � Advanced Data Flow Transformations
Module 04-Dim: Dimension ETL with SSIS
Slowly Changing Dimension types and theory, Using the SCD wizard, Drilling into the SCD transformations, Customizing the SCD, Alternate SCD approaches, Inferred Members, Handling Snowflake and Parent-Child Hierarchies
LAB 04-Dim: Dimension Table ETL
Module 04-Fact: Fact Table ETL with SSIS
Fact types and theory, Aspects of the fact table processing, Dimension lookups with the Lookup transformation, Missing Dimension Members, Measures and Calculations, Handling fact inserts and updates, Changing data grain, Processing Analysis Services Measure Group Partitions
LAB 04-Fact: Fact Table ETL
Module 04-SSAS: Processing SSAS Objects in SSIS
Analysis Services processing types, Analysis Services Processing Task and Execute DDL Task, Dynamically creating and processing partitions, ASCMD command line and AMO API usage
LAB 04-SSAS: Analysis Services Processing
Section C: Advanced SSIS Functionality
Module 05-Conf: Applying Dynamic Configurations and Built-in Logging
Planning your configuration environment, SSIS Configuration types, dynamic Property Expressions, SSIS Logging, Extending SSIS auditing with Event Handlers
LAB 05-Conf: Logging and SSIS Configurations
Module 05-Tran: Understanding SSIS Transaction Management and Restartability
Built-in checkpoints, How and when to use checkpoints, Managing RDBMS transactions, Rollback considerations, Integrating Checkpoints with Transactions
Module 06: Coordinating Team Development and Deployment, Modular Package Design
Modular package design, Team development environment, Deploying Packages, SSIS Deployment Wizard
LAB 06: Creating Parent Packages and Package Deployment
Module 07: Package Administration: Security and Execution
Package Execution Options, DTExec command line execution, SSIS Management features, SQL Server storage and security roles, Package encryption
LAB 07: Securing and Executing Packages
Module 08: Troubleshooting and Optimizing Packages
Identifying package bottlenecks, Understanding the SSIS data flow architecture, Data Flow transformation types, Execution Trees, Engine Threads, Monitoring Data Flow execution with pipeline logging, Debugging Task with Breakpoints, SSIS Performance Monitor counters and tracking
Section D: ETL Case Study and SSIS Frameworks
Module 09: ETL Case Study and Package Frameworks
Case study examples: package design and performance, Integration with SQL Server 2008, using Merge and Change Data Capture features, Partition Table management, Creating an SSIS Framework for Auditing Troubleshooting, and Reporting
Return to Top
|