WIXLIB

How to accessyour CD filesThe print edition of this book includes a CD. To access theCD files, go to http://aka.ms/666092/files, and look for theDownloads tab.Note: Use a desktop web browser, as files may not beaccessible from all ereader devices.Questions? Please contact: mspinput@microsoft.comMicrosoft Press

Exam 70-463: Implementing a Data Warehousewith Microsoft SQL Server 2012OBJECTIVECHAPTERLESSON1. DESIGN AND IMPLEMENT A DATA WAREHOUSE1.1 Design and implement dimensions.Chapter 1Lessons 1 and, 21.2 Design and implement fact tables.Chapter 2Chapter 1Lessons 1, 2, and 3Lesson 3Chapter 2Lessons 1, 2, and 32. EXTRACT AND TRANSFORM DATA2.1 Define connection managers.2.2 Design data flow.2.3 Implement data flow.2.4 Manage SSIS package execution.2.5 Implement script tasks in SSIS.3. LOAD DATA3.1 Design control flow.3.2 Implement package logic by using SSIS variables andparameters.3.3 Implement control flow.3.4 Implement data load options.3.5 Implement script components in SSIS.TK70463 objective card.indd iChapter 3Lessons 1 and 3Chapter 4Lesson 1Chapter 9Chapter 3Lesson 2Lesson 1Chapter 5Lessons 1, 2, and 3Chapter 7Lesson 1Chapter 10Lesson 2Chapter 13Lesson 2Chapter 18Lessons 1, 2, and 3Chapter 19Lesson 2Chapter 20Chapter 3Lesson 1Lesson 1Chapter 5Lessons 1, 2, and 3Chapter 7Lessons 1 and 3Chapter 13Lesson 1 and 2Chapter 18Lesson 1Chapter 20Chapter 8Lessons 2 and 3Lessons 1 and 2Chapter 12Chapter 19Lesson 1Lesson 1Chapter 3Lessons 2 and 3Chapter 4Lessons 2 and 3Chapter 6Lessons 1 and 3Chapter 8Lessons 1, 2, and 3Chapter 10Lesson 1Chapter 12Lesson 1 and 2Chapter 19Chapter 6Lesson 1Lessons 1 and 2Chapter 9Chapter 4Lessons 1 and 2Lessons 2 and 3Chapter 6Lesson 3Chapter 8Lessons 1 and 2Chapter 10Lesson 3Chapter 13Chapter 7Chapter 19Lessons 1, 2, and 3Lesson 2Lesson 29/30/14 10:11 AM

OBJECTIVECHAPTERLESSON4. CONFIGURE AND DEPLOY SSIS SOLUTIONS4.1 Troubleshoot data integration issues.Chapter 10Lesson 14.2 Install and maintain SSIS components.4.3 Implement auditing, logging, and event handling.Chapter 13Chapter 11Chapter 8Lessons 1, 2, and 3Lesson 1Lesson 34.4 Deploy SSIS solutions.Chapter 10Chapter 11Lessons 1 and 2Lessons 1 and 2Chapter 19Chapter 12Lesson 3Lessons 1 and 2Chapter 14Chapter 15Lessons 1, 2, and 3Lessons 1, 2, and 3Chapter 16Chapter 14Lessons 1, 2, and 3Lesson 1Chapter 17Lessons 1, 2, and 3Chapter 20Lessons 1 and 24.5 Configure SSIS security settings.5. BUILD DATA QUALITY SOLUTIONS5.1 Install and maintain Data Quality Services.5.2 Implement master data management solutions.5.3 Create a data quality project to clean data.Exam Objectives The exam objectives listed here are current as of this book’s publication date. Exam objectivesare subject to change at any time without prior notice and at Microsoft’s sole discretion. Please visit the MicrosoftLearning website for the most current listing of exam objectives: ID 70-463&locale en-us.TK70463 objective card.indd ii9/30/14 10:11 AM

Exam 70-463:Implementing a DataWarehouse withMicrosoft SQL Server2012 Training KitDejan SarkaMatija LahGrega Jerkič

Copyright 2012 by SolidQuality Europe GmbHAll rights reserved. No part of the contents of this book may be reproducedor transmitted in any form or by any means without the written permission ofthe publisher.ISBN: 978-0-7356-6609-2Fifth Printing: October 2014Printed and bound in the United States of America.Microsoft Press books are available through booksellers and distributorsworldwide. If you need support related to this book, email Microsoft PressBook Support at mspinput@microsoft.com. Please tell us what you think ofthis book at soft and the trademarks listed at ctualProperty/Trademarks/EN-US.aspx are trademarks of theMicrosoft group of companies. All other marks are property of their respective owners.The example companies, organizations, products, domain names, email addresses, logos, people, places, and events depicted herein are fictitious. Noassociation with any real company, organization, product, domain name,email address, logo, person, place, or event is intended or should be inferred.This book expresses the author’s views and opinions. The information contained in this book is provided without any express, statutory, or impliedwarranties. Neither the authors, Microsoft Corporation,nor its resellers, or distributors will be held liable for any damages caused oralleged to be caused either directly or indirectly by this book.Acquisitions and Developmental Editor: Russell JonesProduction Editor: Holly BauerEditorial Production: Online Training Solutions, Inc.Technical Reviewer: Miloš RadivojevićCopyeditor: Kathy Krause, Online Training Solutions, Inc.Indexer: Ginny Munroe, Judith McConvilleCover Design: Twist Creative SeattleCover Composition: Zyg Group, LLCIllustrator: Jeanne Craver, Online Training Solutions, Inc.tk70463 copyright.indd iv9/30/14 9:35 AM

Contents at a GlanceIntroductionxxviiPart IDESIGNING AND IMPLEMENTING A DATA WAREHOUSEChapter 1Data Warehouse Logical Design3Chapter 2Implementing a Data Warehouse41Part IIDEVELOPING SSIS PACKAGESChapter 3Creating SSIS PackagesChapter 4Designing and Implementing Control Flow131Chapter 5Designing and Implementing Data Flow177Part IIIENHANCING SSIS PACKAGESChapter 6Enhancing Control Flow239Chapter 7Enhancing Data Flow283Chapter 8Creating a Robust and Restartable Package327Chapter 9Implementing Dynamic Packages353Chapter 10Auditing and Logging381Part IVMANAGING AND MAINTAINING SSIS PACKAGESChapter 11Installing SSIS and Deploying PackagesChapter 12Executing and Securing Packages455Chapter 13Troubleshooting and Performance Tuning497Part VBUILDING DATA QUALITY SOLUTIONSChapter 14Installing and Maintaining Data Quality Services529Chapter 15Implementing Master Data Services565Chapter 16Managing Master Data605Chapter 17Creating a Data Quality Project to Clean Data63787421

Part VIADVANCED SSIS AND DATA QUALITY TOPICSChapter 18SSIS and Data Mining667Chapter 19Implementing Custom Code in SSIS Packages699Chapter 20Identity Mapping and De-Duplicating735Index769

ContentsIntroductionxxviiSystem RequirementsxxviiiUsing the Companion CDxxixAcknowledgmentsxxxiSupport & FeedbackxxxiPreparing for the ExamxxxiiiPart IDESIGNING AND IMPLEMENTING A DATA WAREHOUSEChapter 1Data Warehouse Logical Design3Before You Begin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4Lesson 1: Introducing Star and Snowflake Schemas. . . . . . . . . . . . . . . . . . . . 4Reporting Problems with a Normalized Schema5Star Schema7Snowflake Schema9Granularity Level12Auditing and Lineage13Lesson Summary16Lesson Review16Lesson 2: Designing Dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17Dimension Column Types17Hierarchies19Slowly Changing Dimensions21Lesson Summary26Lesson Review26What do you think of this book? We want to hear from you!Microsoft is interested in hearing your feedback so we can continually improve ourbooks and learning resources for you. To participate in a brief online survey, please visit:www.microsoft.com/learning/booksurvey/vii

Lesson 3: Designing Fact Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27Fact Table Column Types28Additivity of Measures29Additivity of Measures in SSAS30Many-to-Many Relationships30Lesson Summary33Lesson Review34Case Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34Case Scenario 1: A Quick POC Project34Case Scenario 2: Extending the POC Project35Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35Analyze the AdventureWorksDW2012 Database Thoroughly35Check the SCD and Lineage in the AdventureWorksDW2012 Database36Answers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37Chapter 2Lesson 137Lesson 237Lesson 338Case Scenario 139Case Scenario 239Implementing a Data Warehouse41Before You Begin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42Lesson 1: Implementing Dimensions and Fact Tables . . . . . . . . . . . . . . . . . 42Creating a Data Warehouse Database42Implementing Dimensions45Implementing Fact Tables47Lesson Summary54Lesson Review54Lesson 2: Managing the Performance of a Data Warehouse. . . . . . . . . . . 55viiiContentsIndexing Dimensions and Fact Tables56Indexed Views58Data Compression61Columnstore Indexes and Batch Processing62

Lesson Summary69Lesson Review70Lesson 3: Loading and Auditing Loads. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 70Using Partitions71Data Lineage73Lesson Summary78Lesson Review78Case Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78Case Scenario 1: Slow DW Reports79Case Scenario 2: DW Administration Problems79Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79Test Different Indexing Methods79Test Table Partitioning80Answers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81Lesson 181Lesson 281Lesson 382Case Scenario 183Case Scenario 283Part IIDEVELOPING SSIS PACKAGESChapter 3Creating SSIS Packages87Before You Begin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89Lesson 1: Using the SQL Server Import and Export Wizard . . . . . . . . . . . . 89Planning a Simple Data Movement89Lesson Summary99Lesson Review99Lesson 2: Developing SSIS Packages in SSDT. . . . . . . . . . . . . . . . . . . . . . . . 101Introducing SSDT102Lesson Summary107Lesson Review108Lesson 3: Introducing Control Flow, Data Flow, andConnection Managers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109Contentsix

Introducing SSIS Development110Introducing SSIS Project Deployment110Lesson Summary124Lesson Review124Case Scenarios. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125Case Scenario 1: Copying Production Data to Development125Case Scenario 2: Connection Manager Parameterization125Suggested Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125Use the Right Tool125Account for the Differences Between Development andProduction Environments126Answers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127Chapter 4Lesson 1127Lesson 2128Lesson 3128Case Scenario 1129Case Scenario 2129Designing and Implementing Control Flow131Before You Begin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132Lesson 1: Connection Managers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133Lesson Summary144Lesson Review144Lesson 2: Control Flow Tasks and Containers . . . . . . . . . . . . . . . . . . . . . . . 145Planning a Complex Data Movement145Tasks147Containers155Lesson Summary163Lesson Review163Lesson 3: Precedence Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164xContentsLesson Summary169Lesson Review169