Designing Tables for Analysis Services

This document provides guidance on designing tables effectively when working with SQL Server Analysis Services (SSAS). Proper table design is fundamental to building performant and scalable analytical models.

Key Principles for Table Design

When designing tables for SSAS, consider the following principles:

• Normalization: While SSAS models often denormalize data for performance, starting with a well-normalized source schema (e.g., 3NF) is recommended. This helps ensure data integrity and reduces redundancy in the source system.
• Data Types: Use appropriate and consistent data types. Avoid using generic types like VARCHAR(MAX) or NVARCHAR(MAX) unless absolutely necessary. Prefer specific lengths and character types. For numeric data, choose the smallest suitable type (e.g., INT instead of BIGINT if the range allows).
• Keys: Ensure that tables have primary keys. In dimensional modeling, fact tables are typically linked to dimension tables via foreign keys. For tabular models, define unique keys for each table.
• Column Naming Conventions: Adopt a clear and consistent naming convention for your columns. Avoid spaces, special characters, and reserved keywords.
• Data Granularity: Understand the grain of your fact tables. This dictates the lowest level of detail that can be analyzed. Ensure that dimension tables can provide attributes relevant to this grain.
• Surrogate Keys: Whenever possible, use surrogate keys (auto-generated integers) for dimension table primary keys. These are generally more performant and stable than natural business keys.

Table Types in Dimensional Modeling

In a typical dimensional model, you will encounter two primary types of tables:

Fact Tables

Fact tables store the quantitative measurements or metrics that represent business processes. They contain foreign keys to dimension tables and the actual measures.

• Measures: These are numeric values that can be aggregated (e.g., Sales Amount, Quantity Sold).
• Foreign Keys: These columns link fact rows to corresponding dimension rows.
• Granularity: The level of detail in a fact table (e.g., individual sales transaction, daily summary).

Dimension Tables

Dimension tables store descriptive attributes that provide context to the facts. They are used for filtering, grouping, and labeling data.

• Primary Key: Uniquely identifies each row in the dimension table. Surrogate keys are highly recommended.
• Descriptive Attributes: Columns that describe the dimension member (e.g., Product Name, Customer City, Date).
• Hierarchies: Dimension tables often contain attributes that form hierarchies (e.g., Day -> Month -> Quarter -> Year).

Considerations for Tabular Models

In tabular models, you typically import tables directly from source systems or create them using DAX. While the principles of data integrity and appropriate data types still apply, the focus shifts:

• Importing from Relational Sources: If importing from a relational database, the structure of the source tables is often preserved. You can rename columns and change data types within the model.
• Calculated Columns and Measures: Tabular models heavily rely on DAX for creating calculated columns and measures. Design your base tables to efficiently support these calculations.
• Relationships: Define clear relationships between tables in the model. These relationships determine how tables are filtered by each other.
• Partitions: For large tables, consider using partitions to manage data more effectively, enabling faster data loading and query performance.

Best Practices Summary

• Start with clean, well-structured source data.
• Use appropriate data types and lengths.
• Implement clear naming conventions.
• Design for the expected query patterns and analytical needs.
• Leverage surrogate keys for dimensions.
• Understand and define the granularity of your fact data.
• For tabular models, design tables to support DAX calculations and relationships effectively.

Related Topics

• Designing Relationships
• Designing Partitions
• Creating Tabular Models