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

Preparing for the exam

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, parent-child, 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