Home Page Icon
Home Page
Table of Contents for
Images
Close
Images
by Alberto Ferrari, Marco Russo
Tabular Modeling in Microsoft SQL Server Analysis Services, Second Edition
Title Page
Copyright Page
Contents at a Glance
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
Handling more complex CSV files
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
Granting permission through the server administrator role
Granting database roles and administrative permissions
Data security
Basic data security
Testing security roles
Advanced row-filter expressions
Security in calculated columns and calculated tables
Using a permissions table
Evaluating the impact of data security on performance
Creating dynamic security
DAX functions for dynamic security
Implementing dynamic security by using CUSTOMDATA
Implementing dynamic security by using USERNAME
Security in DirectQuery
Security and impersonation with DirectQuery
Row-level security on SQL Server earlier than 2016
Monitoring security
Summary
Chapter 11. Processing and partitioning tabular models
Automating deployment to a production server
Table partitioning
Defining a partitioning strategy
Defining partitions for a table in a tabular model
Managing partitions for a table
Processing options
Available processing options
Defining a processing strategy
Executing processing
Processing automation
Using TMSL commands
Using SQL Server Integration Services
Using Analysis Management Objects (AMO) and Tabular Object Model (TOM)
Using PowerShell
Sample processing scripts
Processing a database
Processing tables
Processing partitions
Rolling partitions
Summary
Chapter 12. Inside VertiPaq
Understanding VertiPaq structures
Understanding column storage
Value encoding versus hash encoding
Run-length encoding
Controlling column encoding
Hierarchies and relationships
Segmentation and partitioning
Reading VertiPaq internal metadata
Using DMVs for VertiPaq memory usage
Interpreting VertiPaq Analyzer reports
Memory usage in VertiPaq
Data memory usage
Processing memory usage
Querying memory usage
Understanding processing options
What happens during processing
Available processing options
Summary
Chapter 13. Interfacing with Tabular
Introducing the AMO and TOM libraries
Introducing AMOs
Introducing the TOM
Introducing the TMSL commands
Creating a database programmatically
Automating data refresh and partitioning
Analyzing metadata
Manipulating a data model
Automating project deployment
Copying the same database on different servers
Deploying a model.bim file by choosing a database and server name
Summary
Chapter 14. Monitoring and tuning a Tabular service
Finding the Analysis Services process
Resources consumed by Analysis Services
CPU
Memory
I/O operations
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
Performance counters
SQL Server Profiler
ASTrace
Flight Recorder
Extended Events
Other commercial tools
Monitoring data refresh (process)
Monitoring queries
Summary
Chapter 15. Optimizing tabular models
Optimizing data memory usage
Removing unused columns
Reducing dictionary size
Choosing a data type
Reducing a database size by choosing the sort order
Improving encoding and bit sizing
Optimizing large dimensions
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
Using partitions
Reducing recalculation time
Managing lock during process
Summary
Chapter 16. Choosing hardware and virtualization
Hardware sizing
CPU clock and model
Memory speed and size
NUMA architecture
Disk and I/O
Hardware requirements for DirectQuery
Optimizing hardware configuration
Power settings
Hyper-threading
NUMA settings
Virtualization
Splitting NUMA nodes on different VMs
Committing memory to VMs
Scalability of an SSAS Tabular solution
Scalability for a single database (large size)
Scalability for large user workload
Summary
Index
Code Snippets
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Images
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset