SQL Server 70-767: Implementing a Data Warehouse Using SQL

SQL Server 70-767: Implementing a Data Warehouse Using SQL

by Scot J. Reagin
Released July 2018
Publisher(s): Pearson IT Certification
ISBN: 0135224772

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

8+ Hours of Video Instruction

Prepare for Microsoft Exam 70-767: Implementing a Data Warehouse Using SQL, and develop practical skills to design, configure, and manage a data warehouse with SQL Server

Overview

In this video training, Scot Reagin couples discussion with demonstration to give you not only the technical and procedural aspects of implementing a data warehouse, but also a basis to make design choices that will make your job more efficient and effective.

About the Instructor

Scot Reagin’s technology career spans the entire history of what we now think of as Business Intelligence. Starting with software he developed for mini-computers in the early 1980s to the first Cube technologies of the 1990s and Self-Service BI and contemporary Predictive Analytics, Scot has consistently designed, developed, and delivered solutions for organizations across the evolving BI marketplace.

Scot is a TDWI Certified Business Intelligence Professional and Board Member of the Colorado Data Warehouse Institute. He is also a Certified Data Vault Data Modeler and active member of the Genesee Academy. Scot is the author of Microsoft 70-768: Developing SQL Data Models LiveLessons (Video Training).

Skill Level

  • Intermediate
Learn How To
  • Design and implement dimension tables and fact tables
  • Design Rowstore and Columnstore indexes
  • Design storage for a data solution
  • Implement SSIS packages
Who Should Take This Course
  • IT professionals responsible for designing, implementing, or administering SQL Server data warehouses
  • BI developers pursuing Microsoft Certification
Course Requirements
  • Basic understanding of extract, transform, and load (ETL) and data warehouses
  • Familiarity with Microsoft SQL Server client tools

About Pearson Video Training

Pearson publishes expert-led video tutorials covering a wide selection of technology topics designed to teach you the skills you need to succeed. These professional and personal technology videos feature world-leading author instructors published by your trusted technology brands: Addison-Wesley, Cisco Press, Pearson IT Certification, Prentice Hall, Sams, and Que Topics include: IT Certification, Network Security, Cisco Technology, Programming, Web Development, Mobile Development, and more. Learn more about Pearson Video training at http://www.informit.com/video.

Table of contents

  1. Introduction
    1. SQL Server 70-767: Introduction
  2. Lesson 1: Design and Implement Dimension Tables
    1. Learning objectives
    2. 1.1 Design Hierarchies
    3. 1.2 Implementing Dimension Tables
    4. 1.3 Determine keys and key relationships
    5. 1.4 Design and maintain slowly changing dimensions
    6. 1.5 Implement auditing and lineage
  3. Lesson 2: Design and Implement Fact Tables
    1. Learning objectives
    2. 2.1 Identify measures
    3. 2.2 Identify dimension table relationships
    4. 2.3 Design many-to-many relationships
  4. Lesson 3: Design an Indexed Solution
    1. Learning objectives
    2. 3.1 Design Rowstore indexes
    3. 3.2 Design Columnstore indexes
  5. Lesson 4: Design a Storage Solution
    1. Learning objectives
    2. 4.1 Configure hardware and file layout
    3. 4.2 Design partitions
  6. Lesson 5: Design and Implement SSIS Packages
    1. Learning objectives
    2. 5.1 Implement an SSIS control flow
    3. 5.2 Implement an SSIS control flow - Demo
    4. 5.3 Implement an SSIS data flow
    5. 5.4 Implement an SSIS data flow - Demo
    6. 5.5 Implement transformations
    7. 5.6 Create variables and parameters
  7. Lesson 6: Design Advanced SSIS Packages
    1. Learning objectives
    2. 6.1 Implement fuzzy grouping and fuzzy lookup
    3. 6.2 Implement checkpoints
    4. 6.3 Implement data profiling
  8. Lesson 7: Design for Incremental Data Management
    1. Learning objectives
    2. 7.1 Configure SQL Server to use Change Data Capture
    3. 7.2 Using Change Data Capture
  9. Lesson 8: Deploy and Administer SSIS Package and Projects
    1. Learning objectives
    2. 8.1 Diagnose errors using the debugger
    3. 8.2 Deploy and configure packages
    4. 8.3 Execute packages
  10. Lesson 9: Create a Knowledge Base with Data Quality Services
    1. Learning objectives
    2. 9.1 Implement Data Quality Services
    3. 9.2 Configure Data Quality Services
    4. 9.3 Implement Data Quality Services in SSIS
  11. Lesson 10: Manage Data with Master Data Services
    1. Learning objectives
    2. 10.1 Implement Master Data Services
    3. 10.2 Configure Master Data Services
    4. 10.3 Administer Master Data Services
  12. Summary
    1. SQL Server 70-767: Summary

Product information

  • Title: SQL Server 70-767: Implementing a Data Warehouse Using SQL
  • Author(s): Scot J. Reagin
  • Release date: July 2018
  • Publisher(s): Pearson IT Certification
  • ISBN: 0135224772