• Online, Self-Paced
Course Description

Once the business requirements are determined during the design stage of building a business intelligence solution, the data warehouse needs to be designed and then populated with the data required for analysis and reporting from the source systems. SQL Server 2014 provides an Extract, Transform, and Load (ETL) process to perform this task. This course will discuss and demonstrate how to build an ETL solution to gather the data required, transform it to conform to the destination, and the load it to the data warehouse. You will also learn how to deploy and manage the ETL packages. This course is one of a series in the Skillsoft learning path that helps individuals prepare for the Designing Business Intelligence Solutions with Microsoft SQL Server exam (70-467).

Learning Objectives

ETL Package Design

  • start the course
  • describe the common ETL data flow architectures used in a BI Project
  • describe the considerations that affect the design of data extraction from the data sources
  • describe how to minimize the load times and hardware resource utilization, and plan the extraction windows
  • identify where transformations should take place and determine which transformation should be used
  • design the ETL solution to support slowly changing dimensions (SCD)
  • design ETL solutions to support change data capture
  • handle invalid rows by redirecting the rows
  • use the Balanced Data Distributor, BDD, transformation to optimize package execution
  • determine whether to use an incremental load or a full load on the data
  • determine if aggregation operations should be completed directly in the SSIS pipeline or in the relational database
  • load partitioned fact tables using best practices for an optimal load
  • handle errors through error handling techniques and log audit information

Deploy ETL Projects

  • describe the differences between the package deployment model and the project deployment model, and project deployment model features
  • use parameters in packages, including sharing them
  • use the Integration Service Deployment Wizard to deploy projects to the SSISDB catalog using the project deployment model
  • set up a server environment to specify runtime values for packages within a project

Deploy ETL Packages

  • use package configuration files to modify the properties of package elements at run time
  • build the SSIS project to create a package deployment utility with the packages for deployment
  • copy the deployment folder and run the Package Installation Wizard to install packages to the file system or instance

Manage ETL Solutions

  • configure security on packages requiring different security requirements
  • manage the SSIS catalog, including the modifying the catalog properties
  • use catalog stored procedures and views to manage SSIS objects
  • backup and restore the SSIS catalog, including moving it

Practice: Design an ETL Solution

  • design ETL solutions, including package and project deployment and managing the ETL solution, in a scenario

Framework Connections

The materials within this course focus on the Knowledge Skills and Abilities (KSAs) identified within the Specialty Areas listed below. Click to view Specialty Area details within the interactive National Cybersecurity Workforce Framework.