book

Exam Ref 70-767 Implementing a SQL Data Warehouse

Name: Exam Ref 70-767 Implementing a SQL Data Warehouse
ISBN: 9781509304486

by Jose Chinchilla, Raj Uchhana

November 2017

Beginner to intermediate

289 pages

7h 40m

English

Microsoft Press

Read now

Unlock full access

Cover Page
Title Page
Copyright Page
Dedication Page
Contents at a Glance
Contents
Introduction
Organization of this bookMicrosoft certificationsAcknowledgmentsMicrosoft Virtual AcademyQuick access to online referencesErrata, updates, & book supportWe want to hear from youStay in touch
Important: How to use this book to study for the exam
Chapter 1 Design and implement a data warehouse
Skill 1.1 Design and implement dimension tablesDetermine attributesDesign shared and conformed dimensionsDesign hierarchiesDetermine dimension keys and key relationships for a data warehouseDetermine star or snowflake schema requirementsDetermine auditing or lineage requirementsImplement data lineage of a dimension tableSkill 1.2 Design and implement fact tablesIdentify measuresDesign and implement fact tablesImplement additive, semi-additive, and non-additive measuresIdentify dimension table relationshipsSkill 1.3 Design and implement indexes for a data warehouse workloadDesign an indexing solutionImplement clustered, nonclustered, filtered, and columnstore indexesSelect appropriate indexesSkill 1.4 Design storage for a data warehouseDesign an appropriate storage solution, including hardware, disk, and file layoutSkill 1.5 Design and implement partitioned tables and viewsDesign a partition structure to support a data warehouseImplement sliding windowsImplement partition eliminationDesign a partition structure that supports the quick loading and scale-out of dataThought experimentThought experiment answerChapter summary
Chapter 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) packageUnderstanding new terminologiesDesign and implement ETL control flow elements, including containers, tasks, and precedence constraintsCreate variables and parametersCreate checkpoints, sequence and loop containers, and variables in SSISImplement data profiling, parallelism, transactions, logging, and securitySkill 2.2: Design and implement an ETL data flow by using an SSIS packageImplement slowly changing dimension, fuzzy grouping, fuzzy lookup, audit, blocking, non-blocking, and term lookup |transformationsData flow source and destination column mappingDetermine appropriate scenarios for Transact-SQL joins versus SSIS lookupSkill 2.3: Implement an ETL solution that supports incremental data extractionDesgin fact table patternsEnable Change Data CaptureCreate a SQL MERGE statementSkill 2.4 Implement an ETL solution that supports incremental data loadingDesign a control flow to load change dataLoad data by using Transact-SQL Change Data Capture functionsLoad data by using Change Data Capture in SSISSkill 2.5: Debug SSIS packagesFix performance, connectivity, execution, and failed logic issues by using the debuggerAdd data viewersImplement breakpointsEnable logging for package executionImplement error handling for data typesProfile data with different toolsError handling at package levelSkill 2.6: Deploy and configure SSIS packages and projectsCreate an SSIS catalogDeploy packages by using the deployment utility, SQL Server, and file systemsRun and customize packages by using DTUTILThought exerciseThought exercise answerChapter summary

Chapter 3 Build data quality solutions
Skill 3.1 Create a knowledge baseInstall DQSCreate a Data Quality Services (DQS) knowledge baseDetermine appropriate use cases for a DQS Knowledge BasePerform domain managementPerform knowledge discoverySkill 3.2 Maintain data quality by using DQSAdd matching knowledge to a knowledge baseCreate a matching policyPrepare a DQS Knowledge Base for data deduplicationClean data by using DQS knowledgeClean data by using the SSIS DQS taskSkill 3.3 Implement a Master Data Services (MDS) modelInstall MDSUse the Master Data Services Configuration ManagerCreate a Master Data Services database and web applicationImplement MDSCreate models, entities, hierarchies, collections, and attributesDefine security rolesImport and export dataStage and load dataCreate and edit a subscriptionImplement entities, attributes, hierarchies, and business rulesSkill 3.4 Manage data by using MDSUse MDS toolsDeploy a sample model using MDSModelDeploy.exeCreate a Master Data Management hubThought exerciseThought exercise answerChapter summary
Index
About the authors
Code Snippets

Content preview from Exam Ref 70-767 Implementing a SQL Data Warehouse

Index

A

additive measures 21

administrative tasks 237

administrator role types 237

Advanced Editor 180–181

alternate key columns 5, 6

asynchronous transforms 140–143

attributes

business rules for 244–245

changing 127

changing values of 10–11

columns 5–7

creating 235–237

dimension tables, determining 2–4

fixed 127

grouping related 4

implementing 243

auditing 16–17

audit logging 160

Audit transform 137–139

B

backup files 46, 47

Balanced Tree (B-Tree) structure 25

blocking transforms 140

breakpoints 171–174

built-in connectors 79

business rules 244–246, 256

C

cache limitations 145

calendar hierarchy 8

CDC. See Change Data Capture

Centralized approach 254

Change Data Capture (CDC) 83, 146, 147–148

components of 155–156

control task 155–156

in SSIS ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

SQL Server 70-767: Implementing a Data Warehouse Using SQL

Publisher Resources

ISBN: 9781509304486

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Exam Ref 70-767 Implementing a SQL Data Warehouse

by Jose Chinchilla, Raj Uchhana

Index

A

B

C

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.