20767C: Implementing a SQL Data Warehouse Training

Overview

This five-day instructor-led course provides students with the knowledge and skills to provision a Microsoft SQL Server database. The course covers SQL Server provision both on-premise and in Azure, and covers installing from new and migrating from an existing install.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.

What you will Learn

Describe the key elements of a data warehousing solution
Describe the main hardware considerations for building a data warehouse
Implement a logical design for a data warehouse
Implement a physical design for a data warehouse
Create columnstore indexes
Implementing an Azure SQL Data Warehouse
Describe the key features of SSIS
Implement a data flow by using SSIS
Implement control flow by using tasks and precedence constraints
Create dynamic packages that include variables and parameters
Debug SSIS packages
Describe the considerations for implement an ETL solution
Implement Data Quality Services
Implement a Master Data Services model
Describe how you can use custom components to extend SSIS
Deploy SSIS projects
Describe BI and common BI scenarios

PREREQUISITES

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

CURRICULUM

Learning Objectives:

This module describes data warehouse concepts and architecture consideration.

Lessons:

Overview of Data Warehousing

Considerations for a Data Warehouse Solution

Lab : Exploring a Data Warehouse Solution

Exploring data sources

Exploring an ETL process

Exploring a data warehouse

Learning Objectives:

This module describes the main hardware considerations for building a data warehouse.

Lessons:

Considerations for data warehouse infrastructure.

Planning data warehouse hardware.

Lab : Planning Data Warehouse Infrastructure

Planning data warehouse hardware

Learning Objectives:

This module describes how you go about designing and implementing a schema for a data warehouse.

Lessons:

Data warehouse design overview

Designing dimension tables

Designing fact tables

Physical Design for a Data Warehouse

Lab : Implementing a Data Warehouse Schema

Implementing a star schema

Implementing a snowflake schema

Implementing a time dimension table

Learning Objectives:

This module introduces Columnstore Indexes.

Lessons:

Introduction to Columnstore Indexes

Creating Columnstore Indexes

Working with Columnstore Indexes

Lab : Using Columnstore Indexes

Create a Columnstore index on the FactProductInventory table

Create a Columnstore index on the FactInternetSales table

Create a memory optimized Columnstore table

Learning Objectives:

This module describes Azure SQL Data Warehouses and how to implement them.

Lessons:

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

Lab : Implementing an Azure SQL Data Warehouse

Create an Azure SQL data warehouse database

Migrate to an Azure SQL Data warehouse database

Copy data with the Azure data factory

Learning Objectives:

At the end of this module you will be able to implement data flow in a SSIS package.

Lessons:

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 row task

Using transformation components in a data row

Learning Objectives:

This module describes implementing control flow in an SSIS package.

Lessons:

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

Learning Objectives:

This module describes how to debug and troubleshoot SSIS packages.

Lessons:

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 data flow

Learning Objectives:

This module describes how to implement an SSIS solution that supports incremental DW loads and changing data.

Lessons:

Introduction to Incremental ETL

Extracting Modified Data

Loading modified data

Temporal Tables

Lab : Extracting Modified Data

Using a datetime column to incrementally extract data

Using change data capture

Using the CDC control task

Using change tracking

Lab : Loading a data warehouse

Loading data from CDC output tables

Using a lookup transformation to insert or update dimension data

Implementing a slowly changing dimension

Using the merge statement

Learning Objectives:

This module describes how to implement data cleansing by using Microsoft Data Quality services.

Lessons:

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

Using DQS in an SSIS package

Lab : De-duplicating Data

Creating a matching policy

Using a DS project to match data

Learning Objectives:

This module describes how to implement master data services to enforce data integrity at source.

Lessons:

Introduction to Master Data Services

Implementing a Master Data Services Model

Hierarchies and collections

Creating a Master Data Hub

Lab : Implementing Master Data Services

Creating a master data services model

Using the master data services add-in for Excel

Enforcing business rules

Loading data into a model

Consuming master data services data

Learning Objectives:

This module describes how to extend SSIS with custom scripts and components.

Lessons:

Using scripting in SSIS

Using custom components in SSIS

Lab : Using scripts

Using a script task

Learning Objectives:

This module describes how to deploy and configure SSIS packages.

Lessons:

Overview of SSIS Deployment

Deploying SSIS Projects

Planning SSIS Package Execution

Lab : Deploying and Configuring SSIS Packages

Creating an SSIS catalog

Deploying an SSIS project

Creating environments for an SSIS solution

Running an SSIS package in SQL server management studio

Scheduling SSIS packages with SQL server agent

Learning Objectives:

This module describes how to debug and troubleshoot SSIS packages.

Lessons:

Introduction to Business Intelligence

An Introduction to Data Analysis

Introduction to reporting

Analyzing Data with Azure SQL Data Warehouse

Lab : Using a data warehouse

Exploring a reporting services report

Exploring a PowerPivot workbook

Exploring a power view report

FAQs

You can enroll for this classroom training online. Payments can be made using any of the following options and receipt of the same will be issued to the candidate automatically via email.
1. Online ,By deposit the mildain bank account
2. Pay by cash team training center location

Highly qualified and certified instructors with 20+ years of experience deliver more than 200+ classroom training.

Contact us using the form on the right of any page on the mildaintrainings website, or select the Live Chat link. Our customer service representatives will be able to give you more details.

You will never miss a lecture at Mildaintrainigs! You can choose either of the two options: View the recorded session of the class available in your LMS. You can attend the missed session, in any other live batch.

We have a limited number of participants in a live session to maintain the Quality Standards. So, unfortunately, participation in a live class without enrollment is not possible. However, you can go through the sample class recording and it would give you a clear insight about how are the classes conducted, quality of instructors and the level of interaction in a class.

Yes, you can cancel your enrollment if necessary prior to 3rd session i.e first two sessions will be for your evaluation. We will refund the full amount without deducting any fee for more details check our Refund Policy

Yes, the access to the course material will be available for lifetime once you have enrolled into the course.

Just give us a CALL at +91 8447121833 OR email at info@mildaintrainings.com

Call us At