Tabular Models Overview

Published:

Applies to: SQL Server 2017, SQL Server 2019, SQL Server 2022, Azure Analysis Services, Power BI Premium

Tabular models in SQL Server Analysis Services (SSAS) and Azure Analysis Services provide a flexible, in-memory semantic modeling experience that enables business intelligence professionals to develop and deploy rich analytical solutions. Unlike multidimensional models, tabular models use a relational modeling paradigm that is familiar to users of Microsoft Office, particularly Excel.

Key Concepts

Data Models

A tabular model is comprised of tables, columns, and relationships, similar to a relational database. You define calculations and business logic using Data Analysis Expressions (DAX) formulas, which are also used in Power BI and Excel Power Pivot.

In-Memory Technology

Tabular models primarily use a highly compressed, in-memory column store technology (VertiPaq engine) for fast query performance. Data can also be persisted to disk and loaded into memory on demand.

DAX (Data Analysis Expressions)

DAX is a formula expression language used to define custom calculations, such as calculated columns and measures, within tabular models. DAX provides a rich set of functions for performing complex data analysis.

Relationships

Relationships link tables together, enabling users to traverse between different data sets and perform integrated analysis. These relationships are typically defined between foreign keys and primary keys, mimicking relational database design.

Roles and Security

Tabular models support role-based security, allowing you to define access permissions for users and groups. This ensures that users can only see the data they are authorized to access.

Advantages of Tabular Models

  • Familiarity: The relational model and DAX are familiar to many business users, lowering the learning curve.
  • Performance: The in-memory engine provides excellent query response times.
  • Agility: Development and deployment cycles are often faster compared to multidimensional models.
  • Integration: Seamless integration with Power BI, Excel, and other Microsoft BI tools.

When to Use Tabular Models

Tabular models are ideal for:

  • Interactive reporting and ad-hoc analysis.
  • Self-service BI scenarios where users need to build their own models.
  • Scenarios requiring high query performance and low latency.
  • Organizations that have standardized on Excel or Power BI for analytics.
Learn More:

For a deep dive into DAX, explore the DAX Reference Documentation.

To understand how to build a tabular model, visit the Create a New Tabular Model Project tutorial.

Common Scenarios

Tabular models are widely used for:

  • Sales performance analysis
  • Financial reporting
  • Customer analytics
  • Supply chain optimization

By leveraging the power of tabular models, you can unlock deeper insights from your data and empower your organization to make better, data-driven decisions.