Microsoft® SQL Server® 2012: Data Warehouse Implementation (Exam 70-463)

Course Description

Overview:

You have created databases and performed database administration tasks. As business expands, organizations build data warehouses to manage the huge volumes of data and obtain strategic information from it. To meet these requirements, you will create dimension tables and fact tables and implement them in a data warehouse in SQL Server 2012.

You will also perform ETL operations by creating SSIS packages, managing a control flow and data flow, executing packages, and troubleshooting errors. To ensure the smooth functioning of the ETL operations, you will manage SSIS packages and secure them. In addition to these tasks, you will work with master data and access databases in the Windows Azure cloud platform. By familiarizing yourself with these tasks, you can implement a data warehouse in your organization that suits the requirements of your business. In this course, you will implement a data warehouse.

Course Objectives:

Upon successful completion of this course, students will be able to implement data warehouse in Microsoft SQL Server 2012. They will:

Identify the Basic Features of Data Warehouses and Data Warehousing Concepts
Create Dimensions and Change Granularity of Dimensions
Create Fact Tables and ColumnStore Indexes
Implement Data Warehouses in SQL Server 2012
Work with Integration Services
Manage Control Flow
Work with Dynamic Variables
Implement Data Flow
Manage Data Flow Capabilities
Manage SSIS Packages Execution
Debug and Troubleshoot Packages
Deploy Packages
Secure Packages and Databases
Work with MDS and Windows Azure

Target Student:

This course is designed for Extract Transform Load (ETL) and Data Warehouse developers who are already handling the creation and implementation of Business Intelligence (BI) solutions such as data cleansing, ETL, and implementing Data Warehouse. This course is also intended for database professionals who aspire to become a BI developer and want to gain hands on experience on creating BI solutions.

The primary responsibilities of the students may include the following:

Implement a data warehouse
Develop SQL Server Integration Services (SSIS) packages for data extraction, transformation, and loading (ETL)
Synchronize data, migrate data, and manipulate and transform ad hoc data
Enforce data integrity using Master Data Services
Cleanse data using Data Quality Services
Migrate application or one-time data
Schedule ETL jobs
Manage, configure, and deploy SQL Server Integration Services (SSIS) packages

This course is also for students who are seeking the Microsoft Certified Solutions Associate (MCSA) certification and who want to take the exam 70-463: Implementing a Data Warehouse with Microsoft SQL Server 2012 as a stand-alone, or as part of the requirement for the MCSA: SQL Server 2012; MCSE: Data Platform; MCSE: Business Intelligence certifications.

Prerequisites:

To ensure your success in this course, you should have:

A minimum of two years of experience in administering relational databases
Designed a normalized database
Created tables and relationships
Used query with Transact-SQL
Basic knowledge on key business priorities such as revenue, profitability, and financial accounting
Basic Knowledge in programming constructs such as looping and branching

Relational Database Design Courses
Microsoft SQL Server 2012: Database Querying (Exam 70-461)
Microsoft SQL Server 2012: Database Administration (Exam 70-462)

Course-specific Technical Requirements

Hardware

For this course, you will need one computer for each student and one for the instructor. Each computer will need the following minimum hardware configurations:

Processor: 1.4 GHz 64-bit processor
Hard disk: 500 GB or above
RAM: 8 GB or above
Network adapter: Compatible with Windows Server 2012
DVD-ROM drive
Keyboard and mouse (or other pointing device)
1024 x 768 resolution monitor recommended
Projection system to display the instructor's computer screen

Software

Microsoft® Windows Server® 2012 Standard Edition 64-bit operating system
Microsoft® SQL Server® 2012 Enterprise Edition 64-bit
Microsoft® Office® 2010
Microsoft® Silverlight® 64-bit

Course Content

Lesson 1: Introduction to Data Warehouses

Topic A: Overview of Data Warehouses

Topic B: Examine Data Warehousing Concepts

Topic C: Introduction to Data Analysis and Reporting Concepts

Lesson 2: Creating Dimensions and Changing Granularity of Dimensions

Topic A: Create Dimensions

Topic B: Change Granularity

Lesson 3: Creating Fact Tables and Columnstore Indexes

Topic A: Create Fact Tables

Topic B: Create Columnstore Indexes

Lesson 4: Implementing Data Warehouses in SQL Server 2012

Topic A: Overview of Data Warehouse Components

Topic B: Install Integration Services

Lesson 5: Working with Integration Services

Topic A: Create an SSIS Project

Topic B: Add Packages to an SSIS Project

Lesson 6: Managing Control Flow

Topic A: Deploy a Control Flow

Topic B: Execute Packages from Different Control Flow

Lesson 7: Working with Dynamic Variables

Topic A: Create Expressions with Variables

Topic B: Create Package Templates

Lesson 8: Implementing Data Flow

Topic A: Add Data Flow Components to a Data Flow Task

Topic B: Perform Transformation Tasks

Topic C: Track Changed Data

Lesson 9: Managing Data Flow

Topic A: Add Script Tasks and Script Components to an SSIS Package

Topic B: Profile OLTP Data

Topic C: Cleanse Data Using DQS

Lesson 10: Managing SSIS Package Execution

Topic A: Execute SSIS Packages on the Server

Topic B: Configure the Package Execution Strategy

Lesson 11: Debugging and Troubleshooting Packages

Topic A: Debug Package Execution Components

Topic B: Troubleshoot Package Failures

Lesson 12: Deploying Packages

Topic A: Deploy SSIS Packages

Topic B: Examine MSI Packages

Lesson 13: Securing Packages and Databases

Topic A: Secure Packages

Topic B: Secure Databases

Lesson 14: Working with MDS and Windows Azure

Topic A: Configure MDS

Topic B: Work with MDS Databases

Topic C: Work with Windows Azure