Exam 70-768: Developing SQL Data Models

Microsoft 70-768 exam is intended for business intelligence (BI) developers who focus on creating BI solutions that require implementing multidimensional data models, implementing and maintaining OLAP cubes, and implementing tabular data models. The exam measures the ability to design multidimensional BI models and tabular models and develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX).

Who should take the exam?

This exam is intended for business intelligence (BI) developers who focus on creating BI solutions that require implementing multidimensional data models, implementing and maintaining OLAP cubes, and implementing tabular data models.

Learning Objectives

Microsoft being a dominant player constantly provides relevant and required documentation to meet the needs of all of today’s diverse learners and therefore, helping them to build a life in the world of cloud computing. As a result, the potential aspirants are easily able to locate the required detailed information along with the subtopics included in each domain.

The Microsoft 70-768 exam focuses on four key areas: 

• Design a multidimensional business intelligence (BI) semantic model
• Design a tabular (BI) semantic model
• Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)
• configure and maintain SQL Server Analysis Services (SSAS)

Exam Format

Microsoft 70-768 exam consists of around 40-60 questions covering all topics and their respective subtopics. And to complete these questions, a candidate is given only 120 minutes. Candidates have to secure a score of 700 or more points in order to pass the exam. The fee for this examination is $165 USD. Further, the exam is only available in the English language.

• Exam Name: Developing SQL Data Models
• Exam Code: 70-768
• Exam Price: $165 USD
• Exam Duration: 120 minutes
• Passing score: 700 or more
• Language Available: English

Schedule the exam

Candidates can schedule their exam at the Microsoft portal and schedule their exam by clicking on the “schedule exam” link.

Course Structure

Design a multidimensional business intelligence (BI) semantic model

1.1 Create a multidimensional database by using Microsoft SQL Server Analysis Services (SSAS)

• Design, develop, and create multidimensional databases; select a storage model (Microsoft Documentation: Creating Multidimensional Models Using SQL Server Data Tools)

1.2 Design and implement dimensions in a cube

• Select an appropriate dimension model, such as fact, parent-child, roleplaying, reference, data mining, many-to-many, and slowly changing dimension; implement a dimension type; define attribute relationships (Microsoft Documentation: Dimensions)

1.3 Implement measures and measure groups in a cube

• Design and implement measures, measure groups, granularity, calculated measures, and aggregate functions; define semi-additive behavior (Microsoft Documentation: Measures and Measure Groups)

Design a tabular BI semantic model

2.1 Design and publish a tabular data model

• Design measures, relationships, hierarchies, partitions, perspectives, and calculated columns; create a time table; publish from Microsoft Visual Studio; import from Microsoft PowerPivot; select a deployment option, including Processing Option, Transactional Deployment, and Query Mode (Microsoft Documentation: Tabular model designer)

2.2 Configure, manage, and secure a tabular model

• Configure tabular model storage and data refresh, configure refresh interval settings, configure user security and permissions, configure row-level security (Microsoft Documentation: Roles)

2.3 Develop a tabular model to access data in near real time

• Use DirectQuery with Oracle, Teradata, Excel, and PivotTables; convert in-memory queries to DirectQuery (Microsoft Documentation: Create a tabular model project)

Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)

3.1 Create basic MDX queries

• Implement basic MDX structures and functions, including tuples, sets, and TopCount (Microsoft Documentation: MDX Query – The Basic Query)

3.2 Implement custom MDX solutions

• Create custom MDX or logical solutions for pre-prepared case tasks or business rules, define a SCOPE statement (Microsoft Documentation: Comparing tabular and multidimensional solutions)

3.3 Create formulas by using the DAX language

• Use the EVALUATE and CALCULATE functions, filter DAX queries, create calculated measures, perform data analysis by using DAX (Microsoft Documentation: Apply DAX basics in Power BI Desktop)

Configure and maintain SQL Server Analysis Services (SSAS)

4.1 Plan and deploy SSAS

• Configure memory limits, configure Non-Union Memory Architecture (NUMA), configure disk layout, determine SSAS instance placement (Microsoft Documentation: Deploy)

4.2 Monitor and optimize performance

• Monitor performance and analyze query plans by using Extended Events and Profiler, identify bottlenecks in SSAS queries, monitor processing and query performance, resolve performance issues, configure usability limits, optimize and manage model design (Microsoft Documentation: Monitor and optimize on-premises data gateway performance)

4.3 Configure and manage processing

• Configure partition processing; configure dimension processing; use Process Default, Process Full, Process Clear, Process Data, Process Add, Process Update, Process Index, Process Structure, and Process Clear Structure processing methods; configure Parallel, Sequential, and Writeback processing settings (Microsoft Documentation: Configure and use process manufacturing in Dynamics 365 Supply Chain Management)

4.4 Create Key Performance Indicators (KPIs) and translations

• Configure KPI options, including Associated measure group, Value Expression, Goal Expression, Status, Status expression, Trend, Trend expression, and Weight; create KPIs in multidimensional models and tabular models; create and develop translations (Microsoft Documentation: KPIs)

Exam Policies

Microsoft is well aware of its responsibilities towards its candidates, thus, it provides exam policies with a view to enabling the candidates to plan and manage their activities with respect to Microsoft. Microsoft Certification exam policies are an appropriate blend of all the exam-related details, accompanying the before and after exam procedures. These exam policies include a set of rules that need to be followed during the exam time or at testing centres.

For More Queries Visit: Developing SQL Data Models (70-768) Exam FAQs

Microsoft 70-768 Exam Preparation

Online Training

The Microsoft Learning Platform provides the most authentic access to candidates to get information about the exam. Candidates appearing for the 70-768 exam can refer to these study resources that can be accessed easily at the official exam page. That is to say, you just have to search for the 70-768 exam and the essential information will be provided that will help you in understanding it better.

Books

Books help you acquire more and more knowledge through training. They are your oldest yet best friends who are always happy to help you with your preparation process. As you know, Books are a good resource to acquire knowledge without any disturbance. So here are a few books that would be a good option for you people out there. 

• Exam Ref 70-768 Developing SQL Data Models by Stacia Varga.
• Designing Data-Intensive Applications: The Big Ideas behind Reliable, Scalable, and Maintainable Systems by Martin Kleppmann.

Instructor Led Training

You will feel lucky to know that Microsoft provides free online training as well as instructor-led training to its potential candidates. These are basically online learning and training sessions by industry-recognized experts and certified instructors. This training also comes with few benefits such as- increased convenience, flexible scheduling, and quick responses as well.

Practice Tests

Practice tests are perfect to get an insight into the topics in which you are lacking. However, these practice tests should only be performed after you have gone through the whole syllabus. Practice tests are intended to help candidates encounter the real exam environment around them. After completing practice tests you can easily understand the areas you’re lacking behind and work upon them.

Get ready to Practice and Prepare for Developing SQL Data Models (70-768) Exam