Microsoft SQL Server 2014 - Designing BI Solutions: Extract, Transform, and Load


Overview/Description
Target Audience
Prerequisites
Expected Duration
Lesson Objectives
Course Number
Expertise Level


Overview/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).

Target Audience
Database and business intelligence administrators and developers who are responsible for designing a BI infrastructure, IT professionals interested learning how to design BI infrastructures and how they're related to data systems, and individuals interested in taking the Designing Business Intelligence Solutions with Microsoft SQL Server exam (70-467)

Prerequisites
None

Expected Duration (hours)
2.9

Lesson Objectives

Microsoft SQL Server 2014 - Designing BI Solutions: Extract, Transform, and Load

  • 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
  • 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
  • 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
  • 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
  • design ETL solutions, including package and project deployment and managing the ETL solution, in a scenario

    • Course Number:
    md_dbis_a02_it_enus

    Expertise Level
    Expert