LGIT Smart Solutions

Uplifting: Business - People - Community

Course 20767: Implementing a SQL Data Warehouse

Duration: 90-days online access
ILT Classroom: 5 days
Audience: IT Professionals
Certification: MCSA
Exam: 70-767
Level: 300

Purchase now

Overview

About this course
This course provides students with the knowledge and skills to provision a Microsoft SQL Server 2016 database. The course covers SQL Server 2016 provision both on-premise and in Azure, and covers installing from new and migrating from an existing install.

Audience profile
The primary audience for this course are database professionals who need to fulfil 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.

At course completion
After completing this course, students will be able to:

  • Provision a Database Server
  • Upgrade SQL Server
  • Configure SQL Server
  • Manage Databases and Files (shared)

Pre-requisites
In addition to their professional experience, students who attend this training should already have the following technical knowledge:

  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of relational databases.
  • Some experience with database design.

Course Outline

  • Module 1: Introduction to Data Warehousing
    This module describes data warehouse concepts and architecture consideration.
    - Overview of Data Warehousing
    - Considerations for a Data Warehouse Solution
  • Module 2: Planning Data Warehouse Infrastructure
    This module describes the main hardware considerations for building a data warehouse.
    - Considerations for data warehouse infrastructure.
    - Planning data warehouse hardware.
  • Module 3: Designing and Implementing a Data Warehouse
    This module describes how you go about designing and implementing a schema for a data warehouse.
    - Designing dimension tables
    - Designing fact tables
    - Physical Design for a Data Warehouse
  • Module 4: Columnstore Indexes
    This module introduces Columnstore Indexes.
    - Introduction to Columnstore Indexes
    - Creating Columnstore Indexes
    - Working with Columnstore Indexes
  • Module 5: Implementing an Azure SQL Data Warehouse
    This module describes Azure SQL Data Warehouses and how to implement them.
    - Advantages of Azure SQL Data Warehouse
    - Implementing an Azure SQL Data Warehouse
    - Developing an Azure SQL Data Warehouse
    - Migrating to an Azure SQ Data Warehouse
    - Copying data with the Azure data factory
  • Module 6: Creating an ETL Solution
    At the end of this module you will be able to implement data flow in a SSIS package.
    - Introduction to ETL with SSIS
    - Exploring Source Data
    - Implementing Data Flow
  • Module 7: Implementing Control Flow in an SSIS Package
    This module describes implementing control flow in an SSIS package.
    - Introduction to Control Flow
    - Creating Dynamic Packages
    - Using Containers
    - Managing consistency.
  • Module 8: Debugging and Troubleshooting SSIS Packages
    This module describes how to debug and troubleshoot SSIS packages.
    - Debugging an SSIS Package
    - Logging SSIS Package Events
    - Handling Errors in an SSIS Package
  • Module 9: Implementing a Data Extraction Solution
    This module describes how to implement an SSIS solution that supports incremental DW loads and changing data.
    - Introduction to Incremental ETL
    - Extracting Modified Data
    - Loading modified data
    - Temporal Tables
  • Module 10: Enforcing Data Quality
    This module describes how to implement data cleansing by using Microsoft Data Quality services.
    - Introduction to Data Quality
    - Using Data Quality Services to Cleanse Data
    - Using Data Quality Services to Match Data
  • Module 11: Using Master Data Services
    This module describes how to implement master data services to enforce data integrity at source.
    - Introduction to Master Data Services
    - Implementing a Master Data Services Model
    - Hierarchies and collections
    - Creating a Master Data Hub
  • Module 12: Extending SQL Server Integration Services (SSIS)
    This module describes how to extend SSIS with custom scripts and components.
    - Using scripting in SSIS
    - Using custom components in SSIS
  • Module 13: Deploying and Configuring SSIS Packages
    This module describes how to deploy and configure SSIS packages.
    - Overview of SSIS Deployment
    - Deploying SSIS Projects
    - Planning SSIS Package Execution
  • Module 14: Consuming Data in a Data Warehouse
    This module describes how to debug and troubleshoot SSIS packages.
    - Introduction to Business Intelligence
    - An Introduction to Data Analysis
    - Introduction to reporting
    - Analyzing Data with Azure SQL Data Warehouse

Purchase now