Niche
SQL Server 2014 and 2012

Implementing a Data Warehouse with Microsoft SQL Server
(Microsoft Training Course: 20463) - 5 days - £1850 exc VAT

Save up to 50% of the cost of some courses: check our Certification Packages or buy F1 Training Vouchers
 Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov 
Birmingham Centre 04       24     
Edinburgh Centre          05   
Extended Classroom07        24 21 26 23  
Leeds (City Exchange) Centre      29       
London (Tabernacle St) Centre EC2       06      
London International House Centre E1W07        24 21 26 23  
Manchester (Portland St) Centre  08    06   28    


> Target Audience
This course describes how to implement a data warehouse platform to support a BI solution. Students will learn how to create a data warehouse with Microsoft SQL Server 2014, implement ETL with SQL Server Integration Services, and validate and cleanse data with SQL Server Data Quality Services and SQL Server Master Data Services. It is intended for database professionals who need to create and support a data warehousing solution.
> Course outline
  1. Introduction to Data Warehousing
  2. Planning Date Warehouse Structure
  3. Designing and Implementing a Data Warehouse
  4. Creating an ETL Solutions with SSIS
  5. Implementing Control Flow in an SSIS Package
  6. Debugging and Troubleshooting SSIS Packages
  7. Implementing a Data Extraction Solution
  8. Loading Data into a Data Warehouse
  9. Enforcing Data Quality
  10. Master Data Services
  11. Extending SQL Server Integration Services
  12. Deploying and Configuring SSIS Packages
  13. Consuming Data in a Data Warehouse

Supplementary InformationSubject to demand, training course MS20463 (SQL Server 2014) may be merged with MS10777 (SQL Server 2012), as recommended by Microsoft.
Module 1: Introduction to Data Warehousing
  • Overview of Data Warehousing
  • Considerations for a Data Warehouse Solution
  • Lab: Exploring a Data Warehousing Solution
  • Exploring Data Sources
  • Exploring and ETL Process
  • Exploring a Data Warehouse
Skills
  • Describe the key elements of a data warehousing solution
  • Describe the key considerations for a data warehousing project
top
Module 2: Planning Date Warehouse Structure
  • Considerations for Data Warehouse Infrastructure
  • Planning Data Warehouse Hardware
  • Lab: Planning Data Warehouse Infrastructure
  • Planning Data Warehouse Hardware
Skills
  • Describe key considerations for BI infrastructure
  • Plan data warehouse infrastructure
top
Module 3: Designing and Implementing a Data Warehouse
  • Data Warehouse Design Overview
  • Designing Dimension Tables
  • Designing Fact Tables
  • Physical Design for a Data Warehouse
  • Lab: Implementing a Data Warehouse
  • Implement a Star Schema
  • Implement a Snowflake Schema
  • Implement a Time Dimension
Skills
  • Describe a process for designing a dimensional model for a data warehouse
  • Design dimension tables for a data warehouse
  • Design fact tables for a data warehouse
  • Design and implement effective physical data structures for a data warehouse
top
Module 4: Creating an ETL Solutions with SSIS
  • Introduction to ETL with SSIS
  • Exploring Data Sources
  • Implementing Data Flow
  • Lab: Implementing Data Flow in an SSIS Package
  • Exploring Data Sources
  • Transferring Data by Using a Data Flow Task
  • Using Transformations in a Data Flow
Skills
  • Describe the key features of SSIS
  • Explore source data for an ETL solution
  • Implement a data flow by using SSIS
top
Module 5: Implementing Control Flow in an SSIS Package
  • Introduction to Control Flow
  • Creating Dynamic Packages
  • Using Containers
  • Managing Consistency
  • Lab: Implementing Control Flow in an SSIS Package
  • Using Tasks and Precedence in a Control Flow
  • Using Variables and Parameters
  • Using Containers
  • Lab: Using Transactions and Checkpoints
  • Using Transactions
  • Using Checkpoints
Skills
  • Implement control flow with tasks and precedence constraints
  • Create dynamic packages that include variables and parameters
  • Use containers in a package control flow
  • Enforce consistency with transactions and checkpoints
top
Module 6: Debugging and Troubleshooting SSIS Packages
  • Debugging an SSIS Package
  • Logging SSIS Package Events
  • Handling Errors in an SSIS Package
  • Lab: Debugging and Troubleshooting an SSIS Package
  • Debugging an SSIS Package
  • Logging SSIS Package Execution
  • Implementing an Event Handler
  • Handling Errors in a Data Flow
Skills
  • Debug an SSIS package
  • Implement logging for an SSIS package
  • Handle errors in an SSIS package
top
Module 7: Implementing a Data Extraction Solution
  • Planning Data Extraction
  • Extracting Modified Data
  • Lab: Extracting Modified Data
  • Using a Datetime Column
  • Using Change Data Capture
  • Using the CDC Control Task
  • Using Change Tracking
Skills
  • Plan data extraction
  • Extract modified data
top
Module 8: Loading Data into a Data Warehouse
  • Planning Data Loads
  • Using SSIS for Incremental Loads
  • Using Transact-SQL Loading Techniques
  • Lab: Loading a Data Warehouse
  • Loading Data from CDC Output Tables
  • Using a Lookup Transformation to Insert or Update Dimension Data
  • Implementing a Slowly Changing Dimension
  • Using the MERGE Statement
Skills
  • Describe the considerations for planning data loads
  • Use SQL Server Integration Services (SSIS) to load new and modified data into a data warehouse
  • Use Transact-SQL techniques to load data into a data warehouse
top
Module 9: Enforcing Data Quality
  • Introduction to Data Quality
  • Using Data Quality Services to Cleanse Data
  • Using Data Quality Services to Cleanse Data
  • Lab: Cleansing Data
  • Creating a DQS Knowledge Base
  • Using a DQS Project to Cleanse Data
  • Using DQS in an SSIS Package
Skills
  • Describe how Data Quality Services can help you manage data quality
  • Use Data Quality Services to cleanse your data
  • Use Data Quality Services to match data
top
Module 10: Master Data Services
  • Introduction to Master Data Services
  • Implementing a Master Data Services Model
  • Managing Master Data
  • Creating a Master Data Hub
  • Lab: Implementing Master Data Services
  • Creating a Master Data Services Model
  • Using the Master Data Services Add-in for Excel
  • Enforcing Business Rules
  • Loading Data Into a Model
  • Consuming Master Data Services Data
Skills
  • Describe key Master Data Services concepts
  • Implement a Master Data Services model
  • Use Master Data Services tools to manage master data
  • Use Master Data Services tools to create a master data hub
top
Module 11: Extending SQL Server Integration Services
  • Using Scripts in SSIS
  • Using Custom Components in SSIS
  • Lab: Using Custom Scripts
  • Using a Script Task
Skills
  • Include custom scripts in an SSIS package
  • Describe how custom components can be used to extend SSIS
top
Module 12: Deploying and Configuring SSIS Packages
  • Overview of SSIS Deployment
  • Deploying SSIS Projects
  • Planning SSIS Package Execution
  • Lab: Deploying and Configuring SSIS Packages
  • Creating an SSIS Catalog
  • Deploying an SSIS Project
  • Running an SSIS Package in SQL Server Management Studio
  • Scheduling SSIS Packages with SQL Server Agent
Skills
  • Describe considerations for SSIS deployment
  • Deploy SSIS projects
  • Plan SSIS package execution
top
Module 13: Consuming Data in a Data Warehouse
  • Introduction to Business Intelligence
  • Enterprise Business Intelligence
  • Self-Service BI and Big Data
  • Lab: Using a Data Warehouse
  • Exploring an Enterprise BI Solution
  • Exploring a Self-Service BI Solution
Skills
  • Describe BI and common BI scenarios
  • Describe how a data warehouse can be used in enterprise BI scenarios
  • Describe how a data warehouse can be used in self-service BI scenarios
top
> Pre-Requisites
Before attending this course, students must have the following pre-requisites:
  • At least 2 years’ experience of working with relational databases, including:
  • Designing a normalized database.
  • Creating tables and relationships.
  • Querying with Transact-SQL.
  • Some exposure to basic programming constructs (such as looping and branching).
  • An awareness of key business priorities such as revenue, profitability, and financial accounting is desirable.
> Purpose
After completing this course, students will be able to describe data warehouse concepts and architecture considerations; select an appropriate hardware platform for a data warehouse; design and implement a data warehouse; implement Data Flow in an SSIS Package; implement Control Flow in an SSIS Package; debug and Troubleshoot SSIS packages; implement an ETL solution that supports incremental data extraction; implement an ETL solution that supports incremental data loading; implement data cleansing by using Microsoft Data Quality Services; implement Master Data Services to enforce data integrity; extend SSIS with custom scripts and components; deploy and Configure SSIS packages; describe how BI solutions can consume data from the data warehouse.
> Supplementary Information
Subject to demand, training course MS20463 (SQL Server 2014) may be merged with MS10777 (SQL Server 2012), as recommended by Microsoft.