SQL Server Analysis Services

Tabular Models

On This Page

Introduction to Tabular Models

Tabular models in SQL Server Analysis Services (SSAS) provide a powerful and flexible in-memory data modeling experience. They are designed to deliver business intelligence solutions that are easy to understand, develop, and deploy. Tabular models use a relational database engine and a columnar data store, allowing for high performance and rapid query responses.

Unlike multidimensional models, tabular models represent data as tables and relationships, much like a relational database. This familiar structure makes them accessible to a wider range of users, including business analysts and developers accustomed to relational concepts.

Key Benefits of Tabular Models

  • Ease of Use: The relational metaphor is intuitive for many users.
  • Performance: The in-memory columnar storage engine (VertiPaq) offers exceptional query performance.
  • Integration: Seamless integration with other Microsoft products like Power BI, Excel, and Azure services.
  • DAX: Utilizes Data Analysis Expressions (DAX), a powerful formula language for creating complex calculations and measures.
  • Scalability: Can handle large datasets and complex analytical requirements.

Example Scenario: A retail company wants to analyze sales performance across different regions, product categories, and time periods. A tabular model can quickly aggregate sales data, calculate profit margins, and allow users to slice and dice the data with interactive reports.

Tabular Model Architecture

At its core, a tabular model consists of tables, columns, and relationships. Data is typically imported from various data sources, such as SQL Server databases, Azure SQL Database, flat files, and others. The data is then stored in the tabular model's memory.

Key components include:

  • Tables: Represent entities or facts in your data model.
  • Columns: Attributes of your tables.
  • Relationships: Define how tables are connected, similar to foreign key relationships in relational databases.
  • Measures: Calculations defined using DAX that aggregate data, like SUM, AVERAGE, or more complex business logic.
  • Calculated Columns: Columns created within a table based on DAX expressions.

The engine responsible for processing and querying tabular models is the VertiPaq engine, known for its efficiency in compression and query execution.

Tabular Model Development Workflow

Developing a tabular model typically involves the following steps:

  1. Connect to Data Sources: Establish connections to your source data.
  2. Import Data: Select tables and columns to import into the model.
  3. Define Relationships: Create relationships between imported tables.
  4. Create Measures: Write DAX formulas to define key performance indicators (KPIs) and business metrics.
  5. Add Calculated Columns: Enhance tables with derived attributes.
  6. Implement Security: Define roles and row-level security.
  7. Deploy: Deploy the model to an SSAS tabular instance or Azure Analysis Services.
  8. Consume: Connect to the deployed model from tools like Power BI, Excel, or custom applications.

Development is commonly done using Visual Studio with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS).

-- Example DAX for a simple sales measure
TotalSales = SUM(Sales[Amount])

Best Practices for Tabular Models

  • Import only necessary data: Reduce memory footprint and improve performance.
  • Choose appropriate data types: Optimize storage and query speed.
  • Use meaningful names: For tables, columns, and measures.
  • Design effective relationships: Ensure correct data integrity and query logic.
  • Optimize DAX: Write efficient DAX formulas for measures and calculated columns.
  • Leverage calculated tables: For creating dimension tables or intermediate calculations.
  • Implement row-level security: To restrict data access for specific users.

Further Reading