MCSA: SQL 2016 BI Development Certification Has Been Retired

This certification has been replaced by Microsoft with new certification(s).

Certification Details

Microsoft, best known for its Windows Operating system, provides a variety of products and services across the globe. To ensure the qualifications of the professionals who work with their technologies, Microsoft offers a substantial number of certifications for IT personnel. These certificates are divided according to areas of expertise and levels of proficiency, including those for novices, middle-level specialists, and experts.

Certification Overview

The MCSA: SQL 2016 BI Development certification verifies an individual's skills in extraction, transformation, and loading (ETL). In addition to that, it also confirms the holder's understanding of the implementation of various Business Intelligence solutions through tabular and multidimensional data models along with Online Analytical Processing (OLAP) cubes. Skills in working with data warehouse are also needed.

Audience & Required Exams

This certification was created for those aspiring candidates who have a basic understanding of relational databases and Transact-SQL in addition to some experience with database design. To earn the MCSA: SQL 2016 BI Development certification, the applicant should get a passing score in two exams that are 70-767: Implementing a Data Warehouse using SQL and 70-768: Developing SQL Data Models.

Each of these tests contains 40 to 60 questions that need to be completed within 180 minutes. The maximum score an individual can get is 1000 and the passing mark is 700. The enrollment fee for each exam is $165.

70-767: Implementing a Data Warehouse using SQL

The Microsoft 70-767 test assesses one’s skills needed to perform well as a data warehouse and ETL developers. Candidates should be ready to demonstrate their abilities in building BI solutions, cleansing data, and implementing ETL and data warehouse. Generally, the exam is divided into 3 domains:

  • Design, implementation, and maintenance of data warehouse (35-40%)
  • ETL procedure (40-45%)
  • Creating data quality solutions (15-20%)

To get through this test, the examinee should have a thorough understanding of dimension tables as well as their design and implementation. The knowledge of different types of dimensions, their requirements, and hierarchies is essential too. In addition, the candidate needs to have an understanding of fact tables, relationships of the tables along with the implementation of data lineage.

It is also necessary for the applicant to be able to identify measures, work with composite keys, understand various types of relationships, etc. Design and implementation of indexes to support the workload of the data warehouse and suitable storage solutions are also the areas that will be tested in this exam.

Finally, the examinee should also be fluent in the design and implementation of ETL control flow as well as data flow and should be able to work with all the involved elements. One has to be able to implement the ETL solution for supporting progressive data loading and extraction along with deploying, configuring, and debugging SSIS packets. Furthermore, the candidate must have insights on creating a knowledge base, be skilled in working with Data Quality Services, maintaining data quality, implementing the Master Data Services model (MDS), and managing data with it.

70-768: Developing SQL Data Models

The Microsoft 70-768 test is aimed at developers who are familiar with Business Intelligence (BI) and are focused on working with BI solutions that implement multi-dimensional and tabular data models, along with OLAP cubes. Its content is categorized into 4 domains:

  • Multi-dimensional BI semantic model design (25-30%)
  • Designing a tabular BI semantic model (20-25%)
  • Development of queries via Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX) (15-20%)
  • Configuration and maintenance of SSAS (30-35%)

For the successful passing of this test, the examinee should be capable of designing and developing multidimensional databases using Microsoft SQL Server Analysis Services or SSAS. The skills in selecting the appropriate storage model, dimensional model and design, and implementation of dimensions in a cube are also needed, as well as the ability to implement various necessary measures and functions.

Knowledge on designing tabular BI semantic model and its elements such as relationships, partitions, calculated columns, etc. is also expected from the candidate. One should be fluent in configuring, managing, and securing the tabular model along with its storage and managing refresh interval settings. Understanding of Direct Query, Teradata, Excel, and Pivot Tables is essential too.

The syllabus of 70-768 exam also includes basic MDX structures and functions and their implementation. Insights on SSAS plans, memory limits, Non-Union Memory Architecture (NUMA), Key Performance Indicators will be beneficial as well, along with the knowledge of how to monitor and analyze performance and query plans through Extended Events and Profiler and how to perform configuration of partition and dimension processing.

Career Opportunities

Acquiring the MCSA: SQL 2016 BI Development certification will verify the candidate's advanced knowledge in ETL and BI solutions. This will open up new career opportunities with decent average annual salaries. Here's are some of them:

  • Data Analyst: $61k
  • SQL 2016 BI Developer: $77k
  • BI Analyst: $69k
  • Database Administrator: $74k

The aforementioned sums of annual compensation are taken from the survey conducted by PayScale.

The responsibilities associated with the listed jobs include collection and interpretation of data, identifying data trends and patterns, setting up business requirements for Business Intelligence tools, reporting curation, performing tasks related to BI software development, reviewing and validating customer data, development of policies for collecting data, and so on.

Next Certificates Available

Since MCSA: SQL 2016 BI Development is an associate-level certification, after earning it, one can opt for other accreditations at this path. First of all, this certificate makes you eligible for MCSE: Data Management and Analytics. Also, professionals can expand their skill set by obtaining same-level certifications in the corresponding areas, such as MCSA: BI Reporting, MCSA: SQL 2016 Database Administration, and MCSA: SQL 2016 Database Development. These ones will pave the path to other expert-level accreditations and improved wages.