Exam Ref 70-767 Implementing a SQL Data Warehouse 无水印原版pdf.pdf

发布时间：2022-06-14 发布人：admin 分类：说明书资料大小：19.26M 资料格式：pdf 举报版权申诉

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第1页.png

第1页 / 共289页

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第2页.png

第2页 / 共289页

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第3页.png

第3页 / 共289页

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第4页.png

第4页 / 共289页

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第5页.png

第5页 / 共289页

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第6页.png

第6页 / 共289页

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第7页.png

第7页 / 共289页

d87f9d51-090a-476b-8cfa-c00c4e6f37e6.pdf-第8页.png

第8页 / 共289页

Cover

Contents

Introduction

1 Design and implement a data warehouse

Skill 1.1 Design and implement dimension tables

Determine attributes

Design shared and conformed dimensions

Design hierarchies

Determine dimension keys and key relationships for a data warehouse

Determine star or snowflake schema requirements

Determine auditing or lineage requirements

Implement data lineage of a dimension table

Skill 1.2 Design and implement fact tables

Identify measures

Design and implement fact tables

Implement additive, semi-additive, and non-additive measures

Identify dimension table relationships

Skill 1.3 Design and implement indexes for a data warehouse workload

Design an indexing solution

Implement clustered, nonclustered, filtered, and columnstore indexes

Select appropriate indexes

Skill 1.4 Design storage for a data warehouse

Design an appropriate storage solution, including hardware, disk, and file layout

Skill 1.5 Design and implement partitioned tables and views

Design a partition structure to support a data warehouse

Implement sliding windows

Implement partition elimination

Design a partition structure that supports the quick loading and scale-out of data

Thought experiment

Thought experiment answer

Chapter summary

2 Extract, transform, and load data

Skill 2.1 Design and implement an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package

Understanding new terminologies

Design and implement ETL control flow elements, including containers, tasks, and precedence constraints

Create variables and parameters

Create checkpoints, sequence and loop containers, and variables in SSIS

Implement data profiling, parallelism, transactions, logging, and security

Skill 2.2 Design and implement an ETL data flow by using an SSIS package

Implement slowly changing dimension, fuzzy grouping, fuzzy lookup, audit, blocking, non-blocking, and term lookup |transformations

Data flow source and destination column mapping

Determine appropriate scenarios for Transact-SQL joins versus SSIS lookup

Skill 2.3 Implement an ETL solution that supports incremental data extraction

Desgin fact table patterns

Enable Change Data Capture

Create a SQL MERGE statement

Skill 2.4 Implement an ETL solution that supports incremental data loading

Design a control flow to load change data

Load data by using Transact-SQL Change Data Capture functions

Load data by using Change Data Capture in SSIS

Skill 2.5 Debug SSIS packages

Fix performance, connectivity, execution, and failed logic issues by using the debugger

Add data viewers

Implement breakpoints

Enable logging for package execution

Implement error handling for data types

Profile data with different tools

Error handling at package level

Skill 2.6 Deploy and configure SSIS packages and projects

Create an SSIS catalog

Deploy packages by using the deployment utility, SQL Server, and file systems

Run and customize packages by using DTUTIL

Thought exercise

Thought exercise answer

Chapter summary

3 Build data quality solutions

Skill 3.1 Create a knowledge base

Install DQS

Create a Data Quality Services (DQS) knowledge base

Determine appropriate use cases for a DQS Knowledge Base

Perform domain management

Perform knowledge discovery

Skill 3.2 Maintain data quality by using DQS

Add matching knowledge to a knowledge base

Create a matching policy

Prepare a DQS Knowledge Base for data deduplication

Clean data by using DQS knowledge

Clean data by using the SSIS DQS task

Skill 3.3 Implement a Master Data Services (MDS) model

Install MDS

Use the Master Data Services Configuration Manager

Create a Master Data Services database and web application

Implement MDS

Create models, entities, hierarchies, collections, and attributes

Define security roles

Import and export data

Stage and load data

Create and edit a subscription

Implement entities, attributes, hierarchies, and business rules

Skill 3.4 Manage data by using MDS

Use MDS tools

Deploy a sample model using MDSModelDeploy.exe

Create a Master Data Management hub

Thought exercise

Thought exercise answer

Chapter summary

Index

Exam Ref 70-767 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 solutions This 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. For details on requirements for taking Exam 70-767, go to https://www.microsoft.com/en-us/learning/exam-70-767.aspx l I 7 0 - 7 6 7 Implementing a SQL Data Warehouse E x About the Exam a m Exam 70-767 focuses on skills and knowledge required for working with R relational database technology. e f 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 m p e m e n t Passing this exam as well as Exam 70-768 i n (Developing SQL Data Models) earns g you credit toward a Microsoft Certified a Solutions Associate (MCSA) SQL 2016 S Q Business Intelligence (BI) Development L certification. D a See full details at: t a microsoft.com/learning W About the Authors a r e Jose Chinchilla is the President of h o Agile Bay. He writes and speaks about u SQL Server administration and performance, s e data warehousing, business intelligence, predictive analytics and big data. Raj Uchhana is an enterprise data architect specializing in business intelli- gence, enterprise data warehousing, and Microsoft’s Power BI. Raj can be reached at : Raj@BusinessIntelligenceNow.com U c h h a n a C h n c h a i i l l U.S.A. $39.99 Canada $49.99 [Recommended] 5 3 9 9 9 Certification/SQL Server 2016 MicrosoftPressStore.com ISBN-13: ISBN-10: 978-1-5093-0647-3 1-5093-0647-1 9 7 8 1 5 0 9 3 0 6 4 7 3 7 0 - 7 6 7 E x a m R e f I l m p e m e n t i n g a S Q L D a t a W a r e h o u s e i C h n c h i l l U c h h a n a a Implementing a SQL Data Warehouse Exam Ref 70-767 Jose Chinchilla Raj Uchhana 9781509306473_ExamRef_70-767_Implementing_SQL_Data_Warehouse_Cover.indd All Pages 10/17/17 10:27 AM

Exam Ref 70-767 Implementing a SQL Data Warehouse Jose Chinchilla Raj Uchhana

Exam Ref 70-767 Implementing a SQL Data Warehouse Published with the authorization of Microsoft Corporation by: Pearson Education, Inc. Copyright © 2018 by Pearson Education All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein. ISBN-13: 978-1-5093-0647-3 ISBN-10: 1-5093-0647-1 Library of Congress Control Number: 2017953180 1 17 Trademarks Microsoft and the trademarks listed at https://www.microsoft.com on the “Trademarks” webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners. Warning and Disclaimer Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an “as is” basis. The authors, the publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or programs accompanying it. Special Sales For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at corpsales@pearsoned.com or (800) 382-3419. For government sales inquiries, please contact governmentsales@pearsoned.com. For questions about sales outside the U.S., please contact intlcs@pearson.com. Editor-in-Chief Acquisitions Editor Development Editor Managing Editor Senior Project Editor Editorial Production Copy Editor Indexer Proofreader Technical Editor Cover Designer Greg Wiegand Trina MacDonald Troy Mott Sandra Schroeder Tracey Croom Backstop Media Christina Rudloff Julie Grady Christina Rudloff Martin ‘MC’ Brown Twist Creative, Seattle

To my wife, Dolly, for creating a dream world where every day is a beautiful adventure and anything is possible. To our daughters, Ashia and Aria, who ignite passion into the world. And being a parent, I am humbled by all that my parents, Saubhagya and Usha, have done for us. For all the love and support you have all given me, you have my undying love and admiration. —Raj Uchhana

This page intentionally left blank

Contents at a glance Introduction Important: How to use this book to study for the exam CHAPTER 1 CHAPTER 2 CHAPTER 3 Design and implement a data warehouse Extract, transform, and load data Build data quality solutions Index xi xv 1 79 203 259

This page intentionally left blank

Contents Introduction xiii Organization of this book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiii Microsoft certifications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv Microsoft Virtual Academy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv Quick access to online references . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Errata, updates, & book support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv We want to hear from you . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Stay in touch . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv Important: How to use this book to study for the exam xvii chapter 1 Design and implement a data warehouse 1 Skill 1.1 Design and implement dimension tables . . . . . . . . . . . . . . . . . . . . . 2 2 7 8 Determine attributes Design shared and conformed dimensions Design hierarchies Determine dimension keys and key relationships for a data warehouse Determine star or snowflake schema requirements Determine auditing or lineage requirements Implement data lineage of a dimension table 11 14 16 17 What do you think of this book? We want to hear from you! Microsoft is interested in hearing your feedback so we can continually improve our books and learning resources for you. To participate in a brief online survey, please visit: https://aka.ms/tellpress vii

分享到：

赞收藏

资料库

Exam Ref 70-767 Implementing a SQL Data Warehouse 无水印原版pdf.pdf

相关推荐

开发技术

热门标签

最新资料