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.
- Microsoft SQL Server 2012 Master Data Services 2/E 2nd Edition
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
- Defining Shared Dimensions and Compound Cubes
- Analysis Services MOLAP Performance Guide for SQL Server 2012 and 2014 - 2.3.1 Use a Star Schema for Best Performance
- Defining Dimension Granularity within a Measure Group
- What are Slowly Changing Dimensions?
- Dimension
- Star schema vs. snowflake schema: Which is better?
- Granularity
- Data lineage
- Christian van den heever's Blog
- 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
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
- SQL Server Integration Services (SSIS) Tutorial
- Stairway to Integration Services
- SQL Server Integration Services (SSIS) Part 1 - Getting Started
- Configure the SQL Server Integration Services For Loop Container
- Integration Services Containers
- Precedence Constraints
- Integration Services (SSIS) Package and Project Parameters
- SSIS Variable and Expression Example - Part 1
- Working with Variables in SQL Server Integration Services
- Integration Services Checkpoints to restart package from failure
- Restart Packages by Using Checkpoints
- Using the Data Profiling SQL Server Integration Services SSIS task
- Data Profiling Task
- Implement Parallel Execution in SSIS
- How To Use Transactions in SQL Server Integration Services SSIS
- Integration Services Transactions
- Integrated Logging with the Integration Services Package Log Providers
- SQL Server Integration Services Security Tips
- Security Overview (Integration Services)
- Design and implement an ETL data flow by using an SSIS package
- Handle Slowly Changing Dimensions in SQL Server Integration Services
- Slowly Changing Dimension Transformation
- Slowly Changing Dimension using SSIS
- Fuzzy Grouping Transformation
- Fuzzy Lookup Transformation
- Audit Transformation
- SSIS Non-blocking, Semi-blocking and Fully-blocking components
- Data Mining Part 38: Text Mining - The term lookup
- Term Lookup Transformation
- SSIS Part 152-Load Only Matching Column Data to SQL Server Table from Excel Files Dynamically
- SSIS: There Are Some Mapping Errors On This Path Troubleshooting
- SSIS Lookup or T-SQL Join
- When to use T-SQL or SSIS for ETL
- Top 10 Methods to Improve ETL Performance Using SSIS
- SQL Server Integration Services Performance Tips
- SSIS Deduplication Component
- Implement an ETL solution that supports incremental data extraction
- Implement an ETL solution that supports incremental data loading
- Debug SSIS packages
- SQL Server Integration Services Best Practices Tips
- Top 10 SQL Server Integration Services Best Practices
- SSIS Operational and Tuning Guide
- Integrated Logging with the Integration Services Package Log Providers
- Debugging Control Flow in SQL Server Integration Services Part 2
- Integration Services Error and Message Reference
- SSIS - Data Viewers
- Data Profiling Task - SQL Server 2008
- Data cleaning with SSIS - Part1
- Maintenance Cleanup Task
- Cleaning Up Dirty Data with SSIS
- Deploy and configure SSIS packages and projects
- Understanding the SQL Server Integration Services Catalog and creating the SSISDB Catalog
- Create the SSIS Catalog
- Deploying SSIS Packages
- SSIS 2012 Projects: Setup, Project Creation and Deployment
- Deploy Projects to Integration Services Server
- Deploy Packages to Integration Services Server
- Integration Services Deployment Wizard
- Create a Deployment Utility
- Deploy Packages by Using the Deployment Utility
- SSIS SQL Server Deployment, File System Deployment, and SSIS Package Store
- dtutil Utility
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
- Install Master Data Services
- Install and configure multiple instances of Master Data Services MDS on the same server
- Create a Model (Master Data Services)
- Create a Collection (Master Data Services)
- Database Logins, Users, and Roles (Master Data Services)
- Security (Master Data Services)
- Overview: Importing Data from Tables (Master Data Services)
- Create a Subscription View to Export Data (Master Data Services)
- Entities (Master Data Services)
- Domain-Based Attributes (Master Data Services)
- Attribute Groups (Master Data Services)
- Hierarchies (Master Data Services)
- Business Rules (Master Data Services)
- Manage data by using MDS
- Master Data Services Configuration Manager
- Create a Master Data Manager Web Application (Master Data Services)
- Deploy a Model Deployment Package by Using MDSModelDeploy
- Associate a Master Data Services Database and Web Application
- Master Data Services Add-in for Microsoft Excel
- Master Data Management (MDM) Hub Architecture
- Overview: Importing Data from Tables (Master Data Services)
- Create a Subscription View to Export Data (Master Data Services)
Next Steps
There are several links that will be useful to you:
- Exam 70-767
- Exam policies and FAQ
- 20767: Implementing a SQL Data Warehouse (five days)
- SQL Server Professional Development Certifications Tips
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2017-03-17
