A Small Class Size is the Best Learning Environment*
Only F1 has no more than 6 Delegates in a Class
Everyone has Individual Attention from the Trainer
Niche
SQL Server 2012

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

Save up to 50% of the cost of some courses: check our Certification Packages or buy F1 Training Vouchers


> Target Audience
This course describes how to implement a BI platform to support information worker analytics. It is intended for database professionals who need to fulfil a Business Intelligence Developer role.
> Course outline
  1. Introduction to Data Warehousing
  2. Data Warehouse Hardware
  3. Designing and Implementing a Data Warehouse
  4. Creating an ETL Solution with SSIS
  5. Implementing Control Flow in an SSIS Package
  6. Debugging and Troubleshooting SSIS Packages
  7. Enforcing Data Quality
  8. Using Master Data Services
  9. Extending SQL Server Integration Services
  10. Deploying and Configuring SSIS Packages
  11. Consuming Data in a Data Warehouse
Module 1: Introduction to Data Warehousing
  • Overview of Data Warehousing
  • Considerations for a Data Warehouse Solution
  • Lab: Exploring Data Sources
  • Exploring an ETL Process
  • Exploring a Data Warehouse
Skills
  • Describe the key elements of a data warehouse solution
  • Describe the key considerations for a data warehousing project
top
Module 2: Data Warehouse Hardware
  • Considerations for Building a Data Warehouse
  • Data Warehouse Reference Architectures and Appliances
Skills
  • Describe the main hardware considerations for building a data warehouse
  • Explain how to use reference architectures and data warehouse appliances to create a data warehouse
top
Module 3: Designing and Implementing a Data Warehouse
  • Logical Design for a Data Warehouse
  • Physical Design for a Data Warehouse
  • Lab: Implementing a Star Schema
  • Implementing a Snowflake Schema
  • Implement a Time Dimension Table
Skills
  • Implement a logical design for a data warehouse
  • Implement a physical design for a data warehouse
top
Module 4: Creating an ETL Solution with SSIS
  • Introduction to ETL with SSIS
  • Exploring Source Data
  • Implementing Data Flow
  • Lab: Implementing Data Flow in an SSIS Package
  • Exploring Source Data
  • 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 Data Flow 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 an SSIS Data Flow
Skills
  • Debug an SSIS package
  • Implement logging for an SSIS package
  • Handle Errors in an SSIS package
top
Module 7: Enforcing Data Quality
  • Introduction to Data Quality
  • Using Data Quality Services to Cleanse Data
  • Using Data Quality Services to Match Data
  • Lab: Cleansing Data
  • Creating a DQS Knowledge Base
  • Using a DQS Project to Cleanse Data
  • Use DQS in an SSIS Package
  • Lab: De-Duplicating Data
  • Creating a Matching Policy
  • Using a DQS Project to Match Data
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 8: Using Master Data Services
  • Introduction to Master Data Services
  • Implementing a Master Data Services Model
  • Using the Master Data Services Excel Add-in
  • Lab: Implementing Master Data Services
  • Creating a Basic Model
  • Editing a Model by Using the Master Data Services Add-in for Excel
  • Loading Data into a Model
  • Enforcing Business Rules
  • Consuming Master Data Services Data
Skills
  • Describe key Master Data Services concepts
  • Implement a Master Data Services Model
  • Use the Master Data Services Add-in for Excel to view and modify a Model
top
Module 9: Extending SQL Server Integration Services
  • Using Custom Components in SSIS
  • Using Scripts in SSIS
  • Lab: Using Scripts and Custom Components
  • Using a Custom Component
  • Using the Script Task
Skills
  • Describe how custom components can be used to extend SSIS
  • Describe how you can include custom scripts in an SSIS package
top
Module 10: Deploying and Configuring SSIS Packages
  • Overview of Deployment
  • Deploying SSIS Projects
  • Planning SSIS Package Execution
  • Lab: Deploying and Configuring SSIS Packages
  • Create an SSIS Catalog
  • Deploy an SSIS Project
  • Create Environments for an SSIS Solution
  • Running an SSIS Package in SQL Server Management Studio
  • Scheduling SSIS Packages with SQL Server Agent
Skills
  • Describe SSIS deployment
  • Explain how to deploy SSIS projects using the project deployment model
  • Plan SSIS package execution
top
Module 11: Consuming Data in a Data Warehouse
  • Introduction to Business Intelligence
  • Introduction to Reporting
  • Introduction to Data Analysis
  • Lab: Using Business Intelligence Tools
  • Exploring a Reporting Services Report
  • Exploring a PowerPivot Workbook
  • Exploring a Power View Report
Skills
  • Describe BI and common BI scenario
  • Explain the key features of SQL Server Reporting Services
  • Explain the key features of SQL Server Analysis Services
top
> Pre-Requisites
Before attending this course, students should have 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).
> 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 Data Flow in an SSIS Package; debug and Troubleshoot SSIS packages; implement an SSIS solution that supports incremental DW loads and changing data; integrate cloud data into a data warehouse ecosystem infrastructure; implement data cleansing by using Microsoft Data Quality Services; implement Master Data Services to enforce data integrity at source; extend SSIS with custom scripts and components; Deploy and Configure SSIS packages; describe how information workers can consume data from the data warehouse.