Schedule or Request Class

Course Duration: 5 days

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.

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.

You 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
  • 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

Prerequisites:

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

Course Content

Introduction to Data Warehouses

  • Overview of Data Warehouses
  • Examine Data Warehousing Concepts
  • Introduction to Data Analysis and Reporting Concepts

Creating Dimensions and Changing Granularity of Dimensions

  • Create Dimensions
  • Change Granularity

Creating Fact Tables and Columnstore Indexes

  • Create Fact Tables
  • Create Columnstore Indexes

Implementing Data Warehouses in SQL Server

  • Overview of Data Warehouse Components
  • Install Integration Services

Working with Integration Services

  • Create an SSIS Project
  • Add Packages to an SSIS Project

Managing Control Flow

  • Deploy a Control Flow
  • Execute Packages from Different Control Flow

Working with Dynamic Variables

  • Create Expressions with Variables
  • Create Package Templates

Implementing Data Flow

  • Add Data Flow Components to a Data Flow Task
  • Perform Transformation Tasks
  • Track Changed Data

Managing Data Flow

  • Add Script Tasks and Script Components to an SSIS Package
  • Profile OLTP Data
  • Cleanse Data Using DQS

Managing SSIS Package Execution

  • Execute SSIS Packages on the Server
  • Configure the Package Execution Strategy

Debugging and Troubleshooting Packages

  • Debug Package Execution Components
  • Troubleshoot Package Failures

Deploying Packages

  • Deploy SSIS Packages
  • Examine MSI Packages

Securing Packages and Databases

  • Secure Packages
  • Secure Databases

Working with MDS and Windows Azure

  • Configure MDS
  • Work with MDS Databases
  • Work with Windows Azure

 

You may also be interested in:

MS DP 203 Data Engineering on Microsoft Azure

DP 100 Designing and Implementing a Data Science Solution on Azure

MS PL 300 Microsoft Power BI Data Analyst

SQL for Data Analysis