Table Of Content

Exam Ref 70-767 Implementing 7E Implementing Prepare for Microsoft Exam 70-767—and help demonstrate 0x your real-world mastery of skills for managing data warehouses. a SQL Data Ware7hoEuse -7am This exam is intended for Extract, Transform, Load (ETL) data 0x 6 warehouse developers who create business intelligence (BI) About the Exam -7am 7 R solutions. Their responsibilities include data cleansing as well as Exam 70-767 focuses on ski6lls and e a SQL Data ETL and data warehouse implementation. The reader should have knowledge required for work7ing w Rith f experience installing and implementing a Master Data Services relational database technology. e f (MDS) model, using MDS tools, and creating a Master Data Manager About Microsoft Certification Im database and web application. The reader should understand Passing this exam earns you credImit toward p how to design and implement ETL control flow elements and a Microsoft Certified Professionalp (M CP) or le work with a SQL Service Integration Services package. Microsoft Certified Solutions Aslesociate m Warehouse (MCSA) certification that demonstmrates your e mastery of data warehouse maneagement n Focus on the expertise measured by n ti t n these objectives: Passing this exam as well as Examin 70-768 g (Developing SQL Data Models) egarns • Design, and implement, and maintain a data warehouse ySooulu ctrieodnist tAoswsaorcdia at eM (iMcrCoSsAof) tS C a SQeLr t2i0fi1e6d a S • Extract, transform, and load data Business Intelligence (BI) DeveQlopment Q L L • Build data quality solutions certification. D D a See full details at: t a a t This Microsoft Exam Ref: microsoft.com/learning a W W a • Organizes its coverage by exam objectives About the Authors re a • Features strategic, what-if scenarios to challenge you Jose Chinchilla is the Presidenth of re o • A ssumes you have working knowledge of relational database Agile Bay. He writes and speaks uabout h technology and incremental database extraction, as well SdQatLa S weravreerh aodumsiinngis,t rbautisoinn easnsd i npteerselfloigrmenacnec,e , ou Exam Ref 70-767 as experience with designing ETL control flows, using and predictive analytics and big data. se debugging SSIS packages, accessing and importing or exporting Raj Uchhana is an enterprise data UC data from multiple sources, and managing a SQL data warehouse. architect specializing in busineschs ihinntelli- gence, enterprise data warehouhasinchg, and UC Mat i:c rRoasj@ofBt’su sPinoewsesrIn BtIe. lRligaje cnacne Nboenaw reilla .caocmhe d chhhinc ah For details on requirements for taking Exam 70-767, go to nailla https://www.microsoft.com/en-us/learning/exam-70-767.aspx MicrosoftPressStore.com U.S.A. $39.99 Canada $49.99 ISBN-13: 978-1-5093-0647-3 [Recommended] ISBN-10: 1-5093-0647-1 Jose Chinchilla Certification/SQL Server 2016 5 3 9 9 9 Raj Uchhana 9 781509 306473 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 [email protected] or (800) 382-3419. For government sales inquiries, please contact [email protected]. For questions about sales outside the U.S., please contact [email protected]. Editor-in-Chief Greg Wiegand Acquisitions Editor Trina MacDonald Development Editor Troy Mott Managing Editor Sandra Schroeder Senior Project Editor Tracey Croom Editorial Production Backstop Media Copy Editor Christina Rudloff Indexer Julie Grady Proofreader Christina Rudloff Technical Editor Martin ‘MC’ Brown Cover Designer 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 xi Important: How to use this book to study for the exam xv CHAPTER 1 Design and implement a data warehouse 1 CHAPTER 2 Extract, transform, and load data 79 CHAPTER 3 Build data quality solutions 203 Index 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 Determine attributes 2 Design shared and conformed dimensions 7 Design hierarchies 8 Determine dimension keys and key relationships for a data warehouse 11 Determine star or snowflake schema requirements 14 Determine auditing or lineage requirements 16 Implement data lineage of a dimension table 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 Skill 1.2 Design and implement fact tables ..........................18 Identify measures 18 Design and implement fact tables 19 Implement additive, semi-additive, and non-additive measures 21 Identify dimension table relationships 21 Skill 1.3 Design and implement indexes for a data warehouse workload ..24 Design an indexing solution 24 Implement clustered, nonclustered, filtered, and columnstore indexes 28 Select appropriate indexes 33 Skill 1.4 Design storage for a data warehouse ........................40 Design an appropriate storage solution, including hardware, disk, and file layout 41 Skill 1.5 Design and implement partitioned tables and views ..........47 Design a partition structure to support a data warehouse 48 Implement sliding windows 58 Implement partition elimination 64 Design a partition structure that supports the quick loading and scale-out of data 67 Thought experiment ..............................................73 Thought experiment answer .......................................74 Chapter summary ................................................77 chapter 2 Extract, transform, and load data 79 Skill 2.1: Design and implement an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package ................................................80 Understanding new terminologies 80 Design and implement ETL control flow elements, including containers, tasks, and precedence constraints 83 Create variables and parameters 104 Create checkpoints, sequence and loop containers, and variables in SSIS 107 viii Contents Implement data profiling, parallelism, transactions, logging, and security 115 Skill 2.2: Design and implement an ETL data flow by using an SSIS package ..............................................123 Implement slowly changing dimension, fuzzy grouping, fuzzy lookup, audit, blocking, non-blocking, and term lookup |transformations 123 Data flow source and destination column mapping 143 Determine appropriate scenarios for Transact-SQL joins versus SSIS lookup 144 Skill 2.3: Implement an ETL solution that supports incremental data extraction ...............................................145 Desgin fact table patterns 146 Enable Change Data Capture 147 Create a SQL MERGE statement 148 Skill 2.4 Implement an ETL solution that supports incremental data loading .................................................149 Design a control flow to load change data 149 Load data by using Transact-SQL Change Data Capture functions 151 Load data by using Change Data Capture in SSIS 155 Skill 2.5: Debug SSIS packages ....................................159 Fix performance, connectivity, execution, and failed logic issues by using the debugger 160 Add data viewers 169 Implement breakpoints 171 Enable logging for package execution 174 Implement error handling for data types 178 Profile data with different tools 181 Error handling at package level 183 Skill 2.6: Deploy and configure SSIS packages and projects ...........185 Create an SSIS catalog 185 Deploy packages by using the deployment utility, SQL Server, and file systems 189 Run and customize packages by using DTUTIL 198 Contents ix