Tabular Modeling in Microsoft SQL Server Analysis Services, Second Edition

Tabular Modeling in Microsoft SQL Server Analysis Services, Second Edition

by Marco Russo, Alberto Ferrari
Released April 2017
Publisher(s): Microsoft Press
ISBN: 9781509302895

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Build agile and responsive business intelligence solutions

Create a semantic model and analyze data using the tabular model in SQL Server 2016 Analysis Services to create corporate-level business intelligence (BI) solutions. Led by two BI experts, you will learn how to build, deploy, and query a tabular model by following detailed examples and best practices. This hands-on book shows you how to use the tabular model’s in-memory database to perform rapid analytics—whether you are new to Analysis Services or already familiar with its multidimensional model.

Discover how to:

• Determine when a tabular or multidimensional model is right for your project

• Build a tabular model using SQL Server Data Tools in Microsoft Visual Studio 2015

• Integrate data from multiple sources into a single, coherent view of company information

• Choose a data-modeling technique that meets your organization’s performance and usability requirements

• Implement security by establishing administrative and data user roles

• Define and implement partitioning strategies to reduce processing time

• Use Tabular Model Scripting Language (TMSL) to execute and automate administrative tasks

• Optimize your data model to reduce the memory footprint for VertiPaq

• Choose between in-memory (VertiPaq) and pass-through (DirectQuery) engines for tabular models

• Select the proper hardware and virtualization configurations

• Deploy and manipulate tabular models from C# and PowerShell using AMO and TOM libraries

Get code samples, including complete apps, at: https://aka.ms/tabular/downloads

About This Book

• For BI professionals who are new to SQL Server 2016 Analysis Services or already familiar with previous versions of the product, and who want the best reference for creating and maintaining tabular models.

• Assumes basic familiarity with database design and business analytics concepts.

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

Product information

  • Title: Tabular Modeling in Microsoft SQL Server Analysis Services, Second Edition
  • Author(s): Marco Russo, Alberto Ferrari
  • Release date: April 2017
  • Publisher(s): Microsoft Press
  • ISBN: 9781509302895