Tabular Modeling in Microsoft SQL Server Analysis Services, Second Edition

Table of contents

1. Title Page
2. Copyright Page
3. Contents at a Glance
4. Contents
5. Foreword
6. Introduction
   1. Who should read this book
   2. Who should not read this book
   3. Organization of this book
   4. Conventions and features in this book
   5. System requirements
   6. Code samples
   7. Acknowledgments
   8. Errata and book support
   9. We want to hear from you
   10. Stay in touch
7. Chapter 1. Introducing the tabular model
   1. Semantic models in Analysis Services
      1. What is Analysis Services and why should I use it?
      2. A short history of Analysis Services
   2. Understanding Tabular and Multidimensional
      1. The tabular model
      2. The multidimensional model
      3. Why have two models?
      4. The future of Analysis Services
      5. Azure Analysis Services
   3. Choosing the right model for your project
      1. Licensing
      2. Upgrading from previous versions of Analysis Services
      3. Ease of use
      4. Compatibility with Power Pivot
      5. Compatibility with Power BI
      6. Query performance characteristics
      7. Processing performance characteristics
      8. Hardware considerations
      9. Real-time BI
      10. Client tools
      11. Feature comparison
   4. Understanding DAX and MDX
      1. The DAX language
      2. The MDX language
      3. Choosing the query language for Tabular
   5. Introduction to Tabular calculation engines
      1. Introduction to VertiPaq
      2. Introduction to DirectQuery
   6. Tabular model compatibility level (1200 vs. 110x)
   7. Analysis Services and Power BI
   8. Summary
8. Chapter 2. Getting started with the tabular model
   1. Setting up a development environment
      1. Components of a development environment
      2. Licensing
      3. Installation process
   2. Working with SQL Server Data Tools
      1. Creating a new project
      2. Configuring a new project
      3. Importing from Power Pivot
      4. Importing from Power BI
      5. Importing a Deployed Project from Analysis Services
      6. Contents of a tabular project
   3. Building a simple tabular model
      1. Loading data into tables
      2. Working in the diagram view
      3. Navigating in Tabular Model Explorer
   4. Deploying a tabular model
   5. Querying tabular models with Excel
      1. Connecting to a tabular model
      2. Using PivotTables
      3. Using slicers
      4. Sorting and filtering rows and columns
      5. Using Excel cube formulas
   6. Querying tabular models with Power BI Desktop
      1. Creating a connection to a tabular model
      2. Building a basic Power BI report
      3. Adding charts and slicers
      4. Interacting with a report
   7. Working with SQL Server Management Studio
      1. Importing from Power Pivot
      2. Importing from Power BI Desktop
      3. Using DAX Studio as an alternative to SSMS
   8. Summary
9. Chapter 3. Loading data inside Tabular
   1. Understanding data sources
   2. Understanding impersonation
   3. Understanding server-side and client-side credentials
   4. Working with big tables
   5. Loading from SQL Server
      1. Loading from a list of tables
      2. Loading from a SQL query
      3. Loading from views
   6. Opening existing connections
   7. Loading from Access
   8. Loading from Analysis Services
      1. Using the MDX editor
      2. Loading from a tabular database
   9. Loading from an Excel file
   10. Loading from a text file
       1. Handling more complex CSV files
   11. Loading from the clipboard
   12. Loading from a Reporting Services report
       1. Loading reports by using the report data source
       2. Loading reports by using data feeds
   13. Loading from a data feed
   14. Loading from SharePoint
   15. Choosing the right data-loading method
   16. Summary
10. Chapter 4. Introducing calculations in DAX
    1. Introduction to the DAX language
       1. DAX syntax
       2. DAX data types
       3. DAX operators
       4. Column reference and measures reference
       5. Aggregate functions
       6. Table functions
       7. Evaluation context
       8. CALCULATE and CALCULATETABLE
       9. Variables
    2. Measures
    3. Calculated columns
    4. Calculated tables
    5. Writing queries in DAX
    6. Formatting DAX code
       1. DAX Formatter, DAX Studio, and DAX Editor
    7. Summary
11. Chapter 5. Building hierarchies
    1. Basic hierarchies
       1. What are hierarchies?
       2. When to build hierarchies
       3. Building hierarchies
       4. Hierarchy design best practices
       5. Hierarchies spanning multiple tables
       6. Natural and unnatural hierarchies
    2. Parent-child hierarchies
       1. What are parent-child hierarchies?
       2. Configuring parent-child hierarchies
       3. Unary operators
    3. Summary
12. Chapter 6. Data modeling in Tabular
    1. Understanding different data-modeling techniques
       1. Using the OLTP database
    2. Working with dimensional models
       1. Working with slowly changing dimensions
       2. Working with degenerate dimensions
       3. Using snapshot fact tables
    3. Using views to decouple from the database
    4. Relationship types
       1. Cardinality of relationships
       2. Filter propagation in relationships
       3. Active state of relationships
       4. Implementing relationships in DAX
    5. Normalization versus denormalization
    6. Calculated tables versus an external ETL
       1. Circular reference using calculated tables
    7. Summary
13. Chapter 7. Tabular Model Scripting Language (TMSL)
    1. Defining objects in TMSL
       1. The Model object
       2. The DataSource object
       3. The Table object
       4. The Relationship object
       5. The Perspective object
       6. The Culture object
       7. The Role object
    2. TMSL commands
       1. Object operations in TMSL
       2. Data-refresh and database-management operations in TMSL
       3. Scripting in TMSL
    3. Summary
14. Chapter 8. The tabular presentation layer
    1. Setting metadata for a Date table
    2. Naming, sorting, and formatting
       1. Naming objects
       2. Hiding columns and measures
       3. Organizing measures and columns
       4. Sorting column data
       5. Formatting
    3. Perspectives
    4. Power View–related properties
       1. Default field set
       2. Table behavior properties
    5. Key performance indicators
    6. Translations
       1. Creating a translation file
       2. Writing translated names in a translation file
       3. Choosing an editor for translation files
       4. Importing a translation file
       5. Testing translations using a client tool
       6. Removing a translation
       7. Best practices using translations
    7. Selecting culture and collation in a tabular model
       1. Changing culture and collation using an integrated workspace
       2. Changing culture and collation using a workspace server
    8. Summary
15. Chapter 9. Using DirectQuery
    1. Configuring DirectQuery
       1. Setting DirectQuery in a development environment
       2. Setting DirectQuery after deployment
    2. Limitations in tabular models for DirectQuery
       1. Supported data sources
       2. Restrictions for data sources
       3. Restrictions for data modeling
       4. Restrictions for DAX formulas
       5. Restrictions for MDX formulas
       6. Tuning query limit
    3. Choosing between DirectQuery and VertiPaq
    4. Summary
16. Chapter 10. Security
    1. User authentication
       1. Connecting to Analysis Services from outside a domain
       2. Kerberos and the double-hop problem
    2. Roles
       1. Creating database roles
       2. Membership of multiple roles
    3. Administrative security
       1. Granting permission through the server administrator role
       2. Granting database roles and administrative permissions
    4. Data security
       1. Basic data security
       2. Testing security roles
       3. Advanced row-filter expressions
       4. Security in calculated columns and calculated tables
       5. Using a permissions table
    5. Evaluating the impact of data security on performance
    6. Creating dynamic security
       1. DAX functions for dynamic security
       2. Implementing dynamic security by using CUSTOMDATA
       3. Implementing dynamic security by using USERNAME
    7. Security in DirectQuery
       1. Security and impersonation with DirectQuery
       2. Row-level security on SQL Server earlier than 2016
    8. Monitoring security
    9. Summary
17. Chapter 11. Processing and partitioning tabular models
    1. Automating deployment to a production server
    2. Table partitioning
       1. Defining a partitioning strategy
       2. Defining partitions for a table in a tabular model
       3. Managing partitions for a table
    3. Processing options
       1. Available processing options
       2. Defining a processing strategy
       3. Executing processing
    4. Processing automation
       1. Using TMSL commands
       2. Using SQL Server Integration Services
       3. Using Analysis Management Objects (AMO) and Tabular Object Model (TOM)
       4. Using PowerShell
    5. Sample processing scripts
       1. Processing a database
       2. Processing tables
       3. Processing partitions
       4. Rolling partitions
    6. Summary
18. Chapter 12. Inside VertiPaq
    1. Understanding VertiPaq structures
       1. Understanding column storage
       2. Value encoding versus hash encoding
       3. Run-length encoding
       4. Controlling column encoding
       5. Hierarchies and relationships
       6. Segmentation and partitioning
    2. Reading VertiPaq internal metadata
       1. Using DMVs for VertiPaq memory usage
       2. Interpreting VertiPaq Analyzer reports
    3. Memory usage in VertiPaq
       1. Data memory usage
       2. Processing memory usage
       3. Querying memory usage
    4. Understanding processing options
       1. What happens during processing
       2. Available processing options
    5. Summary
19. Chapter 13. Interfacing with Tabular
    1. Introducing the AMO and TOM libraries
       1. Introducing AMOs
       2. Introducing the TOM
    2. Introducing the TMSL commands
    3. Creating a database programmatically
    4. Automating data refresh and partitioning
    5. Analyzing metadata
    6. Manipulating a data model
    7. Automating project deployment
       1. Copying the same database on different servers
       2. Deploying a model.bim file by choosing a database and server name
    8. Summary
20. Chapter 14. Monitoring and tuning a Tabular service
    1. Finding the Analysis Services process
    2. Resources consumed by Analysis Services
       1. CPU
       2. Memory
       3. I/O operations
    3. Understanding memory configuration
    4. Using memory-related performance counters
    5. Using dynamic management views
       1. Interesting DMVs to monitor a Tabular service
    6. Automating monitoring info and logs acquisition
       1. Performance counters
       2. SQL Server Profiler
       3. ASTrace
       4. Flight Recorder
       5. Extended Events
       6. Other commercial tools
    7. Monitoring data refresh (process)
    8. Monitoring queries
    9. Summary
21. Chapter 15. Optimizing tabular models
    1. Optimizing data memory usage
       1. Removing unused columns
       2. Reducing dictionary size
       3. Choosing a data type
       4. Reducing a database size by choosing the sort order
       5. Improving encoding and bit sizing
       6. Optimizing large dimensions
    2. Designing tabular models for large databases
       1. Optimizing compression by splitting columns
       2. Optimizing the process time of large tables
       3. Aggregating fact tables at different granularities
    3. Designing tabular models for near–real-time solutions
       1. Choosing between DirectQuery and VertiPaq
       2. Using partitions
       3. Reducing recalculation time
       4. Managing lock during process
    4. Summary
22. Chapter 16. Choosing hardware and virtualization
    1. Hardware sizing
       1. CPU clock and model
       2. Memory speed and size
       3. NUMA architecture
       4. Disk and I/O
       5. Hardware requirements for DirectQuery
    2. Optimizing hardware configuration
       1. Power settings
       2. Hyper-threading
       3. NUMA settings
    3. Virtualization
       1. Splitting NUMA nodes on different VMs
       2. Committing memory to VMs
    4. Scalability of an SSAS Tabular solution
       1. Scalability for a single database (large size)
       2. Scalability for large user workload
    5. Summary
23. Index
24. Code Snippets