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?

REVIEW OF SSIS BASICS

• Architecture
• Tools
• Wizards
• Packages
• Control Flows
• Data Flows
• SSIS in operational/data warehousing environments
• SSIS data types
• SSIS transformation types
• Containers
• Precedence constraints
• Variables
• Expressions

REVIEW OF PACKAGE OBJECTS

• Tasks
• Connection Managers
• Log providers
• Containers
• Precedence constraints

SSIS DATA TYPES & OPTIMISATION

• Types
• How to alter and map
• Parsing
• Optimisation

SSIS VARIABLES

• Definition
• Properties
• Scope
• Type
• Namespaces
• System
• User
• Execution behaviour

SSIS EXPRESSIONS

• Definition
• Elements
• Property expressions
• Variable expressions
• Precedence constraint expressions
• For Loop expressions

CONTAINERS

• Concepts
• Types (e.g., Foreach Loop)
• Scoping
• Properties

ADVANCED CONTROL FLOW TASKS

• File System
• Send Mail
• Message Queue
• WMI
• Execute Package
• Execute Process
• Bulk Insert
• Analysis Services
• Data Mining
• Execute SQL Agent Job
• Update Statistics
• Script Task

ADVANCED DATA FLOW COMPONENTS

• Source adapters (e.g., Raw Files)
• Destination adapters (e.g., Data Reader)
• Conditional Split
• Multicast
• Fuzzy Lookup/Fuzzy Grouping
• Script component
• OLE DB command
• Union All
• Sort
• Merge/Merge Join
• Lookup
• Pivot/Unpivot (Excel worksheet)
• Aggregate
• Slowly Changing Dimension

OPTIMISING DATA FLOW TRANSFORMATIONS

• Buffer architecture
• Pipeline considerations
• Synchronous vs. asynchronous
• Blocking vs. semi-blocking vs. non-blocking
• Execution trees interpretation and usage

CHECKPOINT RESTART (WITH/WITHOUT TRANSACTIONS)

• Definition
• When to consider
• Checkpoints – how to implement and manage
• Checkpoint files
• Single Container and checkpoints
• Multiple Containers and checkpoints
• Single vs. multiple transactions
• How to configure
• How to set properties
• Distributive transaction considerations
• Scoping per container
• Isolation levels
• Snapshot isolation

ADVANCED DEPLOYMENT CONSIDERATIONS

• Package configuration
• What does the Deployment Wizard do?
• When should the deployment utility be used vs. import/Export, XCopy, etc?
• How to override configurations
• How to deploy and account for security

COMPREHENSIVE ETL DESIGN & DEVELOPMENT WORKSHOP

• Top-level planning
• Source-to-destination mapping
• Data profiling
• Load frequency
• Historical vs. incremental loads
• Extraction design
• Staging area
• Recovery considerations
• Dimension table processing
• Slowly Changing Dimensions (SCD)
• Late arriving (inferred) dimensions
• Fact table processing
• Extracting fact updates and deletes
• Transforming dimension/fact data
• Fact data aggregation
• Confirmed dimensions/facts
• Surrogate key maintenance
• Fact table load
• Periodic vs. accumulating snapshot fact considerations
• Audit considerations
• Parent package/child package considerations
• Event handling
• Unit testing

SSIS PERFORMANCE & TUNING

• How to optimise sources
• How to optimise logging
• How to optimise data flows
• How to optimise error handling
• How to optimise insert performance
• Useful naming conventions