Implementing a Data Warehouse with Microsoft SQL Server 2012: Training Kit (Exam 70-463)

848 Pages·2012·41.317 MB·English

by  Dejan Sarka, Matija Lah, Grega Jerkic

www.it-ebooks.info
www.it-ebooks.info
Exam 70-463: Implementing a Data Warehouse
with Microsoft SQL Server 2012
Objective chapter LessOn
1. Design anD impLement a Data WarehOuse
1.1 Design and implement dimensions. Chapter 1 Lessons 1 and, 2
Chapter 2 Lessons 1, 2, and 3
1.2 Design and implement fact tables. Chapter 1 Lesson 3
Chapter 2 Lessons 1, 2, and 3
2. extract anD transfOrm Data
2.1 Define connection managers. Chapter 3 Lessons 1 and 3
Chapter 4 Lesson 1
Chapter 9 Lesson 2
2.2 Design data flow. Chapter 3 Lesson 1
Chapter 5 Lessons 1, 2, and 3
Chapter 7 Lesson 1
Chapter 10 Lesson 2
Chapter 13 Lesson 2
Chapter 18 Lessons 1, 2, and 3
Chapter 19 Lesson 2
Chapter 20 Lesson 1
2.3 Implement data flow. Chapter 3 Lesson 1
Chapter 5 Lessons 1, 2, and 3
Chapter 7 Lessons 1 and 3
Chapter 13 Lesson 1 and 2
Chapter 18 Lesson 1
Chapter 20 Lessons 2 and 3
2.4 Manage SSIS package execution. Chapter 8 Lessons 1 and 2
Chapter 12 Lesson 1
2.5 Implement script tasks in SSIS. Chapter 19 Lesson 1
3. LOaD Data
3.1 Design control flow. Chapter 3 Lessons 2 and 3
Chapter 4 Lessons 2 and 3
Chapter 6 Lessons 1 and 3
Chapter 8 Lessons 1, 2, and 3
Chapter 10 Lesson 1
Chapter 12 Lesson 2
Chapter 19 Lesson 1
3.2 Implement package logic by using SSIS variables and Chapter 6 Lessons 1 and 2
parameters. Chapter 9 Lessons 1 and 2
3.3 Implement control flow. Chapter 4 Lessons 2 and 3
Chapter 6 Lesson 3
Chapter 8 Lessons 1 and 2
Chapter 10 Lesson 3
Chapter 13 Lessons 1, 2, and 3
3.4 Implement data load options. Chapter 7 Lesson 2
3.5 Implement script components in SSIS. Chapter 19 Lesson 2
www.it-ebooks.info
Objective chapter LessOn
4. cOnfigure anD DepLOy ssis sOLutiOns
4.1 Troubleshoot data integration issues. Chapter 10 Lesson 1
Chapter 13 Lessons 1, 2, and 3
4.2 Install and maintain SSIS components. Chapter 11 Lesson 1
4.3 Implement auditing, logging, and event handling. Chapter 8 Lesson 3
Chapter 10 Lessons 1 and 2
4.4 Deploy SSIS solutions. Chapter 11 Lessons 1 and 2
Chapter 19 Lesson 3
4.5 Configure SSIS security settings. Chapter 12 Lesson 2
5. buiLD Data quaLity sOLutiOns
5.1 Install and maintain Data Quality Services. Chapter 14 Lessons 1, 2, and 3
5.2 Implement master data management solutions. Chapter 15 Lessons 1, 2, and 3
Chapter 16 Lessons 1, 2, and 3
5.3 Create a data quality project to clean data. Chapter 14 Lesson 1
Chapter 17 Lessons 1, 2, and 3
Chapter 20 Lessons 1 and 2
exam Objectives The exam objectives listed here are current as of this book’s publication date. Exam objectives
are subject to change at any time without prior notice and at Microsoft’s sole discretion. Please visit the Microsoft
Learning website for the most current listing of exam objectives: http://www.microsoft.com/learning/en/us
/exam.aspx?ID=70-463&locale=en-us.
www.it-ebooks.info
Exam 70-463:
Implementing a Data
Warehouse with
Microsoft SQL Server
® ®
2012
Training Kit
Dejan Sarka
Matija Lah
Grega Jerkič
www.it-ebooks.info
Published with the authorization of Microsoft Corporation by:
O’Reilly Media, Inc.
1005 Gravenstein Highway North
Sebastopol, California 95472
Copyright © 2012 by SolidQuality Europe GmbH
All rights reserved. No part of the contents of this book may be reproduced
or transmitted in any form or by any means without the written permission of
the publisher.
ISBN: 978-0-7356-6609-2
1 2 3 4 5 6 7 8 9 QG 7 6 5 4 3 2
Printed and bound in the United States of America.
Microsoft Press books are available through booksellers and distributors
worldwide. If you need support related to this book, email Microsoft Press
Book Support at [email protected]. Please tell us what you think of
this book at http://www.microsoft.com/learning/booksurvey.
Microsoft and the trademarks listed at http://www.microsoft.com/about/legal/
en/us/IntellectualProperty/Trademarks/EN-US.aspx are trademarks of the
Microsoft group of companies. All other marks are property of their respec-
tive owners.
The example companies, organizations, products, domain names, email ad-
dresses, logos, people, places, and events depicted herein are fictitious. No
association 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 con-
tained in this book is provided without any express, statutory, or implied
warranties. Neither the authors, O’Reilly Media, Inc., Microsoft Corporation,
nor its resellers, or distributors will be held liable for any damages caused or
alleged to be caused either directly or indirectly by this book.
acquisitions and Developmental editor: Russell Jones
production editor: Holly Bauer
editorial production: Online Training Solutions, Inc.
technical reviewer: Miloš Radivojević
copyeditor: Kathy Krause, Online Training Solutions, Inc.
indexer: Ginny Munroe, Judith McConville
cover Design: Twist Creative • Seattle
cover composition: Zyg Group, LLC
illustrator: Jeanne Craver, Online Training Solutions, Inc.
www.it-ebooks.info
Contents at a Glance
Introduction xxvii
part i Designing anD impLementing a Data WarehOuse
ChaptEr 1 Data Warehouse Logical Design 3
ChaptEr 2 Implementing a Data Warehouse 41
part ii DeveLOping ssis packages
ChaptEr 3 Creating SSIS packages 87
ChaptEr 4 Designing and Implementing Control Flow 131
ChaptEr 5 Designing and Implementing Data Flow 177
part iii enhancing ssis packages
ChaptEr 6 Enhancing Control Flow 239
ChaptEr 7 Enhancing Data Flow 283
ChaptEr 8 Creating a robust and restartable package 327
ChaptEr 9 Implementing Dynamic packages 353
ChaptEr 10 auditing and Logging 381
part iv managing anD maintaining ssis packages
ChaptEr 11 Installing SSIS and Deploying packages 421
ChaptEr 12 Executing and Securing packages 455
ChaptEr 13 troubleshooting and performance tuning 497
part v buiLDing Data quaLity sOLutiOns
ChaptEr 14 Installing and Maintaining Data Quality Services 529
ChaptEr 15 Implementing Master Data Services 565
ChaptEr 16 Managing Master Data 605
ChaptEr 17 Creating a Data Quality project to Clean Data 637
www.it-ebooks.info
part vi aDvanceD ssis anD Data quaLity tOpics
ChaptEr 18 SSIS and Data Mining 667
ChaptEr 19 Implementing Custom Code in SSIS packages 699
ChaptEr 20 Identity Mapping and De-Duplicating 735
Index 769
www.it-ebooks.info
Contents
introduction xxvii
System Requirements xxviii
Using the Companion CD xxix
Acknowledgments xxxi
Support & Feedback xxxi
Preparing for the Exam xxxiii
part i Designing anD impLementing a Data WarehOuse
chapter 1 Data Warehouse Logical Design 3
Before You Begin ..................................................4
Lesson 1: Introducing Star and Snowflake Schemas ....................4
Reporting Problems with a Normalized Schema 5
Star Schema 7
Snowflake Schema 9
Granularity Level 12
Auditing and Lineage 13
Lesson Summary 16
Lesson Review 16
Lesson 2: Designing Dimensions ...................................17
Dimension Column Types 17
Hierarchies 19
Slowly Changing Dimensions 21
Lesson Summary 26
Lesson Review 26
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:
www.microsoft.com/learning/booksurvey/
vii
www.it-ebooks.info
Lesson 3: Designing Fact Tables ....................................27
Fact Table Column Types 28
Additivity of Measures 29
Additivity of Measures in SSAS 30
Many-to-Many Relationships 30
Lesson Summary 33
Lesson Review 34
Case Scenarios ...................................................34
Case Scenario 1: A Quick POC Project 34
Case Scenario 2: Extending the POC Project 35
Suggested Practices ..............................................35
Analyze the AdventureWorksDW2012 Database Thoroughly 35
Check the SCD and Lineage in the AdventureWorks-
DW2012 Database 36
Answers .........................................................37
Lesson 1 37
Lesson 2 37
Lesson 3 38
Case Scenario 1 39
Case Scenario 2 39
chapter 2 implementing a Data Warehouse 41
Before You Begin .................................................42
Lesson 1: Implementing Dimensions and Fact Tables .................42
Creating a Data Warehouse Database 42
Implementing Dimensions 45
Implementing Fact Tables 47
Lesson Summary 54
Lesson Review 54
Lesson 2: Managing the Performance of a Data Warehouse ...........55
Indexing Dimensions and Fact Tables 56
Indexed Views 58
Data Compression 61
Columnstore Indexes and Batch Processing 62
viii Contents
www.it-ebooks.info