By using our website you agree to our use of cookies.

M10777: Implementing a Data Warehouse with Microsoft SQL Server 2012

5 Day Course
Hands On
Official Microsoft Curriculum
Code M10777

This course has been superseded by M20463: Implementing a Data Warehouse with Microsoft SQL Server (SSIS).

Modules

Collapse all

Introduction to Data Warehousing (7 topics)

  • Describe data warehouse concepts and architecture considerations
  • Considerations for a Data Warehouse Solution
  • Lab: Exploring a Data Warehousing Solution
  • Exploring Data Sources
  • Exploring an ETL Process
  • Exploring a Data Warehouse
  • Describe data warehouse concepts and architecture considerations.

Data Warehouse Hardware Considerations (3 topics)

  • The Challenges of Building a Data Warehouse
  • Data Warehouse Reference Architectures
  • Data Warehouse Appliances

Designing and Implementing a Data Warehouse (7 topics)

  • Logical Design for a Data Warehouse
  • Physical Design for a Data Warehouse
  • Lab: Implementing a Data Warehouse Schema
  • Implementing a Star Schema
  • Implementing a Snowflake Schema
  • Implement a Time Dimension Table
  • Design and implement a schema for a data warehouse.

Design and Implement a Schema for a Data Warehouse (8 topics)

  • Introduction to ETL with SSIS
  • Exploring Source Data
  • Implementing Data Flow
  • Lab: Implementing Data Flow in an SSIS Package
  • Exploring Source Data
  • Transfer Data with a Data Flow Task
  • Using Transformations in a Data Flow
  • Implement Data Flow in an SSIS Package

Implementing Control Flow in an SSIS Package (12 topics)

  • 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
  • Implement control flow in an SSIS package.

Debugging and Troubleshooting SSIS Packages (9 topics)

  • 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
  • Debug and Troubleshoot SSIS packages.

Implementing and Incremental ETL Process (13 topics)

  • Introduction to Incremental ETL
  • Extracting Modified Data
  • Loading Modified Data
  • Lab: Extracting Modified Data
  • Using a DateTime Column to Incrementally Extract Data
  • Using a DateTime Column to Incrementally Extract Data
  • Using Change Tracking
  • Lab: Loading Incremental Changes
  • Using a Lookup task to insert dimension data
  • Using a Lookup task to insert or update dimension data
  • Implementing a Slowly Changing Dimension
  • Using a MERGE statement to load fact data
  • Implement an SSIS solution that supports incremental DW loads and changing data

Incorporating Data from the Cloud in a Data Warehouse (7 topics)

  • Overview of Cloud Data Sources
  • SQL Server Azure
  • Azure Data Market
  • Lab: Using Cloud data in a Data Warehouse Solution
  • Extracting data from SQL Azure
  • Acquiring Data from the Azure Data Market
  • Integrate cloud data into a data warehouse ecosystem.

Enforcing Data Quality (11 topics)

  • Introduction to Data Cleansing
  • 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
  • Implement data cleansing by using Microsoft Data Quality Services.

Using Master Data Services (10 topics)

  • Master Data Services Concepts
  • Implementing a Master Data Services Model
  • Using the Master Data Services Excel Add-in
  • Lab: Implementing Master Data Services
  • Creating a Basic MDS Model
  • Editing an MDS Model With Excel
  • Loading Data into MDS
  • Enforcing Business Rules
  • Consuming Master Data Services Data
  • Implement Master Data Services to enforce data integrity at source.

Extending SSIS (6 topics)

  • Using Custom Components in SSIS
  • Using Scripting in SSIS
  • Lab: Using Scripts and Custom Components
  • Using a Custom Component
  • Using the Script Task
  • Extend SSIS with custom scripts and components

Deploying and Configuring SSIS Packages (10 topics)

  • 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
  • Deploy and configure SSIS packages.

Consuming Data in a Data Warehouse (6 topics)

  • Using Excel to Analyze Data in a data Warehouse.
  • An Introduction to PowerPivot
  • An Introduction to Crescent
  • Lab: Using a Data Warehouse
  • Use PowerPivot to Query the Data Warehouse
  • Visualizing Data by Using Crescent

Prerequisites

In addition to their professional experience, students who attend this training should have technical knowledge equivalent to the following course: M10774A: Querying Microsoft SQL Server 2010

Additional Learning

The courses below may help you meet the knowledge level required to take this course.

  • M20461: Querying Microsoft SQL Server

    This 5-day course provides delegates with the skills required to write basic Transact-SQL queries for Microsoft SQL Server 2012.

    5 Day Course Hands On Training Official Curriculum Course Code M20461
    Classroom Virtual Classroom Private Group - Virtual Self-Paced Online
Microsoft SQL Server Training Courses

Course PDF

Print

Sections