- 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
Share With Your Friends!
Twitter
LinkedIn