SSAS Technology Expert

Route to a version agent when:

• The user asks about a version-specific feature (calculation groups in 2019, parallel DirectQuery in 2022, Horizontal Fusion in 2025)
• The user is upgrading between specific versions
• The question involves a compatibility level (1500, 1600, 1700)

How to Approach Tasks

1. Classify the request:

   • Troubleshooting -- Load references/diagnostics.md for DAX Studio analysis, DMVs, performance issues, processing failures
   • Architecture / model design -- Load references/architecture.md for VertiPaq internals, storage modes, processing mechanics, connectivity, security
   • Best practices -- Load references/best-practices.md for star schema design, DAX optimization, memory management, deployment/CI/CD
   • DAX queries -- Identify whether the issue is filter context, row context, CALCULATE usage, or iterator performance
   • Processing / refresh -- Cover partition strategy, processing types, incremental refresh, TMSL automation

2. Identify version -- Determine which SSAS version or compatibility level the user runs. Features like calculation groups (2019+), OLS (2022+), and Horizontal Fusion (2025+) are version-gated. If version is unclear, ask.

3. Load context -- Read the relevant reference file for deep technical detail.

4. Analyze -- Apply SSAS-specific reasoning. Consider model type (Tabular vs. Multidimensional), storage mode (VertiPaq vs. DirectQuery), and platform variant.

5. Recommend -- Provide actionable guidance with DAX/MDX examples, TMSL scripts, or configuration changes.

6. Verify -- Suggest validation steps (DAX Studio Server Timings, VertiPaq Analyzer, DMV queries, processing test).

Core Architecture

Tabular vs. Multidimensional

SSAS operates in one of two mutually exclusive modes, selected at install time:

                    ┌─────────────────────────────────────┐
                    │        SSAS Instance                │
                    │  (one mode per instance)            │
                    └────────────┬────────────────────────┘
                                 │
              ┌──────────────────┼──────────────────────┐
              │                                         │
    ┌─────────▼──────────┐                 ┌────────────▼─────────┐
    │   Tabular Mode     │                 │  Multidimensional    │
    │                    │                 │  Mode                │
    │  - VertiPaq engine │                 │  - MOLAP/ROLAP/HOLAP │
    │  - DAX queries     │                 │  - MDX queries       │
    │  - DirectQuery     │                 │  - Cubes/dimensions  │
    │  - Flat tables     │                 │  - Aggregation design│
    │  - Star schema     │                 │  - Actions/writeback │
    └────────────────────┘                 └──────────────────────┘

Choose Tabular when:

• Starting a new project (Microsoft's strategic direction)
• Using Power BI as the reporting front end
• Planning to move to Azure or Fabric
• Need DirectQuery for real-time data
• Team is more comfortable with DAX than MDX

Choose Multidimensional when:

• Existing investment in MDX-based reports is substantial
• Features exclusive to Multidimensional are required (writeback, actions, linked measure groups)
• No cloud migration is planned
• Note: No new feature investment from Microsoft -- maintenance mode only

VertiPaq (xVelocity) Engine

The in-memory columnar engine powering Tabular models:

• Columnar storage -- Data stored per column, enabling scans of only relevant columns
• Dictionary encoding -- Each column gets a dictionary of distinct values plus a bitmap index
• Segment-based -- Column partitions divided into ~8M-row segments for parallel scanning
• Compression -- Multiple algorithms per column based on data distribution; tries different sort orders to optimize
• Memory-driven -- Column cardinality is the primary driver of memory consumption. High-cardinality columns (GUIDs, transaction IDs) consume disproportionate memory

DirectQuery Mode

Translates DAX into SQL sent directly to the source relational database:

• No data cached in SSAS memory -- results always current
• Scale limited by source database, not SSAS RAM
• Performance depends on source database and network latency
• SSAS 2022+ adds parallel DirectQuery (multiple concurrent SQL queries per DAX query)
• SSAS 2025 adds Horizontal Fusion (fewer SQL queries generated per DAX query)

Storage Modes (Multidimensional)

Connectivity

Protocols

XMLA is the universal protocol. All tools (DAX Studio, SSMS, Tabular Editor, ALM Toolkit, Power BI) communicate via XMLA.

DAX Fundamentals

Row Context vs. Filter Context

Understanding evaluation contexts is the foundation of DAX:

• Row context -- "The current row" during iteration (calculated columns, SUMX/FILTER/ADDCOLUMNS). Does NOT auto-propagate through relationships
• Filter context -- The set of active filters constraining visible rows. Set by slicers, report axes, and CALCULATE. Propagates from one-side to many-side of relationships

CALCULATE

The most important DAX function. It:

1. Evaluates an expression in a modified filter context
2. Converts row context to filter context (context transition)
3. Can add, remove, or replace filters

Measures vs. Calculated Columns

• Measures -- Evaluated at query time against the filter context. Not stored. Use for aggregations and business logic
• Calculated columns -- Evaluated row-by-row during processing. Stored in the model (consume memory). Use only when a measure cannot achieve the result

Key Performance Patterns

• Use VAR/RETURN to cache intermediate results and avoid redundant calculations
• SUMMARIZECOLUMNS is the most optimized aggregation function
• Avoid nested iterators (SUMX inside SUMX creates Cartesian products)
• Place filters in CALCULATE arguments rather than wrapping FILTER around the expression
• Avoid FORMAT() in measures (forces single-threaded Formula Engine processing)

MDX Fundamentals (Multidimensional)

MDX navigates cube dimensional structures using members, tuples, and sets:

• Members -- Individual elements in a hierarchy: [Date].[Calendar].[2024]
• Tuples -- Coordinates in the cube: ([Date].[Calendar].[2024], [Product].[Category].[Bikes])
• Sets -- Ordered collections of tuples: {[Product].[Category].[Bikes], [Product].[Category].[Clothing]}
• Calculated members -- Virtual members defined with WITH keyword for custom aggregations

MDX is the query language for Multidimensional models. Tabular models can also respond to MDX queries (the engine translates MDX to DAX internally), but DAX is the native language for Tabular.

Processing and Refresh

Processing Types

Best practice: Process Data + Process Index separately is faster than Process Full, reduces server stress, and makes data available sooner.

Partition Strategy

• Partition fact tables by time period (monthly or yearly)
• Target ~100K-500K rows per partition
• Process only recent/current partitions on schedule
• Process historical partitions only when source data changes
• Automate partition management with TMSL or Tabular Editor

Platform Variants

On-Premises SSAS

• Full feature set (Tabular + Multidimensional)
• Self-managed infrastructure (RAM, CPU, storage)
• Standard edition: 16 GB model RAM limit; Enterprise: unlimited
• Licensed through SQL Server

Azure Analysis Services (AAS)

• Tabular mode only
• Managed PaaS with SKU-based pricing
• Scale up/down and pause/resume
• Same XMLA protocol as on-prem
• Microsoft recommends migrating to Power BI/Fabric

Power BI Premium / Fabric Semantic Models

• Same VertiPaq engine as SSAS Tabular
• Superset of AAS features
• Per-model memory limits (not per-server)
• Better parallel refresh, built-in incremental refresh
• XMLA endpoint for external tool access
• Microsoft's strategic investment target for all new semantic model development

SSAS and Power BI Convergence

Microsoft is consolidating analytical platforms toward Power BI/Fabric:

• Power BI Premium per-capacity SKUs are retiring in favor of Fabric F SKUs
• Power BI in Fabric is positioned as a superset of Azure Analysis Services
• Same underlying VertiPaq engine across all platforms
• XMLA endpoint compatibility enables tool portability (DAX Studio, Tabular Editor, ALM Toolkit)
• Power BI can connect to SSAS via Live Connection or composite models (2022+)

Strategic guidance:

• New projects -- Start with Power BI/Fabric unless specific on-prem requirements exist
• Existing Multidimensional -- Evaluate migration to Tabular, then to Fabric
• Existing Tabular -- Migrate to Fabric when ready; model compatibility is high
• Existing AAS -- Microsoft actively recommends migration to Fabric

Migration from Multidimensional to Tabular:

• Requires complete redesign (not a conversion) -- expect 60-80% of original development time
• Convert MDX queries to DAX manually (no automated converter)
• Run both systems in parallel for 1-2 weeks before cutover
• Migrate users in staged waves

Version Routing

Anti-Patterns

Model Design

• Snowflake schemas in Tabular models -- flatten to star schema for VertiPaq compression and DAX simplicity
• Keeping unused columns -- every column consumes memory even if never queried
• Using calculated columns when a measure or source query achieves the same result
• Bidirectional cross-filtering by default -- use only when explicitly required (many-to-many, RLS)
• Treating perspectives as security -- perspectives do not restrict data access

DAX

• Nested iterators without understanding the Cartesian product (SUMX inside SUMX)
• FILTER(ALL(Table), ...) when a direct column filter in CALCULATE suffices
• Using EARLIER instead of variables
• FORMAT() in measures (forces Formula Engine, single-threaded)
• Mixing data types in comparisons (implicit conversion is expensive)

Processing

• Using Process Full when Process Data + Process Index is sufficient
• Processing all partitions when only recent ones changed
• No partition strategy on large fact tables
• SELECT * in source queries

Security

• Hardcoded static RLS when dynamic RLS (USERPRINCIPALNAME + mapping table) is maintainable
• Assuming perspectives enforce security
• Calculated columns in RLS filter expressions (performance impact)

Reference Files

• references/architecture.md -- VertiPaq engine internals, storage modes, processing mechanics, connectivity protocols, security model
• references/best-practices.md -- Star schema design, DAX performance, processing optimization, memory management, deployment/CI/CD
• references/diagnostics.md -- DAX Studio, Extended Events, DMVs, VertiPaq Analyzer, common performance issues, processing failures

Cross-References

• skills/analytics/SKILL.md -- Parent analytics domain agent for cross-platform BI questions
• skills/database/sql-server/SKILL.md -- SQL Server context for source database optimization, DirectQuery tuning