Microsoft 70-767, Implementing a Data Warehouse using SQL

Microsoft Exam 70-767, Implementing a Data Warehouse using SQL Online Tutorial

Exam 70-767: Implementing a Data Warehouse using SQL retired on January 31, 2021.

The Microsoft 70-767, Implementing a SQL Data Warehouse 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). Also, Through this exam, you will understand how to design and implement ETL control flow elements and work with a SQL Service Integration Services package. The 70-767 exam consists of 75% of SQL Server 2016 on-premises. 25% is about Azure Big Data, Azure SQL Data Warehouse and related topics.

Microsoft 70-767 Exam Objectives

Designing, and implementing, and maintaining a data warehouse
Extracting, transform, and load data
Building data quality solutions

Targeted Audience

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

Microsoft 70-767 Exam Prerequisites

For the 70-767 exam, you should have experience about installing and implementing a Master Data Services (MDS) model, using MDS tools and creating a Master Data Manager database and web application.

The Microsoft 70-767 exam is definitely a way to open new heights in your professional career. It always better to know the exam details and be thorough with the syllabus before beginning with your preparations.

Basic Details

Take it from us, getting hold of these basic details will become life saviours on the examination. Here, we are referring to the part, you don’t have any shock before you even attempt your exam. With that being said, let’s look at the few details, you can’t miss.

The exam comprises 40-60 questions that must be completed within 120 minutes. Speaking of the exam pattern, here is a list of few types of questions you may encounter-

Scenario-based single answer question
Multiple-choice questions
Arrange in the correct sequence type questions
Drag & drop questions
One case study with 5 questions

However, the exam is only available in the English language. As far as the registration fee is concerned it’s USD 165 without taxes. And, in case of passing score, one requires 700 or more.

Exam Outline: Microsoft 70-767

As we know, in every exam there are some important topics you need to study. Most importantly, you need to get the details of the exam topics and understand them before starting to study. Moreover, for this exam, Microsoft has provided important topics which are as follows:

1. Design, implement and maintain a data warehouse (35–40%)

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

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

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

Designing storage for a data warehouse

Designing an appropriate storage solution, including hardware, disk, and file layout

Designing and implementing partitioned tables and views

Designing 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%)

Designing and implementing an extract, transform, and load (ETL) control flow by using a SQL Server Integration Services (SSIS) package

Designing and implementing 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

Designing and implementing an ETL data flow by using an SSIS package

Implementing 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

Implementing an ETL solution that supports incremental data extraction

Designing fact table patterns, enable Change Data Capture and create a SQL MERGE statement

Implementing an ETL solution that supports incremental data loading

Designing 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

Debugging SSIS packages

Fixing 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

Deploying and configure SSIS packages and projects

Creating an SSIS catalogue; deploy packages by using the deployment utility, SQL Server, and file systems; run and customize packages by using DTUTIL

Microsoft Exam 70-767 Free Practice Test

3. Build data quality solutions (15–20%)

Creating a knowledge base

Creating a Data Quality Services (DQS) knowledge base, determine appropriate use cases for a DQS knowledge base, perform knowledge discovery, and perform domain management

Maintaining data quality by using DQS

Adding 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

Implementing a Master Data Services (MDS) model

Installing 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

Managing data by using MDS

Using 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.

Exam Policies

If you have any doubt or want information about the certification exam, You can visit Microsoft 70-767, Implementing a SQL Data Warehouse exam page where they have provided exam related information. Moreover, there are many websites which resolve your queries and even provide detailed information about the exam collected from users only.

Exam Scheduling

Microsoft scheduling process is not that hectic. To know this please check the below steps,

Visit Microsoft portal and register yourself.
Schedule your exam by clicking on the “schedule exam” link.
On the next page, you will be asked to fill all your details.
Then there you can select the exam delivery option on the page of Pearson VUE.
Lastly, your need to pay the exam fees and your registration will be completed.

In the next step, you will learn about the exam retake policy.

Exam Retake

If you want to take the exam again then yes it is possible. However, Microsoft has some rules and policy for giving the exam again.

Above all, you need to note that,

If anyhow you are not able to achieve a passing score in the Microsoft 70-461 exam, then you have to wait for at least 24 hours before rescheduling.
Moreover, if you cancel or reschedule your exam within 5 business days of your registered exam then it is subject to a fee. Otherwise, you can also lose your entire fee.
Above all, you are only allowed to take the exam for more than five times per year.
Most importantly, if you pass the exam then you cannot give the exam again.

For more information regarding the Microsoft 70-767 exam FAQ page here!

Step by Step Preparation Guide: Microsoft 70-767

It would take more than a single article to go over every aspect of studying for a certification exam. This subject could fill a small book and not a very exciting one at that. There are some key truisms and must-do’s, however, which every candidate should be aware of when it comes to certification exam preparation.

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 Microsoft 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.

Step by Step Preparation Guide: Microsoft 70-767

STEP 1: Review all the Microsoft 70-767 Exam Info

Take it from the experts over here, while preparing for certification exams, make sure that you don’t jam your head with too many questions. Too many questions will definitely lead to anxiety and believe us when we say, anxiety is one thing you don’t want. To begin with, clear your head first. Next step is to deeply understand and analyse all the exam information provided above. Make sure that all the info is up to date. Review and keep note if there may have been some changes in the exam objectives or not. This step will make sure that you’re overdoing or forgetting something, that may somehow lead to anxiety on the exam day.

So, make sure that you’re on the same page as the certification vendor, so that there is no gap of confusion between the two.

STEP 2: Thoroughly go through the Course Outline

Now that you’ve reviewed the exam details regarding the exam. It’s time to make sure that you are up to date with the Course Outline. As mentioned earlier, the Course Outline is the most crucial part of the examination. Since this forms the syllabus of the examination. And, obviously, all the questions originate from this particular domain list. Therefore, it’s your responsibility to go to the Official site of Microsoft and check it on your own. Make sure to be an empiricist, and view and experience knowledge on your own. In case, you’re unable to locate the list of domains. Check out the image below and download the Course Outline.

STEP 3: Finding all the RIGHT Learning Resources

We cannot stress enough that only finding the right learning resources will allow you to understand each and every domain properly. And, you’re well aware of the fact that the internet is clustered with so many different resources, training programs and exam dumps. We highly recommend not to follow exam dumps. So, hold your horses there and then. Since you have landed here, we just wanna make sure here that you’re in the safe hands. We are going to shed some light on where to get all the right learning resources to follow. Not only will this provide you with a stranger base that will help you qualify, but also this will provide you with that confidence among your peers. So, without a further adieu let’s get started.

STEP 3.1: MICROSOFT Training Programs

If this is your first time with a certification exam, let’s get you familiar with Microsoft Training Programs. When it comes to training programs, Microsoft has two ways to prepare. The first is the free online training and the second is the paid Instructor-led Training. It’s totally a subjective approach to choose either one or both of these learning resources. To provide you with a clear view, let’s just view them separately.

STEP 3.2: Online Learning Paths

Microsoft website provides every update and detail related to the Microsoft 70-461exam. Moreover, gathering information first is a smart choice before starting preparation. Above all, Microsoft provided Microsoft Learn which will help you achieve your goals. Moreover, you will create your own learning schedule with more confidence level.

STEP 3.3: Instructor-led Training

We’re just going to say it out loud, that when it comes to Instructor-led Training, Microsoft has definitely hit the ground running. This is not just a rumour, but a fact that Microsoft Instructor-led Training is a gold standard in the industry. In case, you’re amped up by the high standards, wait till you hear the benefits you’ll enjoy. Further, instructor-led training has a facility to have on-demand classroom training. So, if you’re juggling so many chores and fear you might skip one, then this will allow you to arrange classes according to your convenience.

Both of these training and learning paths can be easily located on the Microsoft Portal. So, make sure to check them out

STEP 3.4: Microsoft Reference Book

Microsoft provides you book for reference for 70-767 exam, which is available on the Microsoft website. Moreover, this book contains lessons, practice exercises and skills assessment with practice test via CD.

STEP 4: Online Courses

If a candidate wishes to join an Online Course for the same. There are various courses available online. In fact, the internet is cluttered with so many courses. While choosing the right online course for your preparation, make sure to view all the objectives of the course. Is it in your price range or not? Is it feasible for you? And, if possible try to take a trial session before hitting that buy button. All these factors will allow you to choose the right fit according to your needs. These courses provide you with the online video study material that can get digested easily. Also, they even provide the candidate with sample papers. So, you can try them out.

STEP 5: Join the Community/ Online Forum

Online forums and study groups are a great way to prepare for the certification exam. Therefore, feel free to get in touch with other candidates through study forums or online groups to ask a question related to the topic you’re having difficulty with.

However, it’s not something you have to join. It’s just something very subjective. Not to mention, these online groups help you stay equated with the other people who are also walking through the same path as yours. Moreover, you can also ask a question related to the topic you’re having difficulty with.

STEP 6: Self-evaluation Time

Finally, we’re on the last step for the preparatory guide. Further, this final step will give the candidate the exact insight of the topics in which they’re lacking. So, make sure you’re going through sample tests after you have gone through the entire syllabus. Most importantly, all the practice tests are designed to encounter the real exam environment around you. However, practice papers can be from various sources. Above all, remember the more you test yourself the better you’re going to become. SO START PRACTICING NOW!