chrishuffman5/ssas

SSAS

This skill covers SQL Server Analysis Services (SSAS) across all supported versions (2019, 2022, 2025):

• Tabular and Multidimensional model architecture
• VertiPaq (xVelocity) in-memory columnar engine
• DAX (Data Analysis Expressions) for Tabular models
• MDX (Multidimensional Expressions) for cubes
• DirectQuery and composite model patterns
• Processing, partitioning, and incremental refresh
• Security (RLS, OLS, roles)
• Deployment, CI/CD, and TMSL scripting
• Platform variants (on-premises, Azure Analysis Services, Power BI/Fabric)
• Diagnostics with DAX Studio, VertiPaq Analyzer, Extended Events, and DMVs

When a question is version-specific, read the matching file under references/versions/. When the version is unknown, provide general guidance and note where behavior differs across versions.

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)

| Mode | Data Location | Aggregations | Best For | |------|--------------|--------------|----------| | MOLAP | SSAS proprietary store | SSAS proprietary store | Query performance priority | | ROLAP | Source database | Source database (indexed views) | Very large dimensions, real-time | | HOLAP | Source database | SSAS proprietary store | Fast summaries, acceptable detail latency |

Connectivity

Protocols

| Protocol/Library | Purpose | |------------------|---------| | XMLA | XML for Analysis -- standard protocol for all SSAS communication (queries, processing, admin) | | ADOMD.NET | Managed .NET client library for querying SSAS | | AMO/TOM | Analysis Management Objects / Tabular Object Model -- .NET library for admin operations | | MSOLAP (OLE DB) | Used by Excel, SSRS, and COM-based clients | | TMSL | Tabular Model Scripting Language (JSON-based) for Tabular models at compatibility level 1200+ |

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

| Type | Effect | When to Use | |------|--------|-------------| | Process Full | Drop + reload + rebuild indexes | Initial load, schema changes | | Process Data | Load data only, no index rebuild | Followed by Process Index | | Process Index | Rebuild indexes/aggregations only | After Process Data | | Process Add | Append new rows only | Incremental loads (no updates) | | Process Update | Reload data + update aggregations | Modified source rows | | Process Clear | Drop all data | Before clean reload |

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-Specific Guidance

| Version | Compat Level | Reference | What's Version-Specific | |---------|-------------|-----------|--------------| | SSAS 2019 | 1500 | references/versions/2019.md | Calculation groups, many-to-many relationships, query interleaving | | SSAS 2022 | 1600 | references/versions/2022.md | Parallel DirectQuery, composite models, OLS, MDX Fusion | | SSAS 2025 | 1700 | references/versions/2025.md | Horizontal Fusion, selection expressions, LINEST/LINESTX, binary XML |

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

Diagnostic Scripts

Ready-made DMV queries in scripts/, numbered by investigation order. Deliver them verbatim (they are read-only); each header explains how to run it and how to interpret output. All also work against Azure AS and Power BI Premium XMLA endpoints.

• scripts/01-active-sessions.sql -- Active sessions with user, duration, CPU, last command
• scripts/02-current-commands.sql -- Commands executing right now (query vs processing, waiting vs computing)
• scripts/03-object-memory.sql -- Objects ranked by memory (find the column eating the instance)
• scripts/04-storage-by-table.sql -- Table/partition sizes and row counts for the current model
• scripts/05-connections.sql -- Client connections (tool audit, connection leaks)
• scripts/06-locks-and-blocking.sql -- Lock grants/waits (processing-commit blocking queries)

Cross-References

• overview skill, this plugin -- cross-platform BI questions
• sql-server skill, database plugin -- SQL Server context for source database optimization, DirectQuery tuning