REVIEW OF BI TERMS & CONCEPTS

• The data warehousing (DW) environment
• The DW computing context
• What is a data warehouse?
• What is a data mart?
• What is Business Intelligence (BI)?
• How do OLTP & OLAP differ?
• What is data mining?
• Operational vs. historical data
• Dimensional modelling
• Some dimensional modelling characteristics
• What is a star schema?
• What is a snowflake schema?
• What is metadata?

SQL SERVER INTEGRATION SERVICES (SSIS) OVERVIEW

• Business Intelligence Suite
• SSIS Architecture
• Scope
• Services
• Run-time engine
• Data-flow engine
• Packages
• Event handlers
• Logging
• Variables
• SSIS Designer
• Command prompt utilities

SSIS TOOLS

• Business Intelligence Studio
• SQL Server Management Studio
• Wizards (e.g., import/export, migration, deployment, etc.)
• Data Sources
• Data Source Views

PACKAGE CREATION USING SSIS DESIGNER

• Control flows
• Data flows
• Data sources
• Event handlers
• Debugging packages
• Viewing package objects
• Saving packages

TASKS, CONTAINERS & PRECEDENCE CONSTRAINTS

• Control flow architecture
• SSIS tasks
• Containers
• Sequence container
• For Loop container
• Foreach Loop container
• Precedence constraints
• Task grouping
• Annotation
• Variables
• Setting task properties via the Properties window
• Setting task properties via the task editor

EXTRACT, TRANSFORM, LOAD (ETL) TERMS & CONCEPTS

• ETL overview
• The ETL process
• ETL staging considerations
• Source-to-destination mapping considerations
• Source system monitoring document
• Normalised (OLTP) vs. dimensional model
• Planning source-to-ETL processing
• How to handle 'inferred members' (late arriving dimensions)
• Fact table properties & overview
• Slowly Changing Dimensions (SCD)
• Surrogate keys

DATA FLOWS

• Data flow tasks
• Data flow components
• Connection managers
• Data sources
• Destinations
• SSIS transformations

SSIS TRANSFORMATION EXAMPLES

• Aggregation
• Character Map
• Conditional Split
• Data Conversion
• Derived Column
• Fuzzy Lookup/Fuzzy Grouping
• Merge and Merge Join
• Multicast
• Sort
• Script Component
• Slowly Changing Dimension

ERROR & EVENT HANDLING

• Types
• Audit
• Precedence constraints
• Script debugging
• Logging

SSIS BUSINESS SCENARIOS – WORKSHOPS

• ETL requirements
• Data source and destination
• SSIS project
• Connection managers
• Data flows
• Lookup transformations
• Foreach Loop container
• Flat file connection manager
• Package configurations
• Directory property configurations
• Logging
• Error and exception handling

SSIS SCRIPTING

• SSIS scripting options
• Script task vs. Script component
• Expressions and scripting
• ActiveX Script task
• Script task vs. Custom managed tasks
• Visual Studio for Applications (VSA)
• Debugging scripts

DEPLOYMENT

• Deployment facilities
• Deployment controls
• How to organise a package for deployment
• How to organise configuration files and variables
• How to create destination computer bundle
• How to install the deployment bundle on the destination computer
• Package execution options
• Package security and roles considerations

DTS TO SSIS MIGRATION ISSUES

• Why migrate?
• Some DTS and SSIS differences
• DTS Data Pump concepts
• SSIS Control Flow/Data Flow concepts
• Summary of important migration issues
• Migrating DTS Packages
• Migration tools
• Migration issues
• Task migration
• Migrating precedence constraints and variables
• Migrating connections
• Migrating passwords and scripts

PERFORMANCE CONSIDERATIONS & BEST PRACTICES

• Naming conventions
• Queries
• Controlled sorts
• Optimised aggregation
• Data loads