Implementing a Data Warehouse with Microsoft SQL Server 2014

Session Detail:    Classroom, Virtual 5 Day

Data warehousing is a solution organizations use to centralize business data for reporting and analysis. This five-day instructor-led course focuses on teaching individuals how to create a data warehouse with SQL Server 2014, and implement ETL with SQL Server Integration Services. This course helps people prepare for exam 70-463.

Prerequisites

This course requires that you meet the following prerequisites:

  • At least 2 years experience of working with relational databases, including:
  • Designing a normalized database.
  • Creating tables and relationships.
  • Querying withTransact-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.

Who Can Benefit

This course is intended for database professionals who need to fulfill a Business Intelligence Developer role. They will need to focus on hands-on work creating BI solutions including Data Warehouse implementation, ETL, and data cleansing. Primary responsibilities include:

  • Implementing a data warehouse.
  • Developing SSIS packages for data extraction, transformation, and loading.
  • Enforcing data integrity by using Master Data Services.
  • Cleansing data by using Data Quality Services.

Session Outline

Class Outline

Module 1: Introduction to Data Warehousing

  • Lesson 1: Overview of Data Warehousing
  • Lesson 2: Considerations for a Data Warehouse Solution

After completing this module, you will be able to:

  • Describe the key elements of a data warehousing solution
  • Describe the key considerations for a data warehousing project

Module 2: Data Warehouse Hardware Considerations

  • Lesson 1: Considerations for building a Data Warehouse
  • Lesson 2: Data Warehouse Reference Architectures and Appliances

After completing this module, you will be able to:

  • Describe key considerations for BIinfrastructure
  • Plan data warehouse infrastructure

Module 3: Designing and Implementing a Data Warehouse

  • Lesson 1: Logical Design for a Data Warehouse
  • Lesson 2: Physical design for a data warehouse

After completing this module, you will be able to:

  • 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

Module 4: Creating an ETL Solution with SSIS

  • Lesson 1: Introduction to ETL with SSIS
  • Lesson 2: Exploring Data Sources
  • Lesson 3: Implementing Data Flow

After completing this module, you will be able to:

  • Describe the key features of SSIS
  • Explore source data for an ETL solution
  • Implement a data flow by using SSIS

Module 5: Implementing Control Flow in an SSIS Package

  • Lesson 1: Introduction to Control Flow
  • Lesson 2: Creating Dynamic Packages
  • Lesson 3: Using Containers
  • Lesson 4: Managing Consistency

After completing this module, you will be able to:

  • 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

Module 6: Debugging and Troubleshooting SSIS Packages

  • Lesson 1: Debugging an SSIS Package
  • Lesson 2: Logging SSIS Package Events
  • Lesson 3: Handling Errors in an SSIS Package

After completing this module, you will be able to:

  • Debug an SSIS package
  • Implement logging for an SSIS package
  • Handle errors in an SSIS package

Module 7: Implementing a Data Extraction Solution

  • Lesson 1: Planning Data Extraction
  • Lesson 2: Extracting Modified Data

After completing this module, you will be able to:

  • Plan data extraction
  • Extract modified data

Module 8: Loading Data into a Data Warehouse

  • Lesson 1: Planning Data Loads
  • Lesson 2: Using SSIS for Incremental Loads
  • Lesson 3: Using Transact-SQL Loading Techniques

After completing this module, you will be able to:

  • 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

Module 9: Enforcing Data Quality

  • Lesson 1: Introduction to Data Quality
  • Lesson 2: Using Data Quality Services to Cleanse Data
  • Lesson 3: Using Data Quality Services to Match Data

After completing this module, you will be able to:

  • Describe how DQS can help you manage data quality
  • Use DQS to cleanse your data
  • Use DQS to match data

Module 10: Master Data Services

  • Lesson 1: Introduction to Master Data Services
  • Lesson 2: Implementing a Master Data Services Model
  • Lesson 3: Managing Master Data
  • Lesson 4: Creating a Master Data Hub

After completing this module, you will be able to:

  • Describe the key concepts of Master Data Services
  • 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

Module 11: Extending SQL Server Integration Services

  • Lesson 1: Using Scripts in SSIS
  • Lesson 2: Using Custom Components in SSIS

After completing this module, you will be able to:

  • Include custom scripts in an SSIS package
  • Describe how custom components can be used to extend SSIS

Module 12: Deploying and Configuring SSIS Packages

  • Lesson 1: Overview of SSIS Deployment
  • Lesson 2: Deploying SSIS Projects
  • Lesson 3: Planning SSIS Package Execution

After completing this module, you will be able to:

  • Describe considerations for SSIS deployment
  • Deploy SSIS projects
  • Plan SSIS package execution

Module 13: Consuming Data in a Data Warehouse

  • Lesson 1: Introduction to Business Intelligence
  • Lesson 2: Enterprise Business Intelligence
  • Lesson 3: Self-Service BI and Big Data

After completing this module, you will be able to:

  • 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

Need to train your team?

All of our sessions can be customized to meet your team’s specific need. Build the perfect program by picking and choosing topics from any of the courses in our catalog. A personalized private session gives you the ultimate flexibility and helps maximize your team’s valuable time!

Requesting Team Training
DateTimeTypePriceAdd To Cart

Implementing a Data Warehouse with Microsoft SQL Server 2014

5 Day
Classroom, Virtual

$3,095.00

Chat with a Coach

Chat with a Coach

Have a more immediate need? Why spend the next hour searching online for answers when you can spend just 15 minutes with one of our experts and get accurate and personalized answers to all of your questions. 

$9.99 | 15 Minutes

Book Now