20767: Implementing a SQL 2016 Data Warehouse

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

• Querying Data with Transact-SQL (20761)

• Describe the key elements of a data warehousing solution
• Describe the main hardware consideration 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 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 Maser Data Services model
• Describe how you can use custom components to extend SSIS
• Deploy SSIS projects
• Describe BI and common BI scenarios

• Database professionals who need to fulfill a BI developer role focused on hands-on work by creating BI solutions, including data warehouse implementation, ETL and data cleansing
• Database professionals responsible for implementing a data warehouse, developing SSIS packages for data ETL, enforcing data integrity using Microsoft Data Services and cleansing data using Data Quality Services

1. Introduction to Data Warehousing

• Overview of Data Warehousing
• Considerations for a Data Warehouse Solution

2. Planning Data Warehouse Infrastructure

• Considerations for Building a Data Warehouse
• Planning data warehouse hardware

3. Designing and Implementing a Data Warehouse

• Data warehouse design overview
• Designing dimension tables
• Designing fact tables
• Physical Design for a Data Warehouse

4. Columnstore Indexes

• Introduction to Columnstore Indexes
• Creating Columnstore Indexes
• Working with Columnstore Indexes

5. Implementing an Azure SQL Data Warehouse

• 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 Azue data factory

6. Creating an ETL Solution

• Introduction ETL with SSIS
• Exploring Source Data
• Implementing Data Flow

7. Implementing Control Flow in an SSIS Package

• Introduction to Control Flow
• Creating Dynamic Packages
• Using Containers
• Managing consistency

8. Debugging and Troubleshooting SSIS Packages

• Debugging an SSIS Package
• Logging SSIS Package Events
• Handling Errors in Data Flow

9. Implementing a Data Extraction Solution

• Introduction to Incremental ETL
• Extracting Modified Data
• Loading Modified Data
• Temporal Tables

10. Enforcing Data Quality

• Introduction to Data Quality
• Using Data Quality Services to Cleanse Data
• Using Data Quality Services to Match Data

11. Using Master Data Services

• Introduction to Master Data Services Concepts
• Implementing a Master Data Services Model
• Hierarchies and Collections
• Creating a Master Data Hub

12. Extending SQL Server Integration Services (SSIS)

• Using Custom Components in SSIS
• Using Scripting in SSIS

13. Deploying and Configuring SSIS Packages

• Overview of SSIS Deployment
• Deploying SSIS Projects
• Planning SSIS Package Execution

14. Consuming Data in a Data Warehouse

• Introduction to Business Intelligence
• An Introduction to Data Analysis
• Introduction to Reporting
• Analyzing Data with Azure SQL Data Warehouse