Learn to Implement a Data Warehouse Solution Using SQL Server Integration Services – SSIS from Scratch
What you will learn
Design a Data Warehouse Solution
Desig Dimension Tables
Create ETL Solution
Explore Data source
Implement control flow
Implement data flow
Debug SSIS Packages
Handle errors
Log SSIS Package Events
Extract and modify data
Load data
Configure SSIS
Deploy SSIS
Execute package with Server Agent
Enforce data quality
Cleanse data
Remove duplicates
Consume data in the data warehouse
Description
This course is a beginners course that will show you how to implement enterprise data warehouse solution using Microsoft SQL Server ,Microsoft SQL Server Integration Services SSIS and Microsoft SQL Server Data Tools -SSDT.
You will learn how to implement ETL ( Extract,Transform, Load)Â process using SQL Server Integration services .
Students will learn how to :
- Deploy and Configure SSIS packages.
- Download and installing SQL Server 2014
- Download and attaching Adventureworks2014 database
- Download and installing SSDT
- Download and installing Visual studio
- Describe data warehouse concepts and architecture considerations.
- Select an appropriate hardware platform for a data warehouse.
- Design and implement a data warehouse.
- Implement Data Flow in an SSIS Package.
- Implement Control Flow in an SSIS Package.
- Debug and Troubleshoot SSIS packages.
- Implement an ETL solution that supports incremental data extraction.
- Implement an ETL solution that supports incremental data loading.
- Implement data cleansing by using Microsoft Data Quality Services.
- Implement Master Data Services to enforce data integrity.
- Extend SSIS with custom scripts and components.
- Databases vs. Data warehouses
- Choose between star and snowflake design schemas
- Explore source data
- Implement data flow
- Debug an SSIS package
- Extract and load modified data
- Enforce data quality
- Consume data in a data warehouse
After completing this course you will be able to :
- Understand the components of a data warehousing solution
- Implement a logical and physical design to create a data warehouse
- Implement a physical design for a data warehouse
- Understand SSIS for implementing data flows
- Create dynamic packages using parameters and variables
- Implement Data Quality Services and Master Data Services
Implement custom components for extending SSIS
English
language
Content
Environment Setup
Introduction
Download and Install SQL SQL Server Enterprise
Download and Load Sample Database
Enable SQL Server Agent
Download and Install SQL Server Express
Download and Load Sample Database
Download and Install SQL Server Data Tools -SSDT
Testing Database
Adjusting Database Settings
Visual Studio installation requirements
Download and Install Visual Studio – Part 1
Download and Install Visual Studio – Part 2
DW Hardware requirements
FTDW Sizing Tool
Introduction to Data Warehouse
What is Data Warehouse
What is ETL
DW Vs EDW
Database vs Data Warehouse
Designing a Data Warehouse – Logical design
Designing a Data Warehouse – Physical design
Designing a Data Warehouse – Physical design part 2
Designing Dimension Tables
Introduction to SSIS and Creating ETL Solution
What is SSIS
Introduction to ETL using SSIS
Exploring the source data – part 1
Exploring the source data – part 2
Introduction to control flow -part 1
Introduction to control flow -part 2
Implementing data flow – part 1
Implementing data flow – part 2
Debugging SSIS package – part 1
Debugging SSIS package – part 2
Logging SSIS package events
Handling errors in an SSIS package
Introduction to Incremental ETL
Extracting modified data part 1
Extracting modified data part 2
Extracting modified data part 3
Extracting modified data part 4
Loading modified data part 1
Loading modified data part 2
Using other slowly changing dimensions
How to Deploy and Configure SSIS
Introduction to Integration Services Catalogs
Deploying SSIS Solutions
Executing SSIS Package with SQL Server Agent
Configuring advanced SSIS Settings
Implementing and Enforcing Data Quality
Installing Data Quality Services
Cleansing Data
Dealing with Duplicates
Dealing with Duplicates – Part 2
Checking quality of SSIS data flow
How to consume Data in a Data Warehouse
Business Intelligence
Reporting Services
Reporting Services -Part 2
Data analysis -Part 1
Data analysis -Part 2