Microsoft 70-463: Implementing a Data Warehouse with SQL Server

Microsoft 70-463: Implementing a Data Warehouse with SQL Server is a self-paced course on building a data warehouse in SQL Server 2012 and loading it with SQL Server Integration Services (SSIS). It follows the Microsoft 70-463 exam and is aimed at ETL and data warehouse developers. You get 7 hours 3 minutes of training across 69 videos, plus 44 test questions.

What you will learn

• Explain how a data warehouse supports business intelligence
• Design dimensions and change their granularity
• Create fact tables and columnstore indexes
• Build a data warehouse in SQL Server 2012
• Create SSIS packages and manage control flow
• Use dynamic variables in packages
• Build and manage data flows for ETL
• Run, debug and troubleshoot SSIS packages
• Deploy and secure packages and databases
• Clean and manage data with Master Data Services

Course outline

1. Introduction to Data Warehouse: what a warehouse is and how it is used
2. Creating Dimensions and Changing Granularity of Dimensions
3. Creating Fact Tables and ColumnStore Indexes
4. Implementing Data Warehouse in SQL Server 2012
5. Working with Integration Services: SSIS basics
6. Managing Control Flow: tasks and containers
7. Working with Dynamic Variables: making packages flexible
8. Implementing Data Flow: sources, transforms and destinations
9. Managing Data Flow: tuning and handling data
10. Managing SSIS Package Execution: running and scheduling packages
11. Debugging and Troubleshooting: finding and fixing errors
12. Deploying Packages: moving packages to servers
13. Securing Packages and Databases
14. Working with MDS and Windows: Master Data Services

The course starts with data warehouse design, covering dimensions, fact tables and columnstore indexes, and builds a warehouse in SQL Server 2012. It then moves into SSIS, where you manage control flow, use variables and build data flows. The final lessons cover running, debugging, deploying and securing packages, plus Master Data Services.

Who this course is for

• ETL developers who move data into warehouses
• Data warehouse developers building BI solutions
• Database administrators who support reporting systems
• SQL developers moving into business intelligence

Before you start

No formal prerequisites are listed. You should be comfortable writing T-SQL queries, for example after our Microsoft 70-461 course, as the warehouse and ETL work builds on them.

The course is made up of 69 video lessons with a total running time of 7 hours 3 minutes. You can watch at your own pace, pause and replay any lesson, and come back to a topic whenever you need it. When you finish, use the 44 test questions to check what you have learned and spot any areas to review.

About the 70-463 exam

Microsoft exam 70-463, Implementing a Data Warehouse with Microsoft SQL Server 2012/2014, tested your ability to design and build a data warehouse, create ETL processes with SSIS, and clean data with Data Quality Services and Master Data Services. Microsoft retired it on 31 January 2021 along with the other MCSA, MCSD and MCSE exams. SSIS and warehouse design are still widely used in SQL Server BI work.

Jobs this course supports

• ETL developer
• Data warehouse developer
• Business intelligence developer
• Database administrator