All rights reserved. Printed in the United States of America. This publication is protected by copyright, and permission must be obtained from the publisher prior to any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means, electronic, mechanical, photocopying, recording, or likewise. For information regarding permissions, request forms, and the appropriate contacts within the Pearson Education Global Rights & Permissions Department, please visit www.pearsoned.com/permissions/. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
ISBN-13: 978-1-5093-0515-5
ISBN-10: 1-5093-0515-7
Library of Congress Control Number: 2017938462
First Printing May 2017
Trademarks
Microsoft and the trademarks listed at https://www.microsoft.com on the “Trademarks” webpage are trademarks of the Microsoft group of companies. All other marks are property of their respective owners.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an “as is” basis. The authors, the
publisher, and Microsoft Corporation shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book or programs accompanying it.
Special Sales
For information about buying this title in bulk quantities, or for special sales opportunities (which may include electronic versions; custom cover designs; and content particular to your business, training goals, marketing focus, or branding interests), please contact our corporate sales department at corpsales@pearsoned.com or (800) 382-3419.
For government sales inquiries, please contact governmentsales@pearsoned.com.
For questions about sales outside the U.S., please contact intlcs@pearson.com.
Editor-in-Chief
Greg Wiegand
Acquisitions Editor
Trina MacDonald
Development Editor
Troy Mott
Managing Editor
Sandra Schroeder
Senior Project Editor
Tracey Croom
Editorial Production
Backstop Media
Copy Editor
Christina Rudloff
Indexer
Julie Grady
Proofreader
Liv Bainbridge
Technical Editor
Ike Ellis
Cover Designer
Twist Creative, Seattle
Contents at a glance
Introduction
Preparingfortheexam
CHAPTER 1 Design a multidimensional business intelligence (BI) semantic model
CHAPTER 2 Design a tabular BI semantic model
CHAPTER 3 Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)
CHAPTER 4 Configure and maintain SQL Server Analysis Services (SSAS)
Index
Contents
Introduction
Organization of this book
Microsoft certifications
Acknowledgments
Microsoft Virtual Academy
Quick access to online references
Errata, updates, & book support
We want to hear from you
Stay in touch
Preparingfortheexam
Chapter 1 Design a multidimensional business intelligence (BI) semantic model
Skill 1.1: Create a multidimensional database by using Microsoft SQL Server Analysis Services (SSAS)
Design, develop, and create multidimensional databases
Select a storage model
Skill 1.2: Design and implement dimensions in a cube
Select an appropriate dimension model, such as fact, parentchild, roleplaying, reference, data mining, many-to-many, and slowly changing dimension
Implement a dimension type
Define attribute relationships
Skill 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
Chapter summary
Thought experiment
Thought experiment answers
Chapter 2 Design a tabular BI semantic model
Skill 2.1: Design and publish a tabular data model
Design measures, relationships, hierarchies, partitions, perspectives, and calculated columns
Relationships
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
Skill 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
Skill 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
Chapter summary
Thought experiment
Thought experiment answer
Chapter 3 Develop queries using Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX)
Skill 3.1: Create basic MDX queries
Implement basic MDX structures and functions, including tuples, sets, and TopCount
Skill 3.2: Implement custom MDX solutions
Create custom MDX or logical solutions for pre-prepared case tasks or business rules
Define a SCOPE statement
Skill 3.3: Create formulas by using the DAX language
Use the EVALUATE and CALCULATE functions
Filter DAX queries
Create calculated measures
Perform analysis by using DAX
Chapter summary
Thought experiment
Thought experiment answer
Chapter 4 Configure and maintain SQL Server Analysis Services (SSAS)
Skill 4.1: Plan and deploy SSAS
Configure memory limits
Configure Non-Union Memory Access (NUMA)
Configure disk layout
Determine SSAS instance placement
Skill 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
Skill 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
Skill 4.4: Create Key Performance Indicators (KPIs) and translations
Create KPIs in multidimensional models and tabular models
Configure KPI options, including Associated measure group, Value Expression, Goal Expression, Status, Status expression, Trend, Trend expression, and Weight
Create and develop translations
Chapter summary
Thought experiment
Thought experiment answer
Index
What do you think of this book? We want to hear from you!
Microsoft is interested in hearing your feedback so we can continually improve our books and learning resources for you. To participate in a brief online survey, please visit: https://aka.ms/tellpress
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/enus/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/enus/sql/ssms/download-sql-server-management-studio-ssmsand https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-datatools-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 mspinput@microsoft.com.
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:
https://aka.ms/tellpress
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.
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.
Chapter 1. Design a multidimensional business intelligence (BI) semantic model
Important Have you read page xiii?
It contains valuable information regarding the skills you need to pass the exam.
A business intelligence semantic model is a semantic layer that you create to represent and enhance data for use in reporting and analysis applications. Microsoft SQL Server Analysis Services (SSAS) supports two types of business intelligence semantic models: multidimensional and tabular. In this chapter, we review the skills you need to create a multidimensional database, whereas we explore the skills necessary for creating a tabular model in Chapter 2, “Design a tabular BI semantic model.” We start with the steps required to physically instantiate a multidimensional database on an SSAS server. Then we work through the steps to perform, and the decisions to consider, for the two main objects in a multidimensional database, dimensions and measures.
Skills in this chapter:
Create a multidimensional database by using Microsoft SQL Server Analysis Services (SSAS)
Design and implement dimensions in a cube
Implement measures and measure groups in a cube
Skill 1.1: Create a multidimensional database by using Microsoft SQL Server Analysis Services (SSAS)
Before you start the development process for a multidimensional database, you should spend some time thinking about its design and preparing your data for the new database. You are then ready to set up the database on the SSAS server and choose how you want SSAS to store data in the database.
This section covers how to:
Design, develop, and create multidimensional databases
Select a storage model
Design, develop, and create multidimensional databases
The design process begins with an understanding of how people ask questions about their business. That is, you must decide how to translate your business requirements into a data model that is suitable as a source for a multidimensional database. Then after loading data into this data model, a process that is not covered in the exam, you proceed by creating a multidimensional database project in SQL Server Data Tools for Visual Studio 2015 (SSDT).
During the development process, you create supporting objects such as a data source and a data source view to define connectivity to your data model and to provide an abstraction layer for that data that you use for developing dimensions, measures, and cubes. As you work through each step, you deploy each newly created object to a multidimensional database on the SSAS server so you can test your work and ensure business requirements are met.
Source table design
An online transactional processing (OLTP) database is structured in third normal form with efficiency of storage and optimization of write operations, or low-volume read operations in mind. An SSAS multidimensional database is an online analytical processing (OLAP) database that is optimized for read operations of high-volume data. If you do not already have a data warehouse to use as a source for your multidimensional database, you should design a new data model in a relational database in which to store data that loads into SSAS.
Before you start the design of a new data model to use as a source for your multidimensional database, you should spend time understanding how the business users want to analyze data. You can interview them to find out the types of questions they want to answer with data analysis, and review the reports they use to find clues about important analytical elements. In particular, you want to discover the measures and dimensions that you need to create in the new data model. Measuresare the numeric values to be analyzed, such as total sales, and dimensionsare the people, places, things, and dates that provide context to these values. In this chapter, you learn how to develop a multidimensional database that can answer the following types of questions, also known as the business requirements, based on data for a fictional company, Wide World Importers:
What is the quantity sold of items by date, customer, salesperson, or location?
How many items are sold by color or by size?
How many items that require chilling are sold as compared to stock items that do not require chilling (dry items)?
How many sales occurred by date, customer, salesperson, or location?
What are total sales (with and without tax included), taxes, and profit by date, salesperson, location, or item?
When reviewing individual transactions, what is the tax rate and what is the unit price per item sold?
For each customer billed for sales, how do those sales break down by the customers receiving the items?
What reasons do customers give for making a purchase and how do sales dollars and sales counts break down by sales reason?
How many distinct items are sold by date, customer, salesperson, or location?
How many items are in inventory by date and what are the target stock levels and reorder points for each item?
When you evaluate questions, look for clues to measure, such as “how many,” “total,” “dollars,” or “count.” You should also note whether a value can be obtained directly from the OLTP system, or whether it must be calculated. If a value is calculated, decide whether it can be calculated on a scalar basis (row by row), and whether summing the calculated results can derive a grand total. Be on the alert for different terms that refer to the same measure, and then consult with your business users to determine which term to use in the multidimensional database. Using these criteria, the following measures emerge from the business requirements:
Quantity
Stock Item Distinct Count
Chiller Items Count
Dry Items Count
Sale Count
Sales Amount With Tax
Sales Amount Without Tax
Tax Amount
Tax Rate
Unit Price
Quantity On Hand
Reorder Level
Target Stock Level
Your next step is to review the business requirements again to identify dimensions. A common clue for a dimension is the word “by” in front of a candidate dimension, although sometimes it is only implicitly included in the requirements. A second review of the business requirements for Wide World Importers yields the following dimensions:
Date
Sometimes there are multiple dates associated with a transaction. It is important to know how each user community within your organization associates data with dates. At Wide World Importers, the sales department is interested in analyzing sales by invoice date, whereas the warehouse department wants to review sales by delivery date.
Customer
Employee
City
Stock
Item
One of the Wide World Importers requirements is to analyze sales by color or size of an item. Although the word “by” is a clue, color and size are more accurately descriptors or characteristics of an item and therefore become part of a single dimension table for items. You do not normally create separate dimension tables for characteristics like this.
Need More Review? Dimensional modeling techniques
Ralph Kimball, the father of dimensional modeling, has several books and online resources that describe this topic in detail. A good place to start is “Dimensional Modeling Techniques” at http://www.kimballgroup.com/data-warehouse-businessintelligence-resources/kimball-techniques/dimensionalmodeling-techniques/.
In an ideal data model for a multidimensional database, data is denormalized to minimize the number of joins across multiple tables
by using a starschema, which consists of dimension tables and at least one fact table in which measures are stored. If you create a diagram by placing the fact table in the center and surround it by related dimensions, the diagram resembles a star shape, as shown in Figure 1-1. This example of a star schema is a selection of six tables from the WideWorldImportersDW sample database for SQL Server 2016 that answer some of the questions established as the requirements for the multidimensional database that you build throughout this chapter.
FIGURE 1-1 Star schema for a subset of tables in WideWorldImportersDW
Note WideWorldImportersDW sample database
You can download the WideWorldImportersDW sample database from https://github.com/Microsoft/sql-serversamples/releases/tag/wide-world-importers-v1.0. Installation instructions are at https://msdn.microsoft.com/library/mt734217.aspx.
Although a star schema is not required as a source for SSAS, it is a preferred structure for enterprise-scale multidimensional models for several reasons. First, the impact of accessing the OLTP system from SSAS adds resource contention to your environment that you can avoid by creating a separate data source. (The degree of impact on the OLTP depends on the storage model you select as explained in the “Select a storage model” section in this chapter.) Second, the amount of time to move data from the OLTP system into the multidimensional database is sometimes less optimal than it can be if you restructure the data first. Third, sometimes analysis requires access to historical information that is no longer preserved in the source system. Having a separate data model in which you store data as it changes becomes a necessity in that case. Other reasons for creating a separate data model include, but are not limited to, cleansing data that cannot be corrected in the OLTP system, having the results available not only to the multidimensional database, but other downstream reporting systems (often with better performance than querying the OLTP directly), and integrating data from multiple OLTP systems.
Note Snowflake dimension design
Another type of design that you can implement for a dimension is a snowflake, in which you use multiple related tables for a single dimension. In traditional dimension modeling, the use of snowflake dimensions is not considered best practice because it adds joins back into the data model that a star schema design seeks to eliminate. For relational reporting scenarios, the additional joins can have an adverse impact on performance. However, when you use SSAS in its default storage mode, the snowflake structure has no impact on performance and can be a preferable design when you need to support analysis across fact tables having different levels of granularity, or to simplify the process that loads the dimension from the OLTP source. You can learn more about why you might use a snowflake dimension and how to design one properly in a series of blog posts by Jason Thomas that begins at http://sqljason.com/2011/05/when-and-how-to-snowflakedimension.html.
To load the star schema with data on a periodic basis, you use an extract-transform-load (ETL) tool, such as SQL Server Integration Services (SSIS). The ETL tool is typically scheduled to run nightly to load new and changed rows into the star schema, although business requirements might dictate a different frequency, such as every five minutes when low latency is required, or once per week for source data that changes infrequently.
Exam Tip
Because the focus of the 70-768 exam is on the implementation of Analysis Services models, this exam reference does not explain how to convert data from an OLTP structure such as Wide World Importers to a star schema structure suitable for OLAP. The assumption for the exam is that the design of the dimension and fact tables is complete and an ETL process has loaded the tables with data from the source OLTP system. Nonetheless, you should be familiar with star schema concepts and terminology and understand how to implement Analysis Services features based on specific data characteristics and analysis requirements.
A dimensiontablecontains data about the entities that a business user wants to analyze—typically a person, place, thing, or point in time. One consideration when designing a dimension table is whether to track history. A slowlychangingdimension(SCD) is a dimension for which you implement specific types of columns and ETL techniques specifically to address how to manage table updates when data changes. You make this design decision for each dimension separately based on business requirements. The two more common approaches to managing history include the following SCD types:
Type 1 Only current data is tracked. The ETL process updates columns with changed values and loses the values previously stored in those columns. For example, you might not track history for employee name changes, as shown in Figure 1-2, because the effect on sales is not likely to be relevant.
