This class teaches you how to design and build a successful data warehouse / business intelligence system on the Microsoft SQL Server platform. Using the Microsoft Data Warehouse Toolkit, Second Edition with SQL Server 2008 R2 as its foundation, it translates proven Kimball Lifecycle methods and patterns to the Microsoft SQL Server 2005, 2008 R2. The Microsoft Data Warehouse Toolkit(2nd Edition) With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset by Joy Mundy, Warren Thornthwaite, Ralph Kimball, Ralph Kimball Ph.D., Thornthwaite Mundy Paperback, 696 Pages, Published 2011 by Wiley ISBN-13: 978-0-470-64038-8, ISBN: 0-470-64038-3.
By: Daniel Calbimonte | Updated: 2017-03-17 | Comments (9) | Related: More >Professional Development Certifications
Problem
I am planning to take the Microsoft 70-767 exam which is focused on Data Warehouse using ETLs, implementing a data warehouse design, interacting with Azure technologies (Big Data, Azure DW, etc.) and maintaining the data with Data Quality Services (DQS). It seems very difficult to me. Do you have tips for this exam?
Solution
Yes. We are here to help. This tip is part of our SQL Server Professional Development Certifications Tips.
What is the Microsoft 70-767 certification exam?
It is an exam about creating a data warehouse using ETLs (Extract, Transform and Load), implement a data warehouse design, interact with Azure technologies (Big Data, Azure DW, etc.) and maintain the data with Data Quality Services (DQS).
Who should take the exam?
This is oriented to Business Intelligence (BI) developers, ETL developers and administrators.
Is it an exam oriented to SQL Server 2016?
Yes. 75% is SQL Server 2016 on-premises. 25% is about Azure Big Data, Azure SQL Data Warehouse and related topics.
Which SQL Server 2012/2014 exam is similar to this one?
It is similar to the exam 70-463. However, it added the Integrate with Cloud data and big data chapter and additional changes.
Is this exam difficult?
Yes. It requires learning several topics. If you already took the 70-463 exam, you will suffer only with Azure (which is cool and fantastic, but it requires time to learn all the topics). If you do not have experience in Business Intelligence technologies, you will need to study a lot. It requires a lot of knowledge to design and work with SQL Server Integration Services (SSIS), table design, performance, etc.
Which books would you recommend for this exam?
There are some books that may be helpful
- The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling 3rd Edition is a good book for dimension and fact concepts. It goes beyond the Microsoft technologies. I recommend this book for the design and implement a Data Warehouse module.
- Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer 1st Edition is a great book for SSIS. It will help you a lot to have deep knowledge in SSIS.
- Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer) 1st Edition is another great book for SSIS.
- Introducing Microsoft Azure HDInsight Kindle Edition will help you to introduce to the Microsoft Hadoop world.
- Microsoft Azure SQL Data Warehouse - Architecture and SQL (Tera-Tom Genius Series) will help you in Azure SQL Data Warehouse topics.
Beware that the Azure world is constantly changing. Some books that are up to date now may be obsolete tomorrow. Make sure to have the latest edition and read the latest changes in the Azure SQL world.
Are there some courses for this exam?
- Course 20767A: Implementing a SQL Data Warehouse is the official course right now.
- Implementing a Data Warehouse with SQL Server Jump Start was the MVA course for the old 70-463 exam, but it still contains valid material for this new exam.
- Implementing Big Data Analysis is a great introductory course for Big Data. I loved these videos.
- Deliver an Elastic Data Warehouse as a Service is a good introduction to Azure Data Warehouse.
Can you provide some links to study?
Of course. Here you have some links:
Design and implement a data warehouse
- Design and implement dimension tables
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 - 2.3.1 Use a Star Schema for Best Performance
- Design and implement fact tables
- Design and implement indexes for a data warehouse workload
- Design storage for a data warehouse
- Design and implement partitioned tables and views
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 2.2 Partitioning a Cube
Extract, transform, and load data
- Design and implement an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package
- Design and implement an ETL data flow by using an SSIS package
- Implement an ETL solution that supports incremental data extraction
- Implement an ETL solution that supports incremental data loading
- Debug SSIS packages
- Deploy and configure SSIS packages and projects
Integrate solutions with cloud data and big data
- Integrate external data sources with SQL Server by using Polybase
- Extract, transform, and load data from SQL Data Warehouse by using Polybase
- Design and implement an Azure SQL Data Warehouse
- Manage and maintain a SQL Data Warehouse
Build data quality solutions
- Create a knowledge base
- Maintain data quality by using DQS
- Implement a Master Data Services (MDS) model
- Manage data by using MDS
Next Steps
There are several links that will be useful to you:
Last Updated: 2017-03-17
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.
View all my tips
View all my tips
The Bermuda Triangle of data warehousing.
Metadata is a vast, relatively uncharted region of the DW/BI system. Some teams sail into it full speed ahead, never to be heard from again. Most teams try to avoid the problem by sailing around it. Unfortunately, the metadata region is smack in the middle of your path to the great new world of business value, and you need to figure out how to navigate it successfully.
One of the first metadata challenges is figuring out what metadata is. We begin this chapter with a brief definition and description of the three major categories of metadata found in a DW/BI system: business, technical, and process metadata.
With a common terminology in place, your next challenge is to figure out what metadata you have and where it comes from. We explore the various sources and uses of metadata across the SQL Server toolset. Every component of the toolset is metadata-driven. The problem is the metadata is kept in different locations and different formats, so finding and managing the metadata is challenging. Finally, we describe a basic, practical approach for dealing with the most important and broadly used metadata elements.
Metadata creation and management can be a tangled topic. What we present here is a starting point. Many of you, and certainly those of you in larger organizations, will need to expand on our recommendations and customize them to your environment.
In this chapter, you will learn: