2-Day (COURSE-BI-SSIS) or 3-Day (COURSE-BI-ADVANCEDSSIS)
Overview
Data processing often consumes the largest portion of effort in business intelligence solutions. Having the skills to design and develop efficient and manageable extraction, transformation, and loading (ETL) processes will yield tremendous savings in time and ongoing support in any business intelligence environment. This workshop focuses on the teaching IT professionals the best practices and skills required to successfully design, build, deploy, and operate ETL processes using SQL Server Integration Services (SSIS).
Description
During this 2-day course, you will learn to effectively use SSIS and how to apply its enterprise functionality to data warehouse ETL systems and general data integration requirements. At the completion this course, you will understand how to design, develop, deploy, and operate SSIS solutions from source systems extractions, to data integration and transformation, and to business intelligence system loading.
This course is also available in a 3-day version which includes more advanced material.
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.
Note: 2-day version is offered through our public schedule and 3-day version is offered only through private training.
For details on booking courses or seminars, please visit our booking page.
Audience
This course is intended for IT Professionals that are responsible for designing, developing, deploying, and operating business intelligence solutions.
While this course is based on SQL Server 2008, it is equally applicable to those working with SQL Server 2005. Differences in the versions will be highlighted.
Prerequisites
The target audience for this session is database warehouse, decision support, and business intelligence solution architects, IT professionals, and business intelligence developers who want to learn the details of how to use the new capabilities provided by SSIS to build ETL or other data integration type solution. 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 or SQL Server 2005 SSIS.
Course Objectives
This course objectives are:
- Learning SSIS Features and Functionality: The initial modules will dive deep into the core feature set of SSIS to lay the foundation for leveraging the tool for data processing requirements.
- Understanding and Applying 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.
- Deploying and Administering SSIS in Production: The course covers getting ETL solutions over the goal line via deployment and ongoing adminstration.
- If the optional 3rd day is taken, the course will consider more advanced topics.
Course Outline
Day 1
Course Introduction
Module IS201 Overview of SQL Server Integration Services
- Introduction to BI and dimensional modelling
- Integration Services features overview
- SQL Server 2008 BI tools
- LAB: Creating SSIS Projects and Packages
Module IS202 - Working with SSIS Control Flow
- Introducing and designing Control Flow
- Control flow features and concepts
- Containers
- Tasks and precedence constraints
- LAB: Working with SSIS Control Flow
Module IS203 - Working with SSIS Data Flow
- Introducing and designing Data Flow
- Working with data sources and destinations
- Transforming data
- Data viewers
- Data paths
- LAB: Working with SSIS Data Flow
Module IS301 - Optimizing the Data Flow
- Source adapter properties and best practices
- Optimizing data loads
- Handling errors
- LAB: Optimizing the Data Flow
Day 2
Module IS302 - Advanced Data Transformations
- Profiling Data
- Derived columns
- Lookup transformations
- Fuzzy transformations
- Script Components
- LAB: Advanced Data Flow Transformations
Module IS303 - Loading Dimension Tables
- Introduction to Dimensional Modelling
- Implementing slowly changing dimenions
- SCD Wizard
- Alternate SCD processing strategies
- LAB: Loading Dimension Tables
Module IS304 - Loading Fact Tables
- Fact table concepts
- Measures
- Fact table grain
- Dimension key lookups
- Implementing inferred members
- LAB: Loading Fact Tables
Module IS305 - SSIS Configuration, Logging, Deployment and Execution
- Package configurations
- Logging options
- Deployment manifests
- Deployment utility
- Scheduling SSIS package execution
- LAB: SSIS Configuration, Logging Deployment and Execution
Day 3 - Advanced SSIS Functionality (Only in 3-day version of the course)
Module IS306 - Automating Analysis Services Processing
- SSAS processing options
- Analysis Services Partitions
- SSAS Processing Tasks
- LAB: Automating Analysis Services Processing
Module IS307 - SSIS Logging, Auditing and Security
- SSIS logging options
- Event Handlers
- Auditing
- Security
- LAB: SSIS Logging, Auditing and Security
Module IS401 - Transaction Management and Restartability
- Transactions
- Database snapshots
- Checkpoints and restartability
- Practical design for restartability
Module IS402 - Advanced Package Design and Performance Optimization
- SSIS modular design
- Characteristics of well-designed packages
- Threading
- Memory utilization and buffering
- Optimizing throughput
- Troubleshooting