Pass 70-767 MCSA Certification Exam Fast

70-767 Exam Has Been Retired

This exam has been replaced by Microsoft with new exam.

Microsoft 70-767 Exam Details

The Microsoft 70-767 exam is part of the requirement for two certifications, namely the MCSA: SQL 2016 BI Development and the MCSE: Data Management and Analytics.

About Certifications

The MCSA: SQL 2016 BI Development certificate verifies the holder's understanding of extracting, transforming, and loading (i.e. ETL procedures) along with knowledge about warehouse data. This certification also shows the candidate's skills in the implementation of BI solutions. The second certificate, the MCSE: Data Management and Analytics, confirms the candidate's capability in running an efficient and modern data center along with other additional skills which include systems management, virtualization, and more. Earning both these certifications will require you to get a passing score in 70-767 exam.

About 70-767 Test

The Microsoft test 70-767 in other words is known as Implementing a Data Warehouse using SQL. It targets warehouse developers whose responsibilities include designing solutions for business intelligence. It assesses and proves one’s competence in extracting and transforming data, performing data cleansing, and implementing a warehouse of data, among the rest. Additionally, it validates the examinee's knowledge of implementing indexes, fact, dimension, and partitioned tables, creating storage solutions, utilizing SQL server integration services, implementing an ETL flow of data as well as using ETL to support data extraction and loading, using DQS to maintain data quality, creating knowledge bases, and implementing MDS model and managing data by using such a model. Exam 70-767 is one of the two tests required for the Microsoft MCSA: SQL 2016 BI Development certification. The second exam is 70-768 that focuses on the development of SQL data models. In particular, this certificate ensures that its holders are ready to handle all the tasks of the data analyst’s role. Also, if you already possess another SQL 2016 related certification, you can pass 70-767 exam to earn the MCSE: Data Management and Analytics. This one opens extra opportunities for database administrators by verifying their expert-level knowledge and skills. All the mentioned topics which are discussed below will be presented in your Microsoft exam 70-767 ranging from 40 to 60 questions. Candidates will be given 120 minutes to answer all the items with an aim to gain a score of about 700 points out of 1000. However, as this is also an MCSE exam, it in this case will last for 150 minutes. This test is available in English and requires an enrollment fee of $165. To register for it, one should create a Microsoft account and follow the hints provided on the exam page.

Exam Topics and Domains

The content of 70-767 covers all the crucial areas one needs to know to perform well as a developer or any other specialist working with data. The topics involved can be divided into the following sections:

  1. Creating, maintaining, and implementing a warehouse of data

The first domain takes up about 35 to 40% of the total exam questions. To master this area, the candidate should be capable of defining and putting into practice various dimension tables. This subsection tests the examinee's knowledge of various types of dimensions, attributes, design hierarchies, schema requirements, and more. It is also essential for the examinee to possess skills in designing and implementing fact tables. The identification of measures, the concept of composite keys, and types of relationships will as well be tested in this part. Furthermore, the applicant must show skills in creating indexes for the warehouses of data, indexing, and storage solutions. Finally, the candidates should have an understanding of partitioned tables and views. To sum it all up, the applicant needs to design as well as implement dimension tables, fact tables, indexes for the data warehouses, storage solutions, and partitioned tables & views.

  1. Loading, transforming, and extracting data

This objective covers about 40 to 45%. To grasp this segment, the examinee should be fluent in designing and implementing ETL control flow through the SSIS package. The candidate should also have an understanding of various components of ETL control flow and SSIS variables and should be capable of implementing changing dimensions, auditing, determining transform objects, and more. Additionally, the knowledge of Transact-SQL will be beneficial to get through this tested area. Furthermore, the applicant must be able to debug SSIS and fix performance, connection, and other issues. To summarize, this domain includes designing and implementing ETL flow of data and control by making use of SSIS, ETL solution supporting minor data extraction and loading, debugging and deploying SSIS packages.

  1. Designing solutions for data quality

The final topic extends to 15 to 20%. For getting through this area, the examinee must be able to build the relevant knowledge with the help of their understanding of Data Quality Services (DQS) and their components. Moreover, using DQS for maintaining data quality will be included in the official test. The examinee should also be capable of data deduplication, creating matching policies, cleaning data, and more. This section also tests the applicant in implementation of Master Data Services (MDS), their installation, and configuration. Being able to manage data through MDS by using its various tools will help the candidate through this objective. At last, candidates must be able to use the MDS Configuration Manager, build a Master Data Manager database, and more. To recap, this part covers the following subject matters like knowledge base, DQS, and MDS.

Career Opportunities and Pay

By passing 70-767 exam and acquiring the MCSA: SQL 2016 BI Development and the MCSE: Data Management and Analytics certificates, candidates can apply for a wider variety of jobs that go with decent annual salaries as stated by Payscale. These options include:

  • BI Developer: $80,237;
  • BI Analyst: $69,078;
  • SQL Developer: $74,102;
  • Database developer: $75,476.
  • Senior database administrator: $108,749.

Career Path

As was said above, the Microsoft 70-767 test is one of the options that you can choose to complete your MCSA path and get certified in SQL 2016 BI Development. After that, you are welcome to pursue the MCSE: Data Management and Analytics certificate, which requires the MCSA: SQL 2016 BI Development as one of the prerequisite alternatives.