O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Exam Ref 70-767 Implementing a SQL Data Warehouse

Book Description

Prepare for Microsoft Exam 70-767–and help demonstrate your real-world mastery of skills for managing data warehouses. This exam is intended for Extract, Transform, Load (ETL) data warehouse developers who create business intelligence (BI) solutions. Their responsibilities include data cleansing as well as ETL and data warehouse implementation. The reader should have experience installing and implementing a Master Data Services (MDS) model, using MDS tools, and creating a Master Data Manager database and web application. The reader should understand how to design and implement ETL control flow elements and work with a SQL Service Integration Services package.

 

Focus on the expertise measured by these objectives:

•  Design, and implement, and maintain a data warehouse

•  Extract, transform, and load data

•  Build data quality solutionsThis Microsoft Exam Ref:

•  Organizes its coverage by exam objectives

•  Features strategic, what-if scenarios to challenge you

•  Assumes you have working knowledge of relational database technology and incremental database extraction, as well as experience with designing ETL control flows, using and debugging SSIS packages, accessing and importing or exporting data from multiple sources, and managing a SQL data warehouse.

 

Implementing a SQL Data Warehouse

About the Exam

Exam 70-767 focuses on skills and knowledge required for working with relational database technology.

 

About Microsoft Certification

Passing this exam earns you credit toward a Microsoft Certified Professional (MCP) or Microsoft Certified Solutions Associate (MCSA) certification that demonstrates your mastery of data warehouse management

 

Passing this exam as well as Exam 70-768 (Developing SQL Data Models) earns you credit toward a Microsoft Certified Solutions Associate (MCSA) SQL 2016 Business Intelligence (BI) Development certification.

 

See full details at: microsoft.com/learning

Table of Contents

  1. Cover Page
  2. Title Page
  3. Copyright Page
  4. Dedication Page
  5. Contents at a Glance
  6. Contents
  7. Introduction
    1. Organization of this book
    2. Microsoft certifications
    3. Acknowledgments
    4. Microsoft Virtual Academy
    5. Quick access to online references
    6. Errata, updates, & book support
    7. We want to hear from you
    8. Stay in touch
  8. Important: How to use this book to study for the exam
  9. Chapter 1 Design and implement a data warehouse
    1. Skill 1.1 Design and implement dimension tables
      1. Determine attributes
      2. Design shared and conformed dimensions
      3. Design hierarchies
      4. Determine dimension keys and key relationships for a data warehouse
      5. Determine star or snowflake schema requirements
      6. Determine auditing or lineage requirements
      7. Implement data lineage of a dimension table
    2. Skill 1.2 Design and implement fact tables
      1. Identify measures
      2. Design and implement fact tables
      3. Implement additive, semi-additive, and non-additive measures
      4. Identify dimension table relationships
    3. Skill 1.3 Design and implement indexes for a data warehouse workload
      1. Design an indexing solution
      2. Implement clustered, nonclustered, filtered, and columnstore indexes
      3. Select appropriate indexes
    4. Skill 1.4 Design storage for a data warehouse
      1. Design an appropriate storage solution, including hardware, disk, and file layout
    5. Skill 1.5 Design and implement partitioned tables and views
      1. Design a partition structure to support a data warehouse
      2. Implement sliding windows
      3. Implement partition elimination
      4. Design a partition structure that supports the quick loading and scale-out of data
    6. Thought experiment
    7. Thought experiment answer
    8. Chapter summary
  10. Chapter 2 Extract, transform, and load data
    1. Skill 2.1: Design and implement an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package
      1. Understanding new terminologies
      2. Design and implement ETL control flow elements, including containers, tasks, and precedence constraints
      3. Create variables and parameters
      4. Create checkpoints, sequence and loop containers, and variables in SSIS
      5. Implement data profiling, parallelism, transactions, logging, and security
    2. Skill 2.2: Design and implement an ETL data flow by using an SSIS package
      1. Implement slowly changing dimension, fuzzy grouping, fuzzy lookup, audit, blocking, non-blocking, and term lookup |transformations
      2. Data flow source and destination column mapping
      3. Determine appropriate scenarios for Transact-SQL joins versus SSIS lookup
    3. Skill 2.3: Implement an ETL solution that supports incremental data extraction
      1. Desgin fact table patterns
      2. Enable Change Data Capture
      3. Create a SQL MERGE statement
    4. Skill 2.4 Implement an ETL solution that supports incremental data loading
      1. Design a control flow to load change data
      2. Load data by using Transact-SQL Change Data Capture functions
      3. Load data by using Change Data Capture in SSIS
    5. Skill 2.5: Debug SSIS packages
      1. Fix performance, connectivity, execution, and failed logic issues by using the debugger
      2. Add data viewers
      3. Implement breakpoints
      4. Enable logging for package execution
      5. Implement error handling for data types
      6. Profile data with different tools
      7. Error handling at package level
    6. Skill 2.6: Deploy and configure SSIS packages and projects
      1. Create an SSIS catalog
      2. Deploy packages by using the deployment utility, SQL Server, and file systems
      3. Run and customize packages by using DTUTIL
    7. Thought exercise
    8. Thought exercise answer
    9. Chapter summary
  11. Chapter 3 Build data quality solutions
    1. Skill 3.1 Create a knowledge base
      1. Install DQS
      2. Create a Data Quality Services (DQS) knowledge base
      3. Determine appropriate use cases for a DQS Knowledge Base
      4. Perform domain management
      5. Perform knowledge discovery
    2. Skill 3.2 Maintain data quality by using DQS
      1. Add matching knowledge to a knowledge base
      2. Create a matching policy
      3. Prepare a DQS Knowledge Base for data deduplication
      4. Clean data by using DQS knowledge
      5. Clean data by using the SSIS DQS task
    3. Skill 3.3 Implement a Master Data Services (MDS) model
      1. Install MDS
      2. Use the Master Data Services Configuration Manager
      3. Create a Master Data Services database and web application
      4. Implement MDS
      5. Create models, entities, hierarchies, collections, and attributes
      6. Define security roles
      7. Import and export data
      8. Stage and load data
      9. Create and edit a subscription
      10. Implement entities, attributes, hierarchies, and business rules
    4. Skill 3.4 Manage data by using MDS
      1. Use MDS tools
      2. Deploy a sample model using MDSModelDeploy.exe
      3. Create a Master Data Management hub
    5. Thought exercise
    6. Thought exercise answer
    7. Chapter summary
  12. Index
  13. About the authors
  14. Code Snippets