Microsoft Learn

SQL Server Analysis Services: Modeling Basics

Welcome to the foundational concepts of modeling data with SQL Server Analysis Services (SSAS). This section introduces the core principles and components necessary for building effective business intelligence solutions.

Understanding Data Models

A data model is a structured representation of data that defines relationships between different data entities. In SSAS, models are designed to facilitate efficient querying and analysis, typically for business intelligence reporting and dashboards. We primarily focus on two types of models: Multidimensional and Tabular.

Key Concepts

• Dimensions: Attributes that describe business facts. Examples include Time, Geography, or Product. Dimensions provide the context for analysis.
• Measures: Numerical values that represent business metrics. Examples include Sales Amount, Quantity Sold, or Profit. Measures are what users typically aggregate and analyze.
• Facts: The central table in a star or snowflake schema, containing the measures and foreign keys linking to dimensions.
• Schemas: The underlying structure of the data source, often a star or snowflake schema, which is leveraged by SSAS to build its models.

Multidimensional Models

Multidimensional models, also known as OLAP cubes, organize data into cubes where dimensions form the axes and measures are the cells. This structure is highly optimized for slicing and dicing data.

A typical Multidimensional model includes:

• Cubes
• Dimensions
• Measure Groups
• Hierarchies

Tabular Models

Tabular models represent data in a relational format, similar to a relational database, but optimized for in-memory analytics using the VertiPaq engine. They are often considered more intuitive for users familiar with relational concepts.

A typical Tabular model includes:

• Tables
• Columns
• Relationships
• Measures (using DAX)
• Hierarchies

DAX (Data Analysis Expressions)

DAX is a formula expression language used in Power BI, Analysis Services, and Power Pivot in Excel. It's used to define calculations, including measures, calculated columns, and row-level security rules, primarily in Tabular models.

Example DAX measure for Total Sales:

Total Sales = SUM(Sales[SalesAmount])

Choosing the Right Model

The choice between Multidimensional and Tabular models depends on several factors, including:

• Existing data source structures.
• Developer and user familiarity with technologies.
• Performance requirements.
• Specific analytical needs.

Both models offer powerful capabilities for building sophisticated business intelligence solutions.

Next Steps

Explore the detailed guides for Tabular Models and Multidimensional Models to learn how to implement these concepts.