Enterprise Extraction, Transformation & Loading with SQL Server 2008 Integration Services

Level
Total time
Logo Perpetual Solutions
Provider rating: starstarstarstarstar 10 Perpetual Solutions has an average rating of 10 (out of 1 reviews)

Need more information? Get more details on the site of the provider.

Starting dates and places

There are no known starting dates for this product.

Description

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.

Course Description:

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 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 c…

Read the complete description

Frequently asked questions

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.

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.

Course Description:

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

The target audience for this course is database warehouse or business intelligence solution architects, IT professionals, and business intelligence developers who want to learn the details of how to use the capabilities of SSIS to build ETL solutions.

On completion of this course, delegates will be able to:

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

This course includes the following modules:

SSIS Features and Concepts 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 1: Creating an SSIS Project and Packages

Applying Control Flow Containers, Tasks and Constraints

  • Management Tasks
  • Standard Tasks
  • Execute SQL Task and parameters
  • Script Tasks
  • Sequence Container
  • Looping Containers
  • Standard and Expression based Constraints
  • LAB 2: Workflow management in the Control Flow

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 3: Working with Data in the Data Flow

Data Extraction Strategies

  • Heterogeneous sources
  • Data quality
  • Full and incremental source extraction
  • Using Data Source Views effectively
  • Dynamic source queries
  • Flat file sources
  • Source lineage tracking
  • LAB 4.1-Extr Putting It Together, Control Flow, Data Flow and Data Extraction

Advanced Data Preparation and Transformations

  • Data profiling, data cleansing (Fuzzy transformations)
  • Script Component Sources and Transformations
  • LAB 4.2-Trnsf Data transformations and preparing

Dimension Table ETL

  • 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.3-Dim: Building Dimension table ETL

Fact Table ETL

  • 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.4-Fact: Building Fact Table ETL

Analysis Services Processing

  • 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.5-SSAS: Creating Analysis Services Processing Tasks

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.1-Conf: ETL auditing and SSIS configurations

Understanding SSIS Transaction Management and Restartability

  • Built-in checkpoints
  • How and when to use checkpoints
  • Managing RDBMS transactions
  • Rollback considerations
  • Integrating Checkpoints with Transactions

Coordinating Team Development and Deployment

  • Modular package design
  • Team development environment
  • Deploying Packages
  • SSIS Deployment Wizard
  • LAB 06: Creating Parent Package to coordinate execution

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

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
  • LAB 08: Troubleshooting package errors

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

For online live training advice please visit our Learning Advice Centre on our website. Be sure to follow us on Twitter to receive special course offers, news and updates!

There are no reviews yet.

Share your review

Do you have experience with this course? Submit your review and help other people make the right choice. As a thank you for your effort we will donate £1.- to Stichting Edukans.

There are no frequently asked questions yet. If you have any more questions or need help, contact our customer service.