Implementing a SQL Data Warehouse


20767C - Version:1
Description
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.
Intended audience
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.
Expand All
  • Module 1: Introduction to Data Warehousing
    • Overview of Data Warehousing
    • Considerations for a Data Warehouse Solution
    • Lab : Exploring a Data Warehouse Solution
  • Module 2: Planning Data Warehouse Infrastructure
    • Considerations for data warehouse infrastructure.
    • Planning data warehouse hardware.
    • Lab : Planning Data Warehouse Infrastructure
  • Module 3: Designing and Implementing 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
  • Module 4: Columnstore Indexes
    • Introduction to Columnstore Indexes
    • Creating Columnstore Indexes
    • Working with Columnstore Indexes
    • Lab : Using Columnstore Indexes
  • Module 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 the Azure data factory
    • Lab : Implementing an Azure SQL Data Warehouse
  • Module 6: Creating an ETL Solution
    • Introduction to ETL with SSIS
    • Exploring Source Data
    • Implementing Data Flow
    • Lab : Implementing Data Flow in an SSIS Package
  • Module 7: 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
    • Lab : Using Transactions and Checkpoints
  • Module 8: Debugging and Troubleshooting SSIS Packages
    • Debugging an SSIS Package
    • Logging SSIS Package Events
    • Handling Errors in an SSIS Package
    • Lab : Debugging and Troubleshooting an SSIS Package
  • Module 9: Implementing a Data Extraction Solution
    • Introduction to Incremental ETL
    • Extracting Modified Data
    • Loading modified data
    • Temporal Tables
    • Lab : Extracting Modified Data
    • Lab : Loading a data warehouse
  • Module 10: Enforcing Data Quality
    • Introduction to Data Quality
    • Using Data Quality Services to Cleanse Data
    • Using Data Quality Services to Match Data
    • Lab : Cleansing Data
    • Lab : De-duplicating Data
  • Module 11: Using Master Data Services
    • Introduction to Master Data Services
    • Implementing a Master Data Services Model
    • Hierarchies and collections
    • Creating a Master Data Hub
    • Lab : Implementing Master Data Services
  • Module 12: Extending SQL Server Integration Services (SSIS)
    • Using scripting in SSIS
    • Using custom components in SSIS
    • Lab : Using scripts
  • Module 13: Deploying and Configuring SSIS Packages
    • Overview of SSIS Deployment
    • Deploying SSIS Projects
    • Planning SSIS Package Execution
    • Lab : Deploying and Configuring SSIS Packages
  • Module 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
    • Lab : Using a data warehouse
  • Basic knowledge of the Microsoft Windows operating system and its core functionality.
  • Working knowledge of relational databases.
  • Some experience with database design.
  • 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