18756 Stone Oak Park Way, Suite200, San Antonio TX 78258 USA
100 Queen St W, Brampton, ON L6X 1A4, Canada
country flag
share button

20767: Implementing SQL Data Warehouse Training


What 20767: Implementing SQL Data Warehouse training is all about?

20767: Implementing SQL Data Warehouse Training teaches professionals the leading methodologies to build a data warehouse with the help of MS SQL Server and Azure SQL Data Warehouse. This technical course demonstrates how to apply a data warehouse platform to support a BI solution. It provides comprehensive knowledge of the main hardware considerations for developing a data warehouse. Throughout this course, trainees will learn to implement a logical design and a physical design for a data warehouse. It also elaborates on the critical features of SSIS and teaches how to create dynamic packages, including parameters and variables. This training program is ideal for a data professional who works as a Business Intelligence Developer. This program covers all the essential topics from implementing control flow and debugging SSIS packages to applying to a master data services school.

This training is designed based on the objectives of the course variant 20767C.

5 SATVs applicable


Schedule
  • Delivery Format:
Date: Nov 30, 2020 | 9:00 am - 5:00 pm EST
Location: Online
$2875 USD
  • Delivery Format:
Date: Dec 07, 2020 | 9:00 am - 5:30 pm EST
Location: Online
$2875 USD
  • Delivery Format:
Date: Dec 14, 2020 | 9:00 am - 5:30 pm EST
Location: Online
$2875 USD
What are the course objectives for 20767: Implementing SQL Data Warehouse training?
  • Describing data warehousing solution’s key elements.
  • Describe the major hardware considerations to build the data warehouse
  • Implementing a logical and physical design for the data warehouses
  • Creating column store indexes
  • Implement the Azure SQL Data Warehouse
  • Describing SSIS’s key features
  • Using SSIS for implementing data flow
  • Using precedence constraints and tasks for implementing control flow
  • Create variables and parameters consisting of dynamic packages
  • Debugging SSIS packages
  • Describing the considerations for implementing ETL solution
  • Implementing Data Quality Services
  • Implementing the Master Data Services model
  • Describe using of custom components for extending SSIS
  • Deploying SSIS projects
  • Describing Business Intelligence and common Business Intelligence scenarios
Who should attend 20767: Implementing SQL Data Warehouse training?

The database professionals looking to upgrade to a BI developer role are the primary audience for the course. This course will teach them hands-on work to create Business Intelligence solutions that include implementing Data Warehouse, data cleansing and ETL.

What are the prerequisites for 20767: Implementing SQL Data Warehouse training?
  • Knowledge of Microsoft Windows operating system; and, its core functionality
  • Knowledge of relational databases
  • Experience in database designing
What is the course outline for 20767: Implementing SQL Data Warehouse training?
  • 1. Introduction to Data Warehousing
  • This module describes data warehouse concepts and architecture consideration.

  • 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
  • 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 solution
  • 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.
  • Lab: Planning Data Warehouse Infrastructure

  • Planning data warehouse hardware
  • After completing this module, you will be able to:

  • Describe the main hardware considerations for building a data warehouse
  • Explain how to use reference architectures and data warehouse appliances to create a data warehouse
  • 3. Designing and Implementing a Data Warehouse
  • This module describes how you go about designing and implementing a schema for a data warehouse.

  • 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
  • After completing this module, you will be able to:

  • Implement a logical design for a data warehouse
  • Implement a physical design for a data warehouse
  • 4. Columnstore Indexes
  • This module introduces Columnstore Indexes.

  • 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
  • After completing this module, you will be able to:

  • Create Columnstore indexes
  • Work with Columnstore Indexes
  • 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
  • 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
  • After completing this module, you will be able to:

  • Describe the advantages of Azure SQL Data Warehouse
  • Implement an Azure SQL Data Warehouse
  • Describe the considerations for developing an Azure SQL Data Warehouse
  • Plan for migrating to Azure SQL Data Warehouse
  • 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
  • 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
  • After completing this module, you will be able to:

  • Describe ETL with SSIS
  • Explore Source Data
  • Implement a Data Flow
  • 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.
  • 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
  • After completing this module, you will be able to:

  • Describe control flow
  • Create dynamic packages
  • Use containers
  • 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
  • Lab: Debugging and Troubleshooting an SSIS Package

  • Debugging an SSIS package
  • Logging SSIS package execution
  • Implementing an event handler
  • Handling errors in data flow
  • After completing this module, you will be able to:

  • Debug an SSIS package
  • Log SSIS package events
  • Handle errors in an SSIS package
  • 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
  • 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
  • After completing this module, you will be able to:

  • Extract modified data
  • Load modified data.
  • Describe temporal tables
  • 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
  • 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
  • After completing this module, you will be able to:

  • Describe data quality services
  • Cleanse data using data quality services
  • Match data using data quality services
  • De-duplicate data using data quality services
  • 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
  • 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
  • After completing this module, you will be able to:

  • Describe the key concepts of master data services
  • Implement a master data service model
  • Manage master data
  • Create a master data hub
  • 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
  • Lab: Using scripts

  • Using a script task
  • After completing this module, you will be able to:

  • Use custom components in SSIS
  • Use scripting in SSIS
  • 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
  • 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
  • After completing this module, you will be able to:

  • Describe an SSIS deployment
  • Plan SSIS package execution
  • 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
  • Lab: Using a data warehouse

  • Exploring a reporting services report
  • Exploring a PowerPivot workbook
  • Exploring a power view report
  • After completing this module, you will be able to:

  • Describe at a high level business intelligence
  • Show an understanding of reporting
  • Show an understanding of data analysis
  • Analyze data with Azure SQL data warehouse
FAQ's

Yes, it is. Then exam is 75% oriented to SQL Server 2016 on-premises. It is 25% oriented to Azure Big Data, Azure SQL Data Warehouse and other related topics.

It is similar to 70-463 exam; however, this exam now covers Integrate with Cloud data and big data chapter and some other updates.

Yes, it prepares you for MCSA: SQL 2016 BI Development and MCSE: Data Management and Analytics certifications.

The class room live lab course outline as described by Microsoft contains 17 exercises including - Exploring a Data Warehouse Solution, Planning Data Warehouse Infrastructure, Using Columnstore Indexes, Implementing a Data Warehouse Schema and Implementing an Azure SQL Data Warehouse etc.

5 Days | $ 2875
4
  247 Ratings

1547 Learners

Get In Touch

Are you being sponsored by your employer to take this class?
* I authorize Microtek Learning to contact me via Phone/Email