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 https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms and https://docs.microsoft.com/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.
Organization of this book
This book is organized by the “Skills measured” list published for the exam. The “Skills measured” list is available for each exam on the Microsoft Learning website: https://aka.ms/examlist. Each chapter in this book corresponds to a major topic area in the list, and the technical tasks in each topic area determine a chapter’s organization. If an exam covers six major topic areas, for example, the book will contain six chapters.
Microsoft certifications
Microsoft certifications distinguish you by proving your command of a broad set of skills and experience with current Microsoft products and technologies. The exams and corresponding certifications are developed to validate your mastery of critical competencies as you design and develop, or implement and support, solutions with Microsoft products and technologies both on-premises and in the cloud. Certification brings a variety of benefits to the individual and to employers and organizations.
More Info All Microsoft certifications
For information about Microsoft certifications, including a full list of available certifications, go to https://www.microsoft.com/learning.
Acknowledgments
Stacia Varga There are many people behind the scenes who make this book possible. Front and center on my mind are the people who ensure that my words make sense grammatically and are technically accurate. Thanks to Ike Ellis, Troy Mott, and Christina Rudloff for fulfilling that role. Also, I’d like to thank Trina MacDonald and her team at Pearson for ensuring the book production process runs smoothly. Throughout my career, there have been many individuals who have helped me fill in the gaps of knowledge about Analysis Services. While writing this book, so many memories came to mind reaching back to my start with SQL Server 2000 Analysis Services long ago with Mike Luckevitch, Scott Cameron, Hilary Feier, Liz Vitt, Scot Reagin, Dan Reh, and Denny Lee among others at the entity that began as EssPro, merged with Aspirity, and later was acquired by Hitachi Consulting. Most of all, my thoughts were with Reed Jacobson who was my mentor in many ways, but in particular he guided my deep learning of MDX and coached me during the writing of my first few books. He is gone now, but definitely not forgotten. Meanwhile, my wonderfully patient husband Dean Varga kept the coffee pot warm and my world relatively peaceful so I could write yet another book.
Microsoft Virtual Academy
Build your knowledge of Microsoft technologies with free expert-led online training from Microsoft Virtual Academy (MVA). MVA offers a comprehensive library of videos, live events, and more to help you learn the latest technologies and prepare for certification exams. You’ll find what you need here:
https://www.microsoftvirtualacademy.com
Quick access to online references
Throughout this book are addresses to webpages that the author has recommended you visit for more information. Some of these addresses (also known as URLs) can be painstaking to type into a web browser, so we’ve compiled all of them into a single list that readers of the print edition can refer to while they read.
Download the list at https://aka.ms/examref768/downloads.
The URLs are organized by chapter and heading. Every time you come across a URL in the book, find the hyperlink in the list to go directly to the webpage.
Errata, updates, & book support
We’ve made every effort to ensure the accuracy of this book and its companion content. You can access updates to this book—in the form of a list of submitted errata and their related corrections—at:
https://aka.ms/examref768/errata
If you discover an error that is not already listed, please submit it to us at the same page.
If you need additional support, email Microsoft Press Book Support at [email protected].
Please note that product support for Microsoft software and hardware is not offered through the previous addresses. For help with Microsoft software or hardware, go to https://support.microsoft.com.
Download the source code and sample database from the book’s website
https://aka.ms/examref768/detail
We want to hear from you
At Microsoft Press, your satisfaction is our top priority, and your feedback our most valuable asset. Please tell us what you think of this book at:
We know you’re busy, so we’ve kept it short with just a few questions. Your answers go directly to the editors at Microsoft Press. (No personal information will be requested.) Thanks in advance for your input!
Stay in touch
Let’s keep the conversation going! We’re on Twitter: http://twitter.com/MicrosoftPress.