Microsoft certifications are internationally recognised and validate your professional skills. The Microsoft Implementing a SQL – Data Warehouse 70–767 exam helps you demonstrate your mastery of managing data warehouses. It is designed for professionals who create Business Intelligence (BI) Solutions.
This exam requires a deep learning about several topics. Moreover, you need to have the knowledge to design and work with SQL Server Integration Services, table design, performance, etc. With such a vast syllabus this exam is certainly difficult. But don’t worry as here we provide you with our specially curated Preparatory guide. It will guide you throughout your certification journey and help you pass the exam with flying colours.
What is Microsoft 70-767 Exam?
The Microsoft-Implementing a SQL Data Warehouse 70-767 exam is all about creating a data warehouse using ETLs (Extract, Transform and Load), implementing a data warehouse design, interacting with Azure technologies and maintaining the data with Data Quality Service (DQS).
The 70-767 exam consist of 75% of SQL Server 2016 on- premises. 25% is about Azure Big Data, Azure SQL Data Warehouse and related topics.
This exam will teach you how to create and use ETL control flow components and manage SQL Service Integration Services packages.
Why go for the Microsoft 70-767 Exam?
The highly acclaimed certification is specially designed to measure your Office 365 skills. Achieving this will definitely bring great opportunities in your career. While, exam makes your resume shine it also grabs the attention of your potential employers. Below are some reasons about why you should take the Microsoft 70-767 exam
- Firstly, this certification validates your skills and mastery in Office 365.
- Secondly, it helps you chase further advanced goals in the field of IT
- Thirdly, according to the Microsoft statistics, the Office 365 Administrator makes annually up to $62.500 at the entry-level, $82.500 at the middle level, and $95.100 at the senior level. Thus this exam will definitely help you grab higher salaries.
- Also, this exam will increase your employability, as you become certified specialist.
Objectives of the Microsoft 70-767 Exam
- Firstly, Designing, and implementing, and maintaining a data warehouse
- Secondly, Extracting, transform, and load data
- Thirdly, Building data quality solutions
- Subsequently, Organizing its coverage by exam objectives
- Lastly, it features strategic, what-if scenarios to challenge you
Who should take the exam?
If you are a business intelligence developer, ETL developer or an administrator, this exam is definitely intended for you. It also targets developers who perform the functions of data cleansing in addition to ETL and data warehouse implementation
Prerequisite for the Microsoft 70-767 Exam
To pass the 70-767 exam, you need to know how to set up and use Master Data Services (MDS), including creating a Master Data Manager Database and web application, and using MDS tools.
The Microsoft 70-767 exam can help advance your career. It’s important to understand the exam details and study the syllabus thoroughly before you start preparing.
Exam Details
| Exam Name | Microsoft-Implementing a SQL Data Warehouse |
| Exam Code | 70-767 |
| Duration | 150 minutes |
| Total Questions | 40-60 Questions |
| Languages | English |
| Audiences | IT professionals |
| Technology | SQL Server 2016/2017/2019 |
| Credit toward certification | MCSA, MCSE |
| Price | $165 |
Exam Course
This exam mainly covers the following 3 domains. Each domain is further subdivided into topics, which are detailed below.
Course Outline for Microsoft 70-767 Exam
1. Design, implement, and maintain a data warehouse (35–40%)
Firstly, designing and implementing dimension tables
Designing shared and conformed dimensions, determine support requirements for slowly changing dimensions, determine attributes, design hierarchies, determine star or snowflake schema requirements, determine the granularity of relationship by using fact tables, determine auditing or lineage requirements, determine keys and key relationships for a data warehouse, implement dimensions, implement data lineage of a dimension table
Secondly, designing and implement fact tables
Identify measures, identify dimension table relationships, create composite keys, design a data warehouse that supports many-to-many relationships, implement semi-additive measures, and implement non-additive measures
Thirdly, designing and implementing indexes for a data warehouse workload
Designing an indexing solution; select appropriate indexes; implement clustered, non-clustered, filtered, and column store indexes
Subsequently, Designing storage for a data warehouse
Design an appropriate storage solution, including hardware, disk, and file layout
Further, Designing and implementing partitioned tables and views
Design a partition structure to support a data warehouse, implement sliding windows, implement partition elimination, and design a partition structure that supports the quick loading and scale-out of data
2.Extract, transform, and load data (40–45%)
Firstly, designing and implementing an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package
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
Then, Designing and implementing an ETL data flow by using an SSIS package
Implement slowly changing dimension, fuzzy grouping, fuzzy lookup, audit, blocking, non-blocking, and term lookup transformations; map columns; determine the appropriate transform object for a given task; determine appropriate scenarios for Transact-SQL joins versus SSIS lookup; design table loading by using bulk loading or standard loading; remove extra rows or bad rows by using deduplication
Subsequently, Implementing an ETL solution that supports incremental data extraction
Design fact table patterns, enable Change Data Capture, and create a SQL MERGE statement
Also, Implementing an ETL solution that supports incremental data loading
Design a control flow to load change data, load data by using Transact-SQL Change Data Capture functions, load data by using Change Data Capture in SSIS
Moreover, Debugging SSIS packages
Fix performance, connectivity, execution, and failed logic issues by using the debugger; enable logging for package execution; implement error handling for data types; implement breakpoints; add data viewers; profile data with different tools; perform batch clean-up
Lastly, Deploying and configure SSIS packages and projects
Create an SSIS catalog; deploy packages by using the deployment utility, SQL Server, and file systems; run and customize packages by using DTUTIL
3.Build data quality solutions (15–20%)
To begin with, create a knowledge base
Create a Data Quality Services (DQS) knowledge base, determine appropriate use cases for a DQS knowledge base, perform knowledge discovery, and perform domain management
Then, Maintain data quality by using DQS
Add matching knowledge to a knowledge base, prepare a DQS for data deduplication, create a matching policy, clean data by using DQS knowledge clean data by using the SSIS DQS task, install DQS
Also, Implement a Master Data Services (MDS) model
Install MDS; implement MDS; create models, entities, hierarchies, collections, and attributes; define security roles; import and export data; create and edit a subscription; implement entities, attributes, hierarchies, and business rules
Additionally, Manage data by using MDS
Use MDS tools, use the Master Data Services Configuration Manager, create a Master Data Manager database and web application, deploy a sample model using MDSModelDeploy.exe, use the Master Data Services web application, use the Master Data Services Add-in for Excel, create a Master Data Management hub, stage and load data, create subscription views
Each and every subtopic of the Domain should be taken in high regard as they form the base of your exam. You should be very clear with the topics so as to pass the exam.
The syllabus is really vast and often leads to stress while preparations. Don’t worry, as to ease out this issue we present you our preparation guide. This guide is specially curated by our experts with years of experience in the field of SQL Data Warehouse and will surely direct you on the right path for this certification.
Resources for Preparation
This exam requires a good amount of consistency and determination. Moreover, you need extreme focus for clearing it in the every first attempt. That’s why it’s essential to take a look at the available study resources that will best aid your preparations.
Books
Books have always been one of the most preferred means to prepare for any exam. They provide deep understanding of the concepts and thus are an important resource for your preparations. There are plenty of books available for the Microsoft 70-767 Exam that you might find helpful. We recommend you to refer the Exam Ref 70-767 Implementing a SQL Data Warehouse published by Microsoft.
Additionally you can also refer to the following books Professional Microsoft SQL Server 2014 Integration Services (Wrox Programmer to Programmer) 1st Edition, and Microsoft SQL Server 2012 Master Data Services 2/E 2nd Edition.
Training Courses
There are various training courses for this exam, offered in both instructor-led and online formats. Choose the one that fits your needs and helps you prepare. The official Course 20767A: Implementing a SQL Data Warehouse by Microsoft is highly recommended. You can also consider other courses like Implementing Big Data Analysis and Delivering an Elastic Data Warehouse as a Service.
Preparatory Guide
Preparing for the Microsoft 70-767 is definitely tough, but our preparatory guide makes it easier for you. Follow the steps while preparing to earn this highly appreciated certification.
Refer the official guide
The official guide for Microsoft 70-767 Exam acts as a blueprint for your preparations. Therefore, it is highly recommended to refer and confirm the details about the exam form the guide.
Strategize a Study Plan
Fixing a study plan for your preparations is of great importance. It helps in maintaining consistency and avoids distractions. A study plan must be made keeping in mind the time duration left till the final exam. We recommend you to start strengthening your weaker areas and have enough time for revisions. The bottom line is that you must set a specific time to prepare every day.
Have clarity about the course
The most important step in preparation is to have clarity about the course domains. Each and every domain needs utmost focus. You should be thorough with all the subtopics as well. Preparing thoroughly also involves learning from the useful materials that are convenient for you.
Attempt Practice Tests
Keeping your preparations under check is one of the most essential step while preparing. Hence you should definitely attempt practice tests. They are a great resource to analyse your preparation and help you figure out which areas require more focus. Strengthening them will help you ace the exam. You must refer the Practice test by Microsoft.
Also, there are various free Practice Tests available that you may attempt while preparing. Therefore, by attempting several practice test questions, you increase your confidence level and acquire time management skills.
Join a Community
Participating in online forums and study groups can be really helpful while you’re preparing for the exam. They allow you to learn from experienced individuals, ask questions to clear your doubts, and gain valuable insights to help you earn the certification.
Tips for the Exam day
The exam day is the day when all your hard work comes to a real test. Don’t stress or panic as that may affect your performance. Here we present you some last minute tips for the Microsoft 70-767 exam-
- Firstly, take a deep breath before starting and try staying calm. A calm and composed mind will help attempt the exam accurately.
- Secondly, read each questions very carefully and only them choose an option. The difficulty level of the exam is quite high hence, the questions may confuse you. Thus read each question and attempt only after you have understood the full meaning
- Thirdly, Use the method of elimination. This process will confine your options to the possible right answers and save your time.
- Lastly, remember not to Over-analyse. There are no random answers in Microsoft exams. Go for the best answer stay firm on your decision.
Conclusion
The Microsoft Implementing a Data Warehouse using SQL 70–767 exam is highly in demand. This exam is definitely your step ahead in the field of IT. Preparing of this exam requires all your concentration and attention. It certainly is difficult to crack, but by following our preparatory guide you will surely clear it in the very first attempt.
The best career opportunities are only a few tests away. Start preparing now for Microsoft Implementing a Data Warehouse using SQL 70–767 exam Now!
