Book description
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 solutionsThis 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.
Implementing a SQL Data Warehouse
About the Exam
Exam 70-767 focuses on skills and knowledge required for working with relational database technology.
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
Passing this exam as well as Exam 70-768 (Developing SQL Data Models) earns you credit toward a Microsoft Certified Solutions Associate (MCSA) SQL 2016 Business Intelligence (BI) Development certification.
See full details at: microsoft.com/learning
Table of contents
- Cover Page
- Title Page
- Copyright Page
- Dedication Page
- Contents at a Glance
- Contents
- Introduction
- 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 tables
- Skill 1.2 Design and implement fact tables
- Skill 1.3 Design and implement indexes for a data warehouse workload
- Skill 1.4 Design storage for a data warehouse
- Skill 1.5 Design and implement partitioned tables and views
- Thought experiment
- Thought experiment answer
- Chapter 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) 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
- Skill 2.3: Implement an ETL solution that supports incremental data extraction
- Skill 2.4 Implement an ETL solution that supports incremental data loading
- Skill 2.5: Debug SSIS packages
- Skill 2.6: Deploy and configure SSIS packages and projects
- Thought exercise
- Thought exercise answer
- Chapter summary
-
Skill 2.1: Design and implement an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package
-
Chapter 3 Build data quality solutions
- Skill 3.1 Create a knowledge base
- Skill 3.2 Maintain data quality by using DQS
-
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
- Thought exercise
- Thought exercise answer
- Chapter summary
- Index
- About the authors
- Code Snippets
Product information
- Title: Exam Ref 70-767 Implementing a SQL Data Warehouse
- Author(s):
- Release date: November 2017
- Publisher(s): Microsoft Press
- ISBN: 9781509304486
You might also like
video
SQL Server 70-767: Implementing a Data Warehouse Using SQL
8+ Hours of Video Instruction Prepare for Microsoft Exam 70-767: Implementing a Data Warehouse Using SQL, …
book
Exam Ref 70-762 Developing SQL Databases
Prepare for Microsoft Exam 70-762, Developing SQL Databases –and help demonstrate your real-world mastery of skills …
book
Exam Ref 70-764 Administering a SQL Database Infrastructure
Prepare for Microsoft Exam 70-764—and help demonstrate your real-world mastery of skills for database administration. This …
book
Exam Ref 70-761 Querying Data with Transact-SQL, 1st Edition
Prepare for Microsoft Exam 70-761–and help demonstrate your real-world mastery of SQL Server 2016 Transact-SQL data …