Tabular Modeling in Microsoft SQL Server Analysis Services (Developer Reference)

748 Pages·2019·33.61 MB·English

by  Marco Russo and Alberto Ferrari

Tabular Modeling in Microsoft SQL Server
Analysis Services
Second Edition
Marco Russo and Alberto Ferrari
PUBLISHED BY
Microsoft Press
A division of Microsoft Corporation
One Microsoft Way
Redmond, Washington 98052-6399
Copyright © 2017 by Marco Russo and Alberto Ferrari
All rights reserved. No part of the contents of this book may be reproduced or transmitted in any form
or by any means without the written permission of the publisher.
Library of Congress Control Number: 2016931110
ISBN: 978-1-5093-0277-2
Printed and bound in the United States of America.
First Printing
Microsoft Press books are available through booksellers and distributors worldwide. If you need
support related to this book, email Microsoft Press Support at [email protected]. Please tell
us what you think of this book at https://aka.ms/tellpress.
This book is provided “as-is” and expresses the author’s views and opinions. The views, opinions
and information expressed in this book, including URL and other Internet website references, may
change without notice.
Some examples depicted herein are provided for illustration only and are fictitious. No real
association or connection is intended or should be inferred.
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.
Acquisitions Editor: Devon Musgrave
Editorial Production: Polymath Publishing
Technical Reviewer: Ed Price
Copy Editor: Kate Shoup
Layout Services: Shawn Morningstar
Proofreader: Corina Lebegioara
Indexing Services: Kelly Talbot Editing Services
Illustrator: Daniele Perilli
Cover: Twist Creative • Seattle
Contents at a Glance
Foreword
Introduction
Chapter 1 Introducing the tabular model
Chapter 2 Getting started with the tabular model
Chapter 3 Loading data inside Tabular
Chapter 4 Introducing calculations in DAX
Chapter 5 Building hierarchies
Chapter 6 Data modeling in Tabular
Chapter 7 Tabular Model Scripting Language (TMSL)
Chapter 8 The tabular presentation layer
Chapter 9 Using DirectQuery
Chapter 10 Security
Chapter 11 Processing and partitioning tabular models
Chapter 12 Inside VertiPaq
Chapter 13 Interfacing with Tabular
Chapter 14 Monitoring and tuning a Tabular service
Chapter 15 Optimizing tabular models
Chapter 16 Choosing hardware and virtualization
Index
Contents
Foreword
Introduction
Who should read this book
Who should not read this book
Organization of this book
Conventions and features in this book
System requirements
Code samples
Acknowledgments
Errata and book support
We want to hear from you
Stay in touch
Chapter 1. Introducing the tabular model
Semantic models in Analysis Services
What is Analysis Services and why should I use it?
A short history of Analysis Services
Understanding Tabular and Multidimensional
The tabular model
The multidimensional model
Why have two models?
The future of Analysis Services
Azure Analysis Services
Choosing the right model for your project
Licensing
Upgrading from previous versions of Analysis Services
Ease of use
Compatibility with Power Pivot
Compatibility with Power BI
Query performance characteristics
Processing performance characteristics
Hardware considerations
Real-time BI
Client tools
Feature comparison
Understanding DAX and MDX
The DAX language
The MDX language
Choosing the query language for Tabular
Introduction to Tabular calculation engines
Introduction to VertiPaq
Introduction to DirectQuery
Tabular model compatibility level (1200 vs. 110x)
Analysis Services and Power BI
Summary
Chapter 2. Getting started with the tabular model
Setting up a development environment
Components of a development environment
Licensing
Installation process
Working with SQL Server Data Tools
Creating a new project
Configuring a new project
Importing from Power Pivot
Importing from Power BI
Importing a Deployed Project from Analysis Services
Contents of a tabular project
Building a simple tabular model
Loading data into tables
Working in the diagram view
Navigating in Tabular Model Explorer
Deploying a tabular model
Querying tabular models with Excel
Connecting to a tabular model
Using PivotTables
Using slicers
Sorting and filtering rows and columns
Using Excel cube formulas
Querying tabular models with Power BI Desktop
Creating a connection to a tabular model
Building a basic Power BI report
Adding charts and slicers
Interacting with a report
Working with SQL Server Management Studio
Importing from Power Pivot
Importing from Power BI Desktop
Using DAX Studio as an alternative to SSMS
Summary
Chapter 3. Loading data inside Tabular
Understanding data sources
Understanding impersonation
Understanding server-side and client-side credentials
Working with big tables
Loading from SQL Server
Loading from a list of tables
Loading from a SQL query
Loading from views
Opening existing connections
Loading from Access
Loading from Analysis Services
Using the MDX editor
Loading from a tabular database
Loading from an Excel file
Loading from a text file
Loading from the clipboard
Loading from a Reporting Services report
Loading reports by using the report data source
Loading reports by using data feeds
Loading from a data feed
Loading from SharePoint
Choosing the right data-loading method
Summary
Chapter 4. Introducing calculations in DAX
Introduction to the DAX language
DAX syntax
DAX data types
DAX operators
Column reference and measures reference
Aggregate functions
Table functions
Evaluation context
CALCULATE and CALCULATETABLE
Variables
Measures
Calculated columns
Calculated tables
Writing queries in DAX
Formatting DAX code
DAX Formatter, DAX Studio, and DAX Editor
Summary
Chapter 5. Building hierarchies
Basic hierarchies
What are hierarchies?
When to build hierarchies
Building hierarchies
Hierarchy design best practices
Hierarchies spanning multiple tables
Natural and unnatural hierarchies
Parent-child hierarchies
What are parent-child hierarchies?
Configuring parent-child hierarchies
Unary operators
Summary
Chapter 6. Data modeling in Tabular
Understanding different data-modeling techniques
Using the OLTP database
Working with dimensional models
Working with slowly changing dimensions
Working with degenerate dimensions
Using snapshot fact tables
Using views to decouple from the database
Relationship types
Cardinality of relationships
Filter propagation in relationships
Active state of relationships
Implementing relationships in DAX
Normalization versus denormalization
Calculated tables versus an external ETL
Circular reference using calculated tables
Summary
Chapter 7. Tabular Model Scripting Language (TMSL)
Defining objects in TMSL
The Model object
The DataSource object
The Table object
The Relationship object
The Perspective object
The Culture object
The Role object
TMSL commands
Object operations in TMSL
Data-refresh and database-management operations in TMSL
Scripting in TMSL
Summary
Chapter 8. The tabular presentation layer
Setting metadata for a Date table
Naming, sorting, and formatting
Naming objects
Hiding columns and measures
Organizing measures and columns
Sorting column data
Formatting
Perspectives
Power View–related properties
Default field set
Table behavior properties
Key performance indicators
Translations
Creating a translation file
Writing translated names in a translation file
Choosing an editor for translation files
Importing a translation file
Testing translations using a client tool
Removing a translation
Best practices using translations
Selecting culture and collation in a tabular model
Changing culture and collation using an integrated workspace
Changing culture and collation using a workspace server
Summary
Chapter 9. Using DirectQuery
Configuring DirectQuery
Setting DirectQuery in a development environment
Setting DirectQuery after deployment
Limitations in tabular models for DirectQuery
Supported data sources
Restrictions for data sources
Restrictions for data modeling
Restrictions for DAX formulas
Restrictions for MDX formulas
Tuning query limit
Choosing between DirectQuery and VertiPaq
Summary
Chapter 10. Security
User authentication
Connecting to Analysis Services from outside a domain
Kerberos and the double-hop problem
Roles
Creating database roles
Membership of multiple roles
Administrative security