Contents
Conventions and features in this book
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 future of Analysis Services
Choosing the right model for your project
Upgrading from previous versions of Analysis Services
Compatibility with Power Pivot
Query performance characteristics
Processing performance characteristics
Choosing the query language for Tabular
Introduction to Tabular calculation engines
Tabular model compatibility level (1200 vs. 110x)
Analysis Services and Power BI
Chapter 2. Getting started with the tabular model
Setting up a development environment
Components of a development environment
Working with SQL Server Data Tools
Importing a Deployed Project from Analysis Services
Building a simple tabular model
Navigating in Tabular Model Explorer
Querying tabular models with Excel
Sorting and filtering rows and columns
Querying tabular models with Power BI Desktop
Creating a connection to a tabular model
Building a basic Power BI report
Working with SQL Server Management Studio
Importing from Power BI Desktop
Using DAX Studio as an alternative to SSMS
Chapter 3. Loading data inside Tabular
Understanding server-side and client-side credentials
Loading from Analysis Services
Loading from a tabular database
Loading from a Reporting Services report
Loading reports by using the report data source
Loading reports by using data feeds
Choosing the right data-loading method
Chapter 4. Introducing calculations in DAX
Introduction to the DAX language
Column reference and measures reference
DAX Formatter, DAX Studio, and DAX Editor
Chapter 5. Building hierarchies
Hierarchy design best practices
Hierarchies spanning multiple tables
Natural and unnatural hierarchies
What are parent-child hierarchies?
Configuring parent-child hierarchies
Chapter 6. Data modeling in Tabular
Understanding different data-modeling techniques
Working with dimensional models
Working with slowly changing dimensions
Working with degenerate dimensions
Using views to decouple from the database
Filter propagation in relationships
Implementing relationships in DAX
Normalization versus denormalization
Calculated tables versus an external ETL
Circular reference using calculated tables
Chapter 7. Tabular Model Scripting Language (TMSL)
Data-refresh and database-management operations in TMSL
Chapter 8. The tabular presentation layer
Setting metadata for a Date table
Naming, sorting, and formatting
Organizing measures and columns
Writing translated names in a translation file
Choosing an editor for translation files
Testing translations using a client tool
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
Setting DirectQuery in a development environment
Setting DirectQuery after deployment
Limitations in tabular models for DirectQuery
Restrictions for data modeling
Choosing between DirectQuery and VertiPaq
Connecting to Analysis Services from outside a domain
Kerberos and the double-hop problem
Granting permission through the server administrator role
Granting database roles and administrative permissions
Advanced row-filter expressions
Security in calculated columns and calculated tables
Evaluating the impact of data security on performance
DAX functions for dynamic security
Implementing dynamic security by using CUSTOMDATA
Implementing dynamic security by using USERNAME
Security and impersonation with DirectQuery
Row-level security on SQL Server earlier than 2016
Chapter 11. Processing and partitioning tabular models
Automating deployment to a production server
Defining a partitioning strategy
Defining partitions for a table in a tabular model
Managing partitions for a table
Defining a processing strategy
Using SQL Server Integration Services
Using Analysis Management Objects (AMO) and Tabular Object Model (TOM)
Understanding VertiPaq structures
Value encoding versus hash encoding
Reading VertiPaq internal metadata
Using DMVs for VertiPaq memory usage
Interpreting VertiPaq Analyzer reports
Understanding processing options
What happens during processing
Chapter 13. Interfacing with Tabular
Introducing the AMO and TOM libraries
Creating a database programmatically
Automating data refresh and partitioning
Copying the same database on different servers
Deploying a model.bim file by choosing a database and server name
Chapter 14. Monitoring and tuning a Tabular service
Finding the Analysis Services process
Resources consumed by Analysis Services
Understanding memory configuration
Using memory-related performance counters
Using dynamic management views
Interesting DMVs to monitor a Tabular service
Automating monitoring info and logs acquisition
Monitoring data refresh (process)
Chapter 15. Optimizing tabular models
Reducing a database size by choosing the sort order
Improving encoding and bit sizing
Designing tabular models for large databases
Optimizing compression by splitting columns
Optimizing the process time of large tables
Aggregating fact tables at different granularities
Designing tabular models for near–real-time solutions
Choosing between DirectQuery and VertiPaq
Chapter 16. Choosing hardware and virtualization
Hardware requirements for DirectQuery
Optimizing hardware configuration
Splitting NUMA nodes on different VMs
Scalability of an SSAS Tabular solution
Scalability for a single database (large size)