New book: Exam Ref 70-768 Developing SQL Data Models

  • Article

We’re pleased to announce the availability of Exam Ref 70-768 Developing SQL Data Models (ISBN 9781509305155), by Stacia Varga.

Purchase from these online retailers:

Microsoft Press Store Amazon Barnes & Noble Independent booksellers – Shop local

Overview

Prepare for Microsoft Exam 70-768—and help demonstrate your real-world mastery of Business Intelligence (BI) solutions development with SQL Server 2016 Analysis Services (SSAS), including modeling and queries. Designed for experienced IT professionals ready to advance their status, Exam Ref focuses on the critical thinking and decision-making acumen needed for success at the MCSA level.

Focus on the expertise measured by these objectives:

  • Design a multidimensional BI semantic model
  • Design a tabular BI semantic model
  • Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)
  • Configure and maintain SSAS

This Microsoft Exam Ref:

  • Organizes its coverage by exam objectives
  • Features strategic, what-if scenarios to challenge you
  • Assumes you are a database or BI professional with experience creating models, writing MDX or DAX queries, and using SSAS

Introduction

The 70-768 exam is designed for Business Intelligence (BI) developers and solution architects to test knowledge of a wide range of topics related to the design, querying, configuration, and administration of BI semantic models in Microsoft SQL Server 2016 Analysis Services (SSAS). Approximately half the exam covers multidimensional BI semantic models, while the remainder covers tabular BI semantic models. In addition, there are new development and query language features available for tabular models developed in SQL Server 2016, with which you should be familiar to successfully pass this exam.

For multidimensional models, the exam focuses not only on the steps required to design and develop the model in SQL Server Data Tools (SSDT), but also tests your understanding of the supported types of dimension models, the options for implanting measures, and the configuration of properties to enable specific behaviors, such as slowly changing dimensions and semi-additivity. It also covers the usage of Multidimensional Expressions (MDX), both to query the model and to embed business logic into the model in the form of calculated measures, named sets, Key Performance Indicators (KPIs), and additions to the MDX script.

The exam’s coverage of tabular models requires you to understand how to import data into a model or use DirectQuery mode, and how the implementation of DirectQuery mode impacts the model development process. You must also know how to enhance the model by defining relationships between tables, adding measures and calculated columns by using Data Analysis Expressions (DAX), configuring partitions, and setting properties of model objects. Furthermore, you must know how to create KPIs from calculated measures and how to use DAX to write analytical queries.

Additionally, the exam focuses on considerations related to deploying and securing models and keeping them up-to-date by choosing the appropriate processing options for specific scenarios. It also requires you to understand how to use various tools to monitor and troubleshoot performance of BI semantic models and identify the necessary steps to take to resolve performance issues. Other areas of focus include the deployment and configuration of Analysis Services instances for memory management and scale-out scenarios.

To supplement your real-world experience with BI semantic models, this book reviews the concepts covered by the exam by using many different examples that you can follow yourself. To do this, you must install the SQL Server 2016 database engine and Analysis Services. For more information about SQL Server 2016 installation, see https://msdn.microsoft.com/en-us/library/ms143219.aspx. You must also download and install SQL Server Management Studio (SSMS) from and SQL Server Data Tools for Visual Studio 2015 (SSDT) from /en-us/sql/ssms/download-sql-server-management-studio-ssms and /en-us/sql/ssdt/download-sql-server-data-tools-ssdt, respectively.

This book covers every major topic area found on the exam, but it does not cover every exam question. Only the Microsoft exam team has access to the exam questions, and Microsoft regularly adds new questions to the exam, making it impossible to cover specific questions. You should consider this book a supplement to your relevant real-world experience and other study materials. If you encounter a topic in this book that you do not feel completely comfortable with, use the “Need more review?” links you’ll find in the text to find more information and take the time to research and study the topic. Great information is available on MSDN, TechNet, and in blogs and forums.

Important: How to use this book to study for the exam

Certification exams validate your on-the-job experience and product knowledge. To gauge your readiness to take an exam, use this Exam Ref to help you check your understanding of the skills tested by the exam. Determine the topics you know well and the areas in which you need more experience. To help you refresh your skills in specific areas, we have also provided “Need more review?” pointers, which direct you to more in-depth information outside the book.

The Exam Ref is not a substitute for hands-on experience. This book is not designed to teach you new skills.

We recommend that you round out your exam preparation by using a combination of available study materials and courses. Learn more about available classroom training at https://www.microsoft.com/learning. Microsoft Official Practice Tests are available for many exams at https://aka.ms/practicetests. You can also find free online courses and live events from Microsoft Virtual Academy at https://www.microsoftvirtualacademy.com.

This book is organized by the “Skills measured” list published for the exam. The “Skills measured” list for each exam is available on the Microsoft Learning website: https://aka.ms/examlist.

Note that this Exam Ref is based on publicly available information and the author’s experience. To safeguard the integrity of the exam, authors do not have access to the exam questions.

About the author

STACIA VARGA, Microsoft MVP (Data Platform) is a consultant, educator, mentor, and author specializing in data solutions since 1999. She provides consulting and custom education services through her company, Data Inspirations, writes about her experiences with data at blog.datainspirations.com, and tweets as @_StaciaV_.