Implementing a Data Warehouse with Microsoft SQL Server 2014

Implementing a Data Warehouse with Microsoft SQL Server (M20463)

Support Business Intelligence solutions and Implement data warehouse
Maintain quality and integrity of data using SQL Server Data Quality Services
Understand about SSIS packages and how to load, extract and transform information from it

Book Now

Implementing a Data Warehouse with Microsoft SQL Server (M20463)

Overview 1

Outlines 2

FAQs 3

What's Included 4

Course Overview

The Pearce Mayfield’s Implementing a Data Warehouse with Microsoft SQL Server (M20463) course provides essential knowledge required to set up warehousing facilities using SQL Server in the organisation. The course introduces delegates to the basic principles of data warehousing, data extraction, using master data services and creating ETL (Extract, Transform and Load) solution. During the course, the delegates will also learn how to cleanse and validate data using SQL Server master data and Quality Services.

Course Outline

Introduction to Data Warehousing
- Data warehousing overview
- Considerations for data warehousing solution
Planning Data Warehouse Infrastructure
- Considerations for Data Warehousing Infrastructure
- Planning Data Warehouse Hardware
Design and Implement Data Warehouse
- Overview of Data Warehouse design
- Design dimension tables
- Design fact tables
- Physical design for data warehouse
Create an ETL solution with SSIS
- Introduction to ETL with SSIS
- Explore data sources
- Implement data flow
Implementing Control Flow in an SSIS Package
- Introduction to control flow
- Create dynamic packages
- Use containers
- Manage consistency
Debugging and Troubleshooting SSIS packages
- Debug an SSIS package
- Logging SSIS package events
- Handle errors in SSIS package
Implementing a Data Extraction Solution
- Plan data extraction
- Extract modified data
Loading Data into Data Warehouse
- Plan data loads
- Use SSIS for incremental loads
- Use Transact-SQL loading techniques
Enforce Data Quality
- Overview of Microsoft SQL Server Data Quality Services (DQS)
- Cleanse and validate data
Master Data Services
- Introduction to Master Data Services
- Implement Master Data Services model
- Manage Master data
- Create Master data hub
Extending SQL Server Integration Services
- Use scripts in SSIS
- Use custom components in SSIS
Deploy and Configure SSIS Packages
- Overview of SSIS deployment
- Deploy SSIS projects
- Plan SSIS package execution
Consuming Data in Data Warehouse
- Introduction to business intelligence
- Enterprise Business Intelligence
- Big data and Self Service BI

The delegates must possess minimum three years working experience with some relational database management system. They must be familiar with the concepts of normalization and able to create tables and databases using the said concept. The knowledge of establishing relationships, executing SQL queries and writing scripts using T-SQL supported functions and procedures would also be beneficial.

The course is intended for Database professionals who are responsible for developing their Business Intelligence skills and Database developers who are involved in the implementation of a data warehouse, ETL and cleansing data. Primary responsibilities of database administrator include: