MSDN Documentation

Designing Databases for SQL Server Analysis Services Multidimensional Models

This document provides comprehensive guidance on designing databases that serve as the foundation for your SQL Server Analysis Services (SSAS) multidimensional models. A well-designed database schema is crucial for performance, scalability, and ease of use of your analytical solutions.

Key Considerations for Database Design

When designing a relational database intended for use with SSAS multidimensional models, several factors should be taken into account:

• Fact Tables: These tables store the quantitative measures of a business process. They should be designed for efficiency in joins and aggregations.
  • Typically contain foreign keys to dimension tables.
  • Should be denormalized to some extent to improve query performance by reducing the number of joins required.
  • Consider using surrogate keys for all foreign keys referencing dimension tables.
• Dimension Tables: These tables contain descriptive attributes that provide context to the measures in the fact tables.
  • Should be normalized as much as possible to avoid redundancy.
  • Include a unique primary key (surrogate key is recommended).
  • Attributes should be descriptive and support the analytical needs.
• Star Schema vs. Snowflake Schema: Understand the trade-offs between these two common schema designs.
  • Star Schema: A central fact table surrounded by a few denormalized dimension tables. Simpler, often faster for queries.
  • Snowflake Schema: More normalized dimension tables, leading to more tables and relationships. Can reduce data redundancy but might increase query complexity.
• Data Types: Use appropriate data types for columns to optimize storage and performance. Avoid overly generic types like VARCHAR(MAX) where a more specific type can be used.
• Indexing: Proper indexing on fact table foreign keys and dimension table primary keys is essential for efficient data retrieval.
• Partitioning: For very large fact tables, consider database partitioning to improve manageability and query performance.

Best Practices and Recommendations

Adhering to these best practices will ensure a robust and performant SSAS multidimensional solution:

1. Use Surrogate Keys: Always use surrogate keys (system-generated, unique integer identifiers) as primary keys in dimension tables and as foreign keys in fact tables. This decouples the SSAS model from the operational system's keys and handles changes in natural keys gracefully.
2. Denormalize Dimension Attributes (Slightly): While dimensions are generally normalized, consider denormalizing attributes that are frequently queried together to avoid additional joins within the dimension itself.
3. Granularity of Fact Tables: Define the lowest level of detail for your fact tables. This granularity dictates what kind of analysis can be performed.
4. Pre-Aggregation: While SSAS handles aggregations, consider if some high-level aggregations can be pre-calculated in the relational source for performance gains, especially for complex calculations.
5. Data Cleansing and Validation: Ensure data quality in the relational source. SSAS models inherit the quality of the underlying data.
6. Naming Conventions: Adopt clear and consistent naming conventions for tables and columns to make the database schema understandable.

Example Schema Design (Star Schema)

Consider a simple sales scenario:

Dimension Tables:

• DimProduct (ProductID [PK], ProductName, Category, Subcategory)
• DimDate (DateKey [PK], FullDate, DayOfWeek, Month, Year, Quarter)
• DimCustomer (CustomerKey [PK], CustomerName, City, State, Country)

Fact Table:

• FactSales (DateKey [FK], ProductID [FK], CustomerKey [FK], Quantity, SalesAmount, UnitPrice)

In this star schema:

• FactSales is the central fact table.
• DimProduct, DimDate, and DimCustomer are the dimension tables.
• Foreign keys in FactSales link to the primary keys of the dimension tables.

Further Reading

• Creating Dimensions in SSAS
• Creating Measures in SSAS