About Microsoft 70-767 Exam
Microsoft 70-767: Implementing a Data Warehouse using SQL is a qualifying exam for the MCSA: SQL 2016 BI Development as well as the MCSE: Data Management and Analytics certifications. The candidates for this test must be able to demonstrate their knowledge and skills in designing, implementing, and keeping a data warehouse; extracting, converting, and loading data; and building quality data solutions.
Microsoft 70-767: Who is this exam for?
This certification exam is intended for the IT professionals working in the positions of a Data Warehouse Developer or an Extract, Transform, and Load (ETL) Specialist. These are those individuals who develop business intelligence solutions. They are also responsible for data cleansing as well as data warehouse and ETL implementation.
Microsoft 70-767: What are the exam prerequisites?
Microsoft 70-767 is a certification exam that does not have any official prerequisites. However, the vendor recommends that the candidates planning to take this test have a strong understanding of the exam objectives.
Microsoft 70-767: What exam details should you know?
The Microsoft 70-767 exam is a 120-minute test consisting of 40-60 questions. It covers different question formats. Some of these include multiple choice, short answer, single answer, active screen, scenario-based, build list, drag and drop, and case study. The students can take this exam in the English language only. The passing score for the 70-767 certification test is 700 points (on a scale of 100-1000).
To register for the exam, it is required that the test takers visit the Pearson VUE platform and pay the fee of $165 plus the applicable taxes. This sum refers to a single delivery of the exam. This means that if a student does not pass this certification test on the first try, he or she will be required to pay another fee to retake it. You can retake the exam 24 hours after a failed attempt. Please note that you can do it five times a year.
Microsoft 70-767: What is the skills outline?
The Microsoft 70-767 exam is designed to measure specific technical tasks. They make up the topics of this certification test. The details of these objectives are highlighted below:
Designing, Implementing & Maintaining a Data Warehouse: 35-40%
- Designing and implementing dimension tables: the test takers should have the relevant skills in designing conformed and shared dimensions; determining support prerequisites for slowing altering dimensions; determining attributes; determining snowflake or star schema prerequisites; designing hierarchies; determining the granularity of relationships with fact tables; determining years and core relationships for a data warehouse; implementing data lineage for a dimension table; determining lineage or auditing requirements.
- Designing and implementing fact tables: this requires one’s skills in identifying measures; identifying relationships in a dimension table; creating composite keys; implementing semi-additive measures; designing a data warehouse that sustains many-to-many relationships; implementing non-additive measures.
- Designing and implementing indexes for data warehouse workloads: this covers the students’ competence in designing indexing solutions; choosing relevant indexes; implementing non-clustered, clustered, columnstore, and filtered indexes.
- Designing storage for a data warehouse: this requires your competence in designing the relevant storage solution, which includes file layout, disk, and data warehouse.
- Designing and implementing partitioned tables views: this involves designing a partition structure to sustain a data warehouse; implementing sliding windows; designing a partition structure that sustains quick loading and scale-out of data; implementing partition elimination.
Extracting, Transforming, & Loading Data: 40-45%
- Designing & implementing an ETL (Extract, Transform, and Load) control flow through the use of SSIS (SQL Server Integration Services): this includes the candidates’ abilities, such as designing and implementing the ETL control-flow components, for example, precedence constraints, tasks, and containers; developing parameters and variables; implementing data profiling, transactions, security, logging, and parallelism; developing checkpoints, loop containers, variables and sequence in SSIS.
- Designing & implementing ETL data flow with the use of SSIS package: this requires the individuals’ skills in implementing slowly altering dimension, audit, fuzzy lookup, fuzzy grouping, term-lookup transformation, blocking, and non-blocking. It also covers their skills in determining the relevant transform objects for specific tasks; map columns; determining the relevant scenarios for Transact-SQL joins versus SSIS lookup; removing additional bad rows or extra rows with the use of de-duplication; designing table loading with the use of standard loading or bulk loading.
- Implementing an ETL solution that sustains incremental data extraction: this involves the learners’ skills in designing fact table patterns; enabling of Change Data Capture; developing the SQL-MERGE statement.
- Implementing an ETL solution that sustains incremental data loading: this covers one’s competence in designing control flow for loading change data; loading data with the use of Transact-SQL Chance Data Capture features; loading data with the use of Change Data Capture within SSIS.
- Debugging SSIS packages: this requires the examinees’ skills in fixing performance, execution, connectivity, and failed logic concerns with the debugger; allowing logging for package execution; implementing breakpoint; implementing error-handling for types of data; adding data viewers; performing a batch clean up; profiling data with diverse tools.
- Deploying and configuring SSIS projects and packages: this entails creating an SSIS catalog; running and customizing packages with the use of DTUTIL; deploying packages with the use of deployment utility; file systems, and SQL Server.
Building Data Quality Solutions: 15-20%
- Developing the knowledge base: this covers the applicants’ skills in creating the DQS (Data Quality Services) knowledge base; performing domain management; performing knowledge discovery; determining the relevant usage cases for the DQS knowledge base.
- Sustaining data quality with the use of DQS: this requires that the candidates know how to prepare DQS for data de-duplication; include matching knowledge to the knowledge base; clean data with the SSIS DQS task; clean data with DQS knowledge; install DQS.
- Implement a MDS (Master Data Services) model: this covers your skills in installing MDS; creating models, hierarchies, entities, attributes, and collections; implementing MDS; defining security roles; creating and editing subscriptions; importing and exporting data; implementing entities, hierarchies, business rules, and attributes.
- Manage data with MDS: the test takers should be able to utilize MDS tools; utilize MDS Configuration Manager; deploy a sample model with MDSModelDeploy.exe; utilize MDS Add-on in Excel; develop subscription views; stage and load data; develop Master Data Management (MDM) hub; utilize MDS web applications.
Microsoft 70-767: What are the career opportunities?
The professionals with the MCSA certification can explore a wide range of job roles, including a Database Designer, a Database Administrator, a Database Developer, an IT Director, an IT Manager, a Senior System Engineer, and a Systems Administrator. In one of these positions, you can count on the average salary between $45,600 and $136,150 per annum.