The Ultimate Guide: Optimizing Data Processing in Azure Analysis Services (AAS)

Hey everyone!

If you work with Azure Analysis Services, SQL Server Analysis Services (SSAS), or Power BI Premium and have found yourself staring at that progress bar for hours waiting for processing to finish, this post is for you. Processing a tabular model of hundreds of gigabytes in production, with tight refresh windows and users waiting for data, is one of the biggest daily challenges for a DBA or BI professional.

In this guide, I'll cover absolutely everything: from what happens under the hood in the VertiPaq engine, through all types of processing (with Process Add in depth), internal vs. external parallelism, data source optimizations (SQL Server, Synapse, Query Folding), advanced partitioning (Hot/Warm/Cold), Scale-Out with read replicas for zero downtime, aggregation tables for models with billions of rows, EncodingHint configuration, professional use of Tabular Editor (BPA) and DAX Studio, automation via TMSL, PowerShell, and ADF, and much more.

Most techniques apply equally to AAS, SSAS 2016+, and Power BI Premium/Fabric.

This is the guide I wish I had found when I started. So, let's dive in!

Introduction to Processing Optimization in Azure Analysis Services

Azure Analysis Services (AAS) is a PaaS (Platform as a Service) based on the same engine as SQL Server Analysis Services (SSAS) Tabular, but running fully managed in the Azure cloud. Internally, it uses the VertiPaq engine, a highly compressed in-memory columnar database optimized for analytical OLAP workloads.

Understanding what happens under the hood is fundamental for any optimization. When you process a tabular model, Analysis Services performs the following steps in order:

1. Data Read: AS connects to your data source (SQL Server, Synapse, Blob Storage, etc.) and executes the queries defined in the partitions/tables.
2. Encoding and Compression: The returned data is encoded by VertiPaq using Value Encoding or Hash Encoding, depending on the data type and column cardinality.
3. Data Dictionary Construction: For each column, AS maintains a dictionary of unique values mapped to integer IDs. This dictionary is at the heart of columnar compression.
4. Index Construction: Two main types of indexes are built: the Forward Index (row → value ID mapping) and the Inverted Index (value → rows containing it mapping, used for filters).
5. Hierarchy and Relationship Recalculation: After the data is loaded, AS rebuilds the relationships and hierarchies between tables.
6. Measure Recalculation (Process Recalc): All calculated measures and calculated columns are recalculated in the context of the new data.

Each of these steps can be a bottleneck depending on the scenario. Therefore, optimizing processing is not a single action, it is a multi-layered strategy.

VertiPaq and RAM usage

VertiPaq is an in-memory database. This means that all model data must fit into the server's RAM during and after processing. In AAS, the server tier determines the available RAM:

• D1: 3 GB
• D2: 5 GB
• D3: 10 GB
• D4: 20 GB
• S0: 25 GB
• S1: 25 GB
• S2: 50 GB
• S4: 100 GB
• S8/S9: 200–400 GB

Processing a model can consume up to 2–3x the model's disk size in RAM, because while new data is loaded, old data remains in memory (to avoid interrupting running queries). This phenomenon is called cold swap or hot swap depending on how you configure processing.

Cold Swap vs. Hot Swap in Processing

This is a fundamental technical detail that directly impacts memory consumption during processing:

• Cold Swap (default): Old data is released from memory before new data is loaded. During processing, queries to the model return an error or outdated data. Consumes less RAM but has unavailability.
• Hot Swap: A new copy of the model is loaded in parallel with the old copy. When the new one is ready, AS performs an atomic swap. Queries continue to function during processing. Requires double the model's RAM.

In AAS, hot swap happens automatically when you use the REST APIs or the XMLA Endpoint for processing. To configure the behavior, you can adjust the CommitMode property in XMLA scripts. For large models, it is common to scale the server tier vertically during the processing window and scale back down afterward; AAS allows this via REST API without downtime.

Understanding Analysis Services Processing Types

Understanding the different processing types is the first step towards an efficient strategy. Each type has a specific cost and purpose. Using the wrong type is one of the most common mistakes that lead to unnecessarily slow processing.

Process Full

Process Full is the most comprehensive and most costly type. It executes, in order: Process Clear → Process Data → Process Index → Process Recalc. Basically, it deletes everything and rebuilds from scratch.

• When to use: After structural changes to the model (adding/removing/renaming columns, changing data types, changing partition queries). Also indicated when data corruption is suspected or after restoring an outdated backup.
• When to avoid: Never use Process Full on fact tables with billions of rows as a daily routine. The impact on the maintenance window can be unmanageable.
• Estimated cost: High, full source read + reconstruction of all indexes + recalculation of all measures.

To execute via TMSL (Tabular Model Scripting Language), use the script below in SQL Server Management Studio (SSMS) connected to your AAS/SSAS server:

{
  "refresh": {
    "type": "full",
    "objects": [
      {
        "database": "MeuModeloAAS",
        "table": "FatoVendas"
      }
    ]
  }
}

Process Clear

Removes all data from an object (model, table, or partition), but keeps the metadata structure intact. The object remains in an unprocessed state after Process Clear.

• When to use: When you need to free up RAM urgently. Or as the first step of a manual staged processing strategy. Also useful for clearing a specific partition before reloading it with corrected data.
• Point of attention: After Process Clear, queries to the object will return an error until it is reprocessed.

{
  "refresh": {
    "type": "clearValues",
    "objects": [
      {
        "database": "MeuModeloAAS",
        "table": "FatoVendas",
        "partition": "FatoVendas_Atual"
      }
    ]
  }
}

Process Data

Loads data from the source to the specified object, but does not rebuild indexes and does not recalculate measures. It is faster than Process Full, but the object remains in a dirty state; the data is present, but the filter indexes and DAX calculations still point to the previous data.

• When to use: As the first step of a two-stage processing flow: you process data from several tables/partitions with Process Data (possibly in parallel) and then trigger a single Process Recalc on the entire model. This prevents Process Recalc from running repeatedly for each table.
• Point of attention: While a table is in a dirty state, queries involving it will return old data. Never use Process Data and leave the model in this state; always complete the cycle with Process Recalc.
• Estimated cost: Medium, reads the entire source of the partition/table and rebuilds the dictionary and segments, but does not redo relationship indexes or calculated columns.

Examples of use via TMSL, processing a specific table, a specific partition, and multiple partitions:

// Exemplo 1: Process Data em uma tabela inteira
{
  "refresh": {
    "type": "dataOnly",
    "objects": [{ "database": "MeuModeloAAS", "table": "DimCliente" }]
  }
}

// Exemplo 2: Process Data em uma partição específica
{
  "refresh": {
    "type": "dataOnly",
    "objects": [{
      "database": "MeuModeloAAS",
      "table": "FatoVendas",
      "partition": "FatoVendas_2024_12"
    }]
  }
}

// Exemplo 3: Process Data em múltiplas partições + Process Recalc ao final
// Tudo num único comando TMSL (o AS gerencia o paralelismo internamente)
{
  "sequence": {
    "maxParallelism": 8,
    "operations": [
      {
        "refresh": {
          "type": "dataOnly",
          "objects": [
            { "database": "MeuModeloAAS", "table": "FatoVendas",   "partition": "FatoVendas_Hot" },
            { "database": "MeuModeloAAS", "table": "FatoEntradas", "partition": "FatoEntradas_Hot" },
            { "database": "MeuModeloAAS", "table": "DimProduto" },
            { "database": "MeuModeloAAS", "table": "DimCliente" }
          ]
        }
      },
      {
        "refresh": {
          "type": "calculate",
          "objects": [{ "database": "MeuModeloAAS" }]
        }
      }
    ]
  }
}

This pattern of sequence + parallel dataOnly + final calculate is one of the most efficient for models with multiple large tables, as Process Recalc runs only once, after all data has been loaded.

Process Add: The Most Powerful (and Most Misunderstood) Type

Process Add is, in my opinion, the most misunderstood processing type in Analysis Services, and for a simple reason: it does not appear in the SSMS UI when you open the "Process Database" dialog. You will see Process Full, Process Default, Process Clear, but Process Add is not there. This is because Process Add only makes sense at the specific partition level, not the entire database. To use it, you either need to open the processing dialog for an individual partition in SSMS, or, more commonly in production, write the TMSL script manually.

How Process Add Works Internally

Process Add executes the partition's source query (exactly as defined in the model's metadata) and appends the results to what already exists in the partition, without deleting previous data. VertiPaq creates new segments with the new data and adds them to the existing partition.

The responsibility for ensuring that the partition query returns only data that has not yet been loaded is entirely yours. AS simply executes what you defined.

Correct Patterns for Using Process Add

There are three main patterns that make Process Add safe and efficient:

Pattern 1, "Delta" partition with staging table:

The partition query points to a staging table that contains only the new rows since the last processing. After Process Add completes, you truncate the staging table (or mark the rows as "processed").

-- Query for partition "FatoVendas_Delta" in the AS model:
SELECT DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda
FROM dbo.FatoVendas_Staging_AS
-- This table is truncated BEFORE each ETL load
-- and populated ONLY with new data for the day

-- After the Process Add completes, in your ETL pipeline you execute:
TRUNCATE TABLE dbo.FatoVendas_Staging_AS;

Pattern 2, Watermark in the partition query:

The query references a control table that stores the cutoff point of the last successful processing. After Process Add, you update this table.

-- Query for partition "FatoVendas_Hot" in the AS model:
SELECT DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda
FROM dbo.FatoVendas
WHERE RowVersion > (
    SELECT ISNULL(MAX(LastRowVersion), 0)
    FROM dbo.AS_ProcessControl
    WHERE TableName = 'FatoVendas' AND PartitionName = 'FatoVendas_Hot'
)

-- In the ETL pipeline, AFTER the Process Add successfully completes:
UPDATE dbo.AS_ProcessControl
SET LastRowVersion = (SELECT MAX(RowVersion) FROM dbo.FatoVendas),
    LastProcessedAt = GETDATE()
WHERE TableName = 'FatoVendas' AND PartitionName = 'FatoVendas_Hot';

Pattern 3, Empty fixed-period partition:

The simplest and safest case: you create a new partition (which does not yet have data) and run Process Add (or Process Full, the result is the same, as the partition is empty). After that, the partition is no longer touched with Process Add, only with Process Full when it is necessary to correct historical data.

// Criar a partição para dezembro/2024 (vazia) via TMSL e processar logo em seguida
// A partição tem query: SELECT * FROM FatoVendas WHERE DataKey >= 20241201 AND DataKey < 20250101
// Como está vazia, Process Add = Process Full nesse caso
{
  "refresh": {
    "type": "add",
    "objects": [{
      "database": "MeuModeloAAS",
      "table": "FatoVendas",
      "partition": "FatoVendas_2024_12"
    }]
  }
}

When NOT to Use Process Add

• Data with UPDATE or DELETE at the source: If a sale from the previous day was canceled (DELETE) or corrected (UPDATE), Process Add will not delete the incorrect row from the partition; it remains there. For mutable rows, always use Process Full on the affected partition.
• Partitions with fixed range query: A partition defined as WHERE Month = 3 AND Year = 2024 already has all data for the period. Running Process Add on it will duplicate everything. Never do this.
• Without a delta control mechanism: If you cannot guarantee that the partition query returns only new data (no watermark table, no staging, no new empty partition), do not use Process Add.
• After many accumulated Process Adds: Each Process Add creates new segments in the partition. After dozens or hundreds of executions, the partition becomes fragmented (many small segments), which harms scan performance. Periodically, perform a Process Full on the partition to consolidate everything into large, well-compressed segments, or use Process Defrag.

Anti-Duplication Checklist for Process Add

Complete TMSL for Process Add on an existing partition:

{
  "refresh": {
    "type": "add",
    "objects": [{
      "database": "MeuModeloAAS",
      "table": "FatoVendas",
      "partition": "FatoVendas_Hot"
    }]
  }
}

After Process Add on the partition, you still need to run a Process Recalc on the database (or table) so that calculated columns, hierarchies, and relationships reflect the new data:

{
  "sequence": {
    "operations": [
      {
        "refresh": {
          "type": "add",
          "objects": [{ "database": "MeuModeloAAS", "table": "FatoVendas", "partition": "FatoVendas_Hot" }]
        }
      },
      {
        "refresh": {
          "type": "calculate",
          "objects": [{ "database": "MeuModeloAAS" }]
        }
      }
    ]
  }
}

Defragmentation after Repeated Process Add

Each Process Add execution adds a new set of segments to the partition. A partition that has received 365 daily Process Adds over a year can have hundreds of segments of varying sizes. To check for fragmentation and correct it:

-- Check how many segments each partition has
SELECT
    DIMENSION_NAME  AS Tabela,
    PARTITION_NAME  AS Particao,
    COUNT(*)        AS TotalSegmentos,
    SUM(RECORDS_COUNT) AS TotalLinhas,
    AVG(RECORDS_COUNT) AS MediaLinhasPorSegmento
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
WHERE ATTRIBUTE_NAME = 'RowNumber'  -- RowNumber = 1 segment per data block
GROUP BY DIMENSION_NAME, PARTITION_NAME
HAVING COUNT(*) > 5
ORDER BY TotalSegmentos DESC;

If a partition has dozens or hundreds of segments with very few rows each, perform a Process Full on it to consolidate. Process Full on the partition does not affect other partitions of the same table; data from other partitions remains intact for queries during reprocessing (thanks to Hot Swap).

Process Recalc

Recalculates all calculated columns, hierarchies, relationships, and DAX measures in the model. Does not read data from the source. It is executed automatically at the end of a Process Full, but can be separated for advanced scenarios.

• When to use: At the end of a parallel processing flow where you processed several tables with Process Data independently and want to trigger the recalculation only once at the end, avoiding recalculating multiple times during the process.
• Estimated cost: Medium to high, depending on the complexity of DAX measures and the number of calculated columns. In models with many calculated columns with complex formulas, Process Recalc can be the most time-consuming phase.

{
  "refresh": {
    "type": "calculate",
    "objects": [
      {
        "database": "MeuModeloAAS"
      }
    ]
  }
}

Process Defrag

Internally reorganizes data segments on disk to improve read performance in subsequent queries. Analogous to SQL Server's REORGANIZE INDEX.

• When to use: Periodically in models with many incremental Process Add operations. Over time, VertiPaq can create many small and fragmented segments, which degrades scan performance.
• Estimated cost: Low to medium, does not read from the source, only reorganizes data in memory/disk.

Process Index

Rebuilds indexes (forward index and inverted index) for an object without reloading data from the source. Generally executed after a Process Data.

• When to use: In manual flows where Process Data and Process Index are separated for granular control. Less common in modern automations where the TMSL refresh handles everything.

Summary: Which type to use in each scenario?

To help with decision-making, see the quick reference table:

• Structural change in the model (new column, data type): Process Full on the affected table.
• Daily append-only update with staging/watermark: Process Add on the "hot" partition + Process Recalc on the model.
• Daily update of a large table (with possible updates/deletes): Process Full on the "hot" partition + Process Recalc on the model.
• Reprocessing of corrected data in a historical period: Process Full only on the affected historical partition + Process Recalc on the model.
• New partition being loaded for the first time: Process Full or Process Add (equivalent, the partition is empty).
• Multiple tables/partitions with maximum parallelism: Process Data on all + a single Process Recalc at the end (in a single TMSL command).
• Partition with many accumulated and fragmented Process Add operations: Process Full on the partition to consolidate segments.
• Model with accumulated fragmentation without the need for reload: Scheduled weekly Process Defrag.

Parallelism in Processing: AS Handles It vs. You "Help" (and Make it Worse)

Parallelism is the most misunderstood topic when it comes to optimizing Analysis Services processing. Intuition leads many people down the wrong path: "if I send 4 processing commands at the same time, it will be 4x faster!" In practice, it can be slower, or even break.

Let's understand the difference between the two types of parallelism and when each is appropriate.

Internal Parallelism: Let AS Do the Work

When you send a single TMSL command with multiple objects, AS manages all parallelism internally. It analyzes dependencies between objects (for example, it knows that Process Recalc needs to wait for all Process Data to finish), distributes the work among available threads, and intelligently coordinates memory usage.

// Paralelismo interno: 1 comando sequence, N objetos, AS gerencia tudo
// maxParallelism e propriedade do sequence, nao do refresh
{
  "sequence": {
    "maxParallelism": 8,
    "operations": [
      {
        "refresh": {
          "type": "full",
          "objects": [
            { "database": "MeuModeloAAS", "table": "FatoVendas",    "partition": "FatoVendas_2024_11" },
            { "database": "MeuModeloAAS", "table": "FatoVendas",    "partition": "FatoVendas_2024_12" },
            { "database": "MeuModeloAAS", "table": "FatoEntradas",  "partition": "FatoEntradas_Hot" },
            { "database": "MeuModeloAAS", "table": "FatoDevolucoes","partition": "FatoDevolucoes_Hot" },
            { "database": "MeuModeloAAS", "table": "DimProduto" },
            { "database": "MeuModeloAAS", "table": "DimCliente" }
          ]
        }
      }
    ]
  }
}

The maxParallelism parameter defines the ceiling for simultaneously processed objects. If not specified, AS uses its own criteria (usually based on the number of CPU cores). Increasing it beyond available cores rarely helps and can worsen performance due to resource contention.

This is the recommended standard for 99% of cases. It is safe, predictable, and AS optimizes it automatically.

External Parallelism: Why It Seems Like a Good Idea but (Almost Always) Isn't

“External parallelism” is when you, outside of AS, trigger multiple processing commands simultaneously, for example, 4 PowerShell jobs running in parallel, each sending a separate TMSL script to the same server.

# ANTI-PATTERN example: external parallelism
# Trigger 4 independent processes "at the same time"
$jobs = @(
    Start-Job { Invoke-ASCmd -Server $srv -Query '{"refresh":{"type":"full","objects":[{"database":"M","table":"FatoVendas","partition":"P1"}]}}' },
    Start-Job { Invoke-ASCmd -Server $srv -Query '{"refresh":{"type":"full","objects":[{"database":"M","table":"FatoVendas","partition":"P2"}]}}' },
    Start-Job { Invoke-ASCmd -Server $srv -Query '{"refresh":{"type":"full","objects":[{"database":"M","table":"FatoEntradas","partition":"P1"}]}}' },
    Start-Job { Invoke-ASCmd -Server $srv -Query '{"refresh":{"type":"full","objects":[{"database":"M","table":"DimProduto"}]}}' }
)
$jobs | Wait-Job | Receive-Job

In theory, it sounds great: 4 processes at the same time! In practice, the problems are serious:

Problems with External Parallelism

• Uncoordinated RAM memory spike: Each external command allocates memory independently, without communication between them. AS has no visibility into what other commands are doing. If each needs 10GB of RAM at peak and the server has 32GB total, you will hit the HardMemoryLimit and AS will start canceling transactions or rejecting connections.
• Contention in Process Recalc: Each TMSL command of type: "full" includes a Process Recalc at the end. When two commands reach the recalculation phase at the same time, they contend for an exclusive write lock on the model's metadata. One will execute, the other will wait (or fail with a timeout). You've lost all the benefits of parallelism.
• Metadata lock and transaction conflicts: Processing operations acquire write locks on the model. Two external commands trying to write at the same time generate a serialization queue or deadlock errors, resulting in sequential execution worsened by the overhead of managing multiple concurrent transactions.
• Inconsistency in case of partial failure: If 3 out of 4 commands complete and 1 fails, the model remains in an inconsistent state, some partitions updated, others not, without a transaction to group everything. With internal parallelism in a single TMSL, you explicitly control the commitMode and failure behavior.
• No dependency orchestration: AS, within a single TMSL, knows that it needs to process dimensions before recalculating fact tables that depend on them. With external commands, you need to implement this dependency logic manually, and it's easy to make mistakes.

When External Parallelism CAN Work

There are specific scenarios where sending multiple commands in parallel makes sense:

• Separate databases on the same server: Processing the "Sales" database and the "Finance" database simultaneously is safe; they have independent metadata, with no lock conflicts.
• Pure Process Data phase, without Process Recalc: If you ensure that all external commands are of the dataOnly type (without recalculation), lock contention is much lower. You trigger Process Data in parallel, wait for all to finish, and only then send a single calculate. But at this point, a single TMSL with sequence already does this better.
• Power BI Premium with Enhanced Refresh API: The Power BI refresh API (REST API v2) manages parallelism on the server, not on the client. You can submit multiple refresh requests that the service queues and orchestrates with resource visibility. This is indeed a well-implemented external parallelism.

The Correct Pattern for Maximum Parallelism

The most efficient and safest way to maximize parallelism in AAS processing is to use a single TMSL with the sequence + parallel dataOnly + final calculate pattern:

{
  "sequence": {
    "maxParallelism": 10,
    "operations": [
      {
        "refresh": {
          "type": "dataOnly",
          "objects": [
            { "database": "MeuModeloAAS", "table": "FatoVendas",    "partition": "FatoVendas_2024_12" },
            { "database": "MeuModeloAAS", "table": "FatoVendas",    "partition": "FatoVendas_Hot" },
            { "database": "MeuModeloAAS", "table": "FatoEntradas",  "partition": "FatoEntradas_Hot" },
            { "database": "MeuModeloAAS", "table": "FatoDevolucoes","partition": "FatoDevolucoes_Hot" },
            { "database": "MeuModeloAAS", "table": "DimProduto" },
            { "database": "MeuModeloAAS", "table": "DimCliente" },
            { "database": "MeuModeloAAS", "table": "DimCalendario" }
          ]
        }
      },
      {
        "refresh": {
          "type": "calculate",
          "objects": [{ "database": "MeuModeloAAS" }]
        }
      }
    ]
  }
}

In this pattern: all objects in step 1 are processed in parallel (up to the maxParallelism limit). AS waits for all to complete. Only then does it execute the single calculate in step 2. You get maximum parallelism with minimal contention and full transactional consistency.

Optimizing the Data Source: Your First Step to Performance

Optimization begins long before Analysis Services. AS is only as fast as your data source allows. If the query at the source takes 2 hours to return data, no model optimization will solve that. Therefore, let's start with the most overlooked layer: the source.

Diagnosing the Bottleneck at the Source

Before any optimization, you need to measure. Use DAX Studio to capture the query AS is sending to the data source:

1. Connect DAX Studio to your AAS/SSAS server.
2. Go to Advanced > View Metrics and enable Query Plan and Server Timings.
3. Execute an EVALUATE ROW("x", COUNTROWS(FactSales)) to force a full scan.
4. In the Server Timings tab, you will see the time spent in Storage Engine (SE), which includes the source read time, versus Formula Engine (FE).

Alternatively, during processing, check SQL Server Profiler or AAS/SSAS Extended Events to capture ProgressReportBegin/End events and identify which tables/partitions are slower.

SQL Server at the Source: Best Practices for AS Reading

Before creating any index, there are execution behavior adjustments that directly impact read time and are almost never configured. AS sends SQL queries to the source like any other client, but with patterns that can be suboptimal depending on the environment.

Isolation Level: Avoiding Blocks During Processing

By default, AS performs reads with READ COMMITTED. In databases with active ETL during AS processing (a common situation in tight pipelines), reads block on rows with write locks. The result is AS waiting for ETL to release locks before it can read, potentially adding tens of minutes to processing. For DW tables where the load has already completed before AS starts:

-- Option 1: NOLOCK in the partition query (dirty reads, suitable for DW with completed load)
SELECT DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda
FROM dbo.FatoVendas WITH (NOLOCK)
WHERE DataVendaKey >= 20240101 AND DataVendaKey < 20250101;

-- Option 2: READ_COMMITTED_SNAPSHOT in the database (MVCC: consistent reads without blocking writes)
-- Safer than NOLOCK, no dirty reads, no extra lock cost
-- Requires enabling once in the source database:
ALTER DATABASE MeuDW SET READ_COMMITTED_SNAPSHOT ON;
-- With RCSI active, AS reads the latest committed version of data without blocking writes

Statistics: Update After Each ETL Load

Outdated statistics cause the SQL Server optimizer to choose bad plans for queries that AS sends, potentially causing full scans on tables with billions of rows when a columnstore index would eliminate 99% of the rows. Schedule the update immediately after ETL:

-- Update critical table statistics with full scan after each load
UPDATE STATISTICS dbo.FatoVendas WITH FULLSCAN;
UPDATE STATISTICS dbo.DimProduto  WITH FULLSCAN;

-- Identify tables with outdated statistics (more than 10% of rows modified)
SELECT
    OBJECT_NAME(s.object_id)       AS tabela,
    s.name                          AS estatistica,
    sp.last_updated                 AS ultima_atualizacao,
    sp.rows                         AS linhas,
    sp.modification_counter         AS modificacoes_desde_atualizacao,
    CAST(sp.modification_counter * 100.0 / NULLIF(sp.rows, 0) AS DECIMAL(5,1)) AS pct_modificado
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_SCHEMA_NAME(s.object_id) = 'dbo'
  AND sp.rows > 0
  AND sp.modification_counter * 100.0 / sp.rows > 10
ORDER BY sp.modification_counter DESC;

OPTION RECOMPILE and MAXDOP in Partition Queries

AS generates queries with literals in partition filters (e.g., WHERE SaleDateKey >= 20240101). SQL Server may reuse a cached plan compiled for other values, which is not optimal for specific literals. OPTION (RECOMPILE) forces recompilation with current statistics for each execution, generating an ideal plan for the actual data. On shared servers, combine with MAXDOP to avoid affecting OLTP queries:

-- Optimized partition query: fresh plan + controlled parallelism
SELECT DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda
FROM dbo.FatoVendas WITH (NOLOCK)
WHERE DataVendaKey >= 20240101 AND DataVendaKey < 20250101
OPTION (MAXDOP 8, RECOMPILE);
-- MAXDOP 8: uses 8 cores for parallel reading, leaves others for OLTP
-- RECOMPILE: plan compiled with actual literals, takes advantage of partition elimination

Indexes in the Data Source (SQL Server / Azure Synapse)

For relational sources, indexes at the source are critical. AS usually executes a query like SELECT col1, col2, col3 FROM table WHERE filter and expects an efficient full scan. For this:

Clustered Columnstore Index (CCI): The King for DW

For fact tables in SQL Server or Azure Synapse, the Clustered Columnstore Index is the best choice. It stores data in a compressed columnar format, eliminates the need to read unnecessary columns, and is extremely efficient for analytical scans (which is exactly what AS does).

To create a CCI on an existing table, use the script below:

-- Creating CCI on existing fact table (SQL Server / Synapse)
DROP INDEX IF EXISTS CCI_FatoVendas ON dbo.FatoVendas;

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FatoVendas
ON dbo.FatoVendas
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE, MAXDOP = 8);

-- Check quality of created Row Groups
SELECT
    object_name(rg.object_id)   AS tabela,
    rg.partition_number,
    rg.row_group_id,
    rg.state_desc,
    rg.total_rows,
    rg.deleted_rows,
    rg.size_in_bytes / 1024.0   AS tamanho_kb
FROM sys.dm_db_column_store_row_group_physical_stats rg
WHERE object_id = OBJECT_ID('dbo.FatoVendas')
ORDER BY rg.row_group_id;

Result: Ideal row groups have close to 1,048,576 rows (1M). Much smaller row groups (below 100k rows) indicate fragmentation and should be reorganized with ALTER INDEX REORGANIZE.

NonClustered Columnstore Index (NCCI)

If your table already has a Clustered Index (B-Tree) and cannot be converted to CCI (for example, because it is a transactional table with frequent updates/deletes), a NonClustered Columnstore Index can be created only on the columns that AS will read:

-- NCCI only on columns used by Analysis Services
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_FatoVendas_AS
ON dbo.FatoVendas (DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda)
WHERE DataVendaKey >= 20200101;  -- Filter for data relevant to the model

Partitioning at the Source (SQL Server)

If your fact table is partitioned in SQL Server, configure the AS partition query to use partition elimination. Instead of filtering by date directly, query by the partition function:

-- AS partition query aligned with SQL Server partitioning
-- Partition "2024" in AS maps to 2024 partition in the source table
SELECT
    DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda, Margem
FROM dbo.FatoVendas
WHERE DataVendaKey >= 20240101
  AND DataVendaKey < 20250101
  -- SQL Server uses the constraint to perform partition elimination automatically

When the source table is partitioned and the query includes the partition filter criterion, SQL Server performs partition elimination and reads only the necessary filegroups, drastically faster than a full table scan.

Query Folding in Power Query / M

When your model uses Power Query (M) as a transformation layer, Query Folding is critical for performance. Query Folding is when Power Query can convert your M transformations into native SQL that is executed directly at the source, instead of pulling all raw data and filtering/transforming in memory in AS.

To check if a step in your query is performing folding, right-click on the step in the Power Query Editor and see if the "View Native Query" option is available (if available, folding is happening).

To force the filter at the source and ensure folding, make the date/key filter the first step of the query, before any other transformation:

-- In the SQL source, ensure the query with WHERE is already filtered
-- In Power Query, the first step should be the date filter:
-- Source = Sql.Database("server", "database"),
-- FatoVendas = Source{[Schema="dbo",Item="FatoVendas"]}[Data],
-- FiltroData = Table.SelectRows(FatoVendas, each [DataVendaKey] >= 20200101)
-- ^^ this performs query folding, generating WHERE DataVendaKey >= 20200101 at the source

Materialized Views in Azure Synapse Analytics

For sources in Azure Synapse Dedicated SQL Pool, a powerful feature is the Materialized View. Unlike a common view, the materialized view pre-computes and physically stores the results of a complex query (joins, aggregations), being automatically kept up-to-date by Synapse:

-- Creating a Materialized View in Azure Synapse to feed AS
CREATE MATERIALIZED VIEW dbo.mv_VendasPorDiaCategoria
WITH (DISTRIBUTION = HASH(DataVendaKey))
AS
SELECT
    fv.DataVendaKey,
    dp.CategoriaKey,
    dp.SubcategoriaKey,
    dc.RegiaoKey,
    SUM(fv.VlrVenda)        AS TotalVendas,
    SUM(fv.QtdVenda)        AS TotalQuantidade,
    SUM(fv.CustoVenda)      AS TotalCusto,
    COUNT(DISTINCT fv.ClienteKey) AS ClientesDistintos
FROM dbo.FatoVendas fv
INNER JOIN dbo.DimProduto dp ON fv.ProdutoKey = dp.ProdutoKey
INNER JOIN dbo.DimCliente dc ON fv.ClienteKey = dc.ClienteKey
GROUP BY fv.DataVendaKey, dp.CategoriaKey, dp.SubcategoriaKey, dc.RegiaoKey;

Result: AS reads from the materialized view instead of the raw fact table, skipping the join and aggregation that were previously done in Synapse with each processing. For models with complex joins at the source, this can reduce read time by 60–80%.

Table Distribution in Azure Synapse

Synapse Dedicated SQL Pool distributes data among 60 compute nodes. The wrong distribution choice can cause massive data movement during joins, making queries slow. For large tables read by AS:

• Hash Distribution: Ideal for large fact tables. Distribute by the key used in the most frequent joins (usually the date or product key). This places data from both sides of the join on the same node, eliminating data movement.
• Replicated: Ideal for small dimension tables (up to ~2 GB). A complete copy on each node eliminates all data movement for joins with the fact table.
• Round Robin: Default, avoid for tables read by AS in production.

-- Fact table: Hash on the join key with the main dimension
CREATE TABLE dbo.FatoVendas
(
    FatoVendaKey    BIGINT NOT NULL,
    DataVendaKey    INT    NOT NULL,
    ProdutoKey      INT    NOT NULL,
    ClienteKey      INT    NOT NULL,
    VlrVenda        DECIMAL(18,2),
    QtdVenda        INT
)
WITH
(
    DISTRIBUTION = HASH(ProdutoKey),  -- aligns with the most selective dimension
    CLUSTERED COLUMNSTORE INDEX
);

-- Small dimension table: Replicated (copy on all nodes)
CREATE TABLE dbo.DimProduto
(
    ProdutoKey      INT NOT NULL,
    NomeProduto     NVARCHAR(200),
    CategoriaKey    INT,
    Preco           DECIMAL(10,2)
)
WITH
(
    DISTRIBUTION = REPLICATE,
    CLUSTERED INDEX (ProdutoKey)
);

Co-Location and Network: The Silent Bottleneck

One of the most overlooked factors in AAS processing is network latency and bandwidth between the AAS server and the data source. During the processing of a large model, AS can transfer hundreds of gigabytes from the source. If this transfer goes through a connection with high latency or limited bandwidth, all gains from indexing and partitioning are compromised.

• Same Azure region: AAS and the source (Azure SQL, Synapse, ADLS) must be in the same Azure region. Cross-region traffic has additional latency (typically 20–100ms depending on distance) and may be charged as egress traffic. Intra-region traffic is free and has 1–2ms latency.
• AAS Firewall: Azure Analysis Services has a native IP firewall (configurable in the Azure portal, server's Firewall tab). Enable it and restrict inbound access only to the necessary IP ranges (e.g., Power BI Service IP, administrators, and deployment tools). AAS does not support Azure Private Link/Private Endpoint, unlike newer services like Fabric. To protect the data source from external access, configure the Azure SQL or Synapse firewall to accept only the outbound IP of the AAS server.
• On-Premises with ExpressRoute: If the data source is on-premises, use Azure ExpressRoute for a dedicated high-speed connection. Avoid processing AAS via basic VPN for large sources, as bandwidth is insufficient to transfer tens of gigabytes in a timely manner (a typical S2S VPN has theoretical 1–10 Gbps, but with latency and overhead that impact long transfers).
• On-Premises Data Gateway: For on-premises sources without ExpressRoute, the Gateway acts as a proxy between AAS and the local source. Install it on a machine on the same network as the source (not on the same machine as the source, to avoid CPU contention). Monitor the Gateway's CPU and memory usage during processing.
• Move data to Azure first: The most efficient pattern for hybrid environments is to use the data pipeline (ADF, Synapse Pipelines) to move data from on-premises to Azure (Azure SQL, Synapse, ADLS) as a first step, and only then AAS reads from the source, all within the same Azure region. This eliminates on-premises latency from the critical processing path.

Partitioning Strategies for Tabular Models

Partitioning is the single most impactful technique for optimizing the processing of large tabular models. Without partitioning, all processing is a Process Full on the entire table. With a good partitioning scheme, you can reduce the volume of reprocessed data by 90%+ per operation.

Why Partition? The Three Main Benefits

• 1. Incremental Processing: Process only the slice of data that has changed, instead of the entire table. A table with 5 years of data with monthly partitions allows processing only the current month, 1/60 of the total data.
• 2. Parallelism: AS can process multiple partitions in parallel, utilizing all available CPU cores. Without partitions, processing a single table is single-threaded.
• 3. High Availability: While one partition is being reprocessed, the others remain available for queries. Users do not lose access to historical data during the update.

Choosing Partition Granularity

The ideal granularity depends on the data volume and update frequency. As a general rule:

• Annual partitions: For tables with a few million rows per year or historical data rarely updated.
• Monthly partitions: The most common pattern for medium-to-large fact tables (tens of millions of rows/month).
• Daily partitions: For tables with very high volume (hundreds of millions of rows/day) or when the update granularity is hourly/daily.
• Partitions by entity: For specific scenarios such as multi-tenancy (one partition per client/region), widely used in Power BI Embedded.

Hot/Warm/Cold Partitioning Pattern

This is the most sophisticated and efficient partitioning pattern for large models. The idea is to divide partitions into three layers:

• Hot (Current Partition): Data from the last few days/weeks that may still change. Reprocessed with Process Full every cycle (daily or hourly). Typically 1 partition.
• Warm (Last few months): Data from 1–6 months ago, consolidated but still relevant for recent analyses. Reprocessed weekly or when corrections occur. Generally 6–12 monthly partitions.
• Cold (History): Data from previous years. Consolidated and immutable. Never reprocessed in the normal routine, only when there is a specific historical correction. Can have dozens of annual partitions.

With this pattern, a typical daily update processes only the "Hot" partition, which may contain only the current day, instead of years of history.

Creating and Managing Partitions via TMSL

To automate partition creation, you can use TMSL scripts executed via SSMS, PowerShell, or Azure Data Factory. See a complete example of creating monthly partitions for 12 months:

{
  "createOrReplace": {
    "object": {
      "database": "MeuModeloAAS",
      "table": "FatoVendas",
      "partition": "FatoVendas_2024_01"
    },
    "partition": {
      "name": "FatoVendas_2024_01",
      "source": {
        "type": "query",
        "query": "SELECT DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda FROM dbo.FatoVendas WHERE DataVendaKey >= 20240101 AND DataVendaKey < 20240201",
        "dataSource": "DS_Synapse"
      },
      "mode": "import"
    }
  }
}

Automating Partitions with PowerShell and AMO

To create partitions dynamically (e.g., automatically create the next month's partition), use PowerShell with the AMO (Analysis Management Objects) library or with the SqlServer module:

# Install the SqlServer module if necessary: Install-Module -Name SqlServer
Import-Module SqlServer

$servidor   = "asazure://brazilsouth.asazure.windows.net/meuservidor"
$banco       = "MeuModeloAAS"
$tabela      = "FatoVendas"
$dataFonte   = "DS_Synapse"

# Connect to the AS server
$server = New-Object Microsoft.AnalysisServices.Tabular.Server
$server.Connect($servidor)
$db    = $server.Databases[$banco]
$table = $db.Model.Tables[$tabela]

# Create partitions for each month of 2024
$ano = 2024
for ($mes = 1; $mes -le 12; $mes++) {
    $inicio  = Get-Date -Year $ano -Month $mes -Day 1
    $fim     = $inicio.AddMonths(1)
    $nomePart = "FatoVendas_{0}_{1:D2}" -f $ano, $mes
    $iniKey  = $inicio.ToString("yyyyMMdd")
    $fimKey  = $fim.ToString("yyyyMMdd")

    # Check if it already exists
    if ($table.Partitions[$nomePart]) {
        Write-Host "Partição $nomePart já existe, pulando..." -ForegroundColor Yellow
        continue
    }

    $particao = New-Object Microsoft.AnalysisServices.Tabular.Partition
    $particao.Name = $nomePart

    $fonte = New-Object Microsoft.AnalysisServices.Tabular.QueryPartitionSource
    $fonte.DataSource = $db.Model.DataSources[$dataFonte]
    $fonte.Query = "SELECT DataVendaKey, ProdutoKey, ClienteKey, VlrVenda, QtdVenda FROM dbo.FatoVendas WHERE DataVendaKey >= $iniKey AND DataVendaKey < $fimKey"

    $particao.Source = $fonte
    $table.Partitions.Add($particao)

    Write-Host "Partição $nomePart criada." -ForegroundColor Green
}

$db.Model.SaveChanges()
$server.Disconnect()
Write-Host "Partições salvas com sucesso!" -ForegroundColor Cyan

Result: With this script scheduled in Azure Automation or Task Scheduler, you ensure that the next month's partition is created automatically, without manual intervention.

Parallel Partition Processing

AS supports native parallel processing when you include multiple objects in the same TMSL refresh command. The server automatically decides the level of parallelism based on available resources. To force maximum parallelism:

{
  "sequence": {
    "maxParallelism": 10,
    "operations": [
      {
        "refresh": {
          "type": "full",
          "objects": [
            { "database": "MeuModeloAAS", "table": "FatoVendas",    "partition": "FatoVendas_Hot" },
            { "database": "MeuModeloAAS", "table": "FatoEntradas",  "partition": "FatoEntradas_Hot" },
            { "database": "MeuModeloAAS", "table": "FatoDevolucoes","partition": "FatoDevolucoes_Hot" }
          ],
          "retryCount": 2,
          "commitMode": "transactional"
        }
      }
    ]
  }
}

The maxParallelism parameter controls how many partitions are processed simultaneously. The default is the number of CPU cores on the server. Increasing this value can speed up processing on servers with many cores, but it increases peak RAM usage (each partition being processed occupies memory).

The commitMode parameter accepts two values:

• "transactional": All partitions in the batch are committed together at the end. If one fails, all roll back. Ensures consistency but keeps old data available until the final commit (hot swap).
• "partialBatch": Each partition is committed individually as soon as it finishes. If one fails, those already committed remain. Less memory, but possible temporary inconsistency between partitions.

Scale-Out in AAS: Zero Downtime during Processing

Scale-Out is one of the most powerful features of Azure Analysis Services in enterprise production and one of the least used. With it, you add up to 7 read replicas to the server. Processing occurs exclusively on the write instance, while the read replicas continue serving queries to users with data from the last successful cycle. The result: users never notice that processing is happening.

How Scale-Out Works

The flow of an update with Scale-Out active has four steps:

1. You send the TMSL refresh to the write endpoint (suffix :rw). Replicas continue responding to queries with previous data, without interruption.
2. Processing occurs isolated on the write instance. All RAM allocation, source reading, encoding, and recalculation happen only there, without competing with read replicas.
3. After processing completes, you trigger synchronization. AS writes the updated model to shared storage (Azure Blob) and signals replicas to reload.
4. Each replica performs an internal hot-swap: loads the new model in parallel with the old model and, when ready, swaps atomically. During this process (usually seconds to a few minutes), replicas still serve old data. After the swap, they start using new data.

Configuring and Using Scale-Out

To process without interrupting queries, send the TMSL to the write endpoint (:rw) and synchronize immediately after:

# Read endpoint: the default, automatically routes to replicas
$endpointLeitura = "asazure://brazilsouth.asazure.windows.net/meuservidor"

# Write endpoint: only for processing, goes to the primary instance
$endpointEscrita = "asazure://brazilsouth.asazure.windows.net/meuservidor:rw"

# Step 1: process on the write instance (replicas continue serving old data)
Invoke-ASCmd -Server $endpointEscrita -InputFile "refresh.tmsl"

# Step 2: synchronize replicas (each replica loads the new model and performs a hot-swap)
Invoke-ASCmd -Server $endpointEscrita -Query '{
  "synchronize": {
    "database": "MeuModeloAAS",
    "syncReadOnly": true,
    "syncOnlyCommittedSegments": true
  }
}'

Write-Host "Processamento e sincronizacao concluidos."

The parameter syncOnlyCommittedSegments: true transfers only the segments that were effectively changed during processing, instead of copying the entire model. For models with granular partitioning where only a few partitions have been updated, this greatly reduces synchronization time.

When Scale-Out Pays Off

• 24/7 availability SLA: If the model needs to be continuously available (users in multiple time zones, real-time operations dashboards), Scale-Out eliminates unavailability windows.
• Long processing times (1–4+ hours): For large models that take hours to process, Scale-Out ensures that this entire window does not cause unavailability for users.
• High query concurrency: Replicas distribute the load. A heavy query from one user does not block responses for others.

Semantic Model Optimizations with Tabular Editor and DAX Studio

Now that the source is optimized and partitioning is well-defined, it's time to look inside the model. This is where the most powerful tools in the arsenal come in: Tabular Editor and DAX Studio. Both are free (with paid versions) and indispensable for any serious professional working with Analysis Services or Power BI.

Connecting with Tabular Editor and DAX Studio

Both tools connect via XMLA Endpoint:

• Azure Analysis Services: The endpoint follows the format asazure://<region>.asazure.windows.net/<server-name>. Find it in the Azure Portal, on the AAS server blade under "Overview".
• SSAS on-premises: Use the Windows server name or IP address.
• Power BI Premium/Fabric: The XMLA endpoint is powerbi://api.powerbi.com/v1.0/myorg/<workspace-name>. It needs to be enabled in the workspace settings (Settings > Premium > Dataset connections).
• Power BI Desktop (local): Open the model in PBI Desktop and connect DAX Studio via "Power BI / SSDT". Tabular Editor can be opened as an External Tool directly from PBI Desktop.

VertiPaq Analyzer: Model X-Ray

The VertiPaq Analyzer is the starting point for any tabular model optimization analysis. Available in DAX Studio (Advanced menu > View Metrics) and as an add-in in Tabular Editor, it shows:

• Size of each table and column in memory.
• Compression rate per column. Columns with low compression are candidates for encoding optimization.
• Cardinality of each column. High cardinality = more memory = more processing time.
• Number of segments per column. Many small segments = fragmentation = poor performance.
• Size of relationships and hierarchies.

To generate the report via DAX Studio, use the script below:

-- In DAX Studio, go to Advanced > View Metrics for the visual report
-- To extract data via DMV (Dynamic Management View):
SELECT
    DIMENSION_NAME        AS Tabela,
    ATTRIBUTE_NAME        AS Coluna,
    DICTIONARY_SIZE       AS TamanhoDicionario_Bytes,
    ATTRIBUTE_DATA_SIZE   AS TamanhoForwardIndex_Bytes,
    ATTRIBUTE_INDEX_SIZE  AS TamanhoInvertedIndex_Bytes,
    ATTRIBUTE_HIERARCHY_SIZE AS TamanhoHierarquia_Bytes,
    USERS_ATTRIBUTE_HIERARCHY_SIZE AS TamanhoHierPublica_Bytes
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS
WHERE ATTRIBUTE_NAME <> 'RowNumber'
ORDER BY (DICTIONARY_SIZE + ATTRIBUTE_DATA_SIZE + ATTRIBUTE_INDEX_SIZE) DESC;

Result: You see exactly which columns are consuming the most memory and can prioritize optimizations where the impact will be greatest.

Best Practice Analyzer: Automated Model Audit

The Best Practice Analyzer (BPA) in Tabular Editor is an automated analysis of the model against a set of best practice rules. Think of it as a linter for tabular models: it points out performance, design, and maintainability issues with explanations and suggestions for correction.

To run: in Tabular Editor 2 or 3, go to Tools > Best Practice Analyzer. The main rule categories:

• Performance: Calculated columns that should be DAX measures, large tables without partitioning, columns with high cardinality without EncodingHint, unnecessary bidirectional relationships, date/time columns without component separation.
• Model: Dimension tables without a column marked as key, many-to-many relationships without a bridge table, hierarchies with only one level (useless).
• Maintainability: Measures without descriptions, visible objects without defined number format, use of DIVIDE() instead of direct division (safety against division by zero).

Microsoft and the community maintain a set of public rules on GitHub at TabularEditor/BestPracticeRules. To import into Tabular Editor:

// No Tabular Editor 2/3: Tools > Best Practice Analyzer > Manage Rules
// Cole a URL do arquivo JSON de regras da comunidade:
// https://raw.githubusercontent.com/TabularEditor/BestPracticeRules/master/BPARules.json
// O TE faz o download e aplica automaticamente

You can create custom rules for your team's standards (measure naming, mandatory prefixes, acceptable cardinality limits) and version the rules along with the model in Git. Run the BPA before each deploy to ensure the model has not regressed.

Column and Data Type Analysis: What to Cut and What to Change

After the VertiPaq Analyzer, the first practical action is to remove what doesn't need to be in the model and correct inappropriate data types.

Removing Unnecessary Columns

Every column in the tabular model occupies RAM, consumes processing time (encoding + indexing), and increases the size of the .abf file (backup). If a column is not used in reports, measures, filters, or relationships, it should not be in the model.

Use the following C# script in Tabular Editor to identify columns that have no references in measures or relationships:

// C# script in Tabular Editor 2 or 3 - List unused columns
// Execute in: Macros > New Macro > Paste the code below

var colunasUsadasEmMedidas = new HashSet<string>();

foreach (var measure in Model.AllMeasures)
{
    foreach (var col in Model.AllColumns)
    {
        var refName = "[" + col.Name + "]";
        var refFull = "'" + col.Table.Name + "'[" + col.Name + "]";
        if (measure.Expression.Contains(refName) || measure.Expression.Contains(refFull))
            colunasUsadasEmMedidas.Add(col.DaxObjectFullName);
    }
}

var colunasEmRelacionamentos = new HashSet<string>();
foreach (var rel in Model.Relationships)
{
    colunasEmRelacionamentos.Add(rel.FromColumn.DaxObjectFullName);
    colunasEmRelacionamentos.Add(rel.ToColumn.DaxObjectFullName);
}

var sem_uso = Model.AllColumns
    .Where(c => c.Type == ColumnType.Data)
    .Where(c => !colunasUsadasEmMedidas.Contains(c.DaxObjectFullName))
    .Where(c => !colunasEmRelacionamentos.Contains(c.DaxObjectFullName))
    .Where(c => !c.IsHidden)  // visible column (IsHidden = false)
    .Select(c => c.DaxObjectFullName)
    .OrderBy(x => x)
    .ToList();

Info("Colunas possivelmente sem uso:\n" + string.Join("\n", sem_uso));

Optimizing Data Types

The data type directly impacts VertiPaq compression and memory usage. See the most common types and their implications:

• Int64 (Integer): Best compression with Value Encoding. Ideal for IDs, keys, quantities.
• Decimal (Fixed number): Internally stored as an integer multiplied by a scale factor. Good compression for monetary values with fixed decimal places.
• Double (Floating-point number): Lower compression than Decimal for monetary values. Use Decimal for prices and financial values.
• DateTime: Stored as Double internally in VertiPaq. High cardinality when including time (each unique timestamp is a distinct value). Prefer to store only the date (without time component) when possible, or use an integer key in YYYYMMDD format.
• String: The lowest compression of all. Avoid string columns with high cardinality (like URLs, GUIDs, hashes). If you need GUID for relationships, convert to Int64 in ETL.
• Boolean: Excellent compression (only 2 possible values in the dictionary). Prefer Boolean to "Y/N" or "Yes/No" string fields.

To identify data type optimization opportunities via DMV, use the script below:

-- Identify String columns with high cardinality (candidates for conversion to Int)
SELECT
    DIMENSION_NAME          AS Tabela,
    ATTRIBUTE_NAME          AS Coluna,
    DATATYPE                AS TipoDado,
    DICTIONARY_SIZE / 1024  AS DicionarioKB
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMNS
WHERE DATATYPE = 'WChar'         -- String type in VertiPaq
  AND DICTIONARY_SIZE > 1048576  -- Dictionary larger than 1MB
  AND ATTRIBUTE_NAME <> 'RowNumber'
ORDER BY DICTIONARY_SIZE DESC;

Optimizing Relationships: Cardinality, Direction, and Impact

Relationships in the tabular model impact both processing (time to build relationship indexes) and DAX queries. Understanding the options is crucial.

Relationship Cardinality

• Many-to-One: The default and most efficient. The fact table (Many) relates to the dimension table (One) on the primary key side of the dimension. AS optimizes this pattern internally.
• One-to-One: Used for extension tables or when you split a large table into two for performance reasons. It has a similar cost to M:1.
• Many-to-Many: Avoid when possible. Internally, AS implements M:M using bidirectional cross-filters and intermediate materializations, which are expensive for complex queries. If you need M:M, prefer using a bridge/junction table with explicit M:1 relationships on both sides.

Bidirectional Filtering: Use with Caution

Bidirectional filtering (Cross Filter Direction = Both) allows filters applied to one table to propagate in both directions of the relationship, instead of just from the dimension to the fact. It seems useful but has serious implications:

• Ambiguity in complex models: In models with multiple paths between tables, bidirectional filtering can create filtering loops that return incorrect results or "ambiguous relationship" errors.
• Impact on query performance: DAX needs to resolve filter propagation in both directions, which increases the complexity of the query plan.
• Recommended alternative: Use the DAX function CROSSFILTER() within specific measures where bidirectional behavior is needed, instead of enabling it globally on the relationship. This gives you precise control over when cross-filtering occurs.

-- DAX measure using CROSSFILTER() for bidirectional only when necessary
Clientes com Compras Categoria X =
CALCULATE(
    DISTINCTCOUNT(FatoVendas[ClienteKey]),
    CROSSFILTER(FatoVendas[ProdutoKey], DimProduto[ProdutoKey], Both),
    DimProduto[Categoria] = "Eletrônicos"
)

EncodingHint: Advanced Column Compression Control

EncodingHint is one of VertiPaq's most powerful and least known optimizations. It suggests to the engine how to encode and store the data of a specific column. It exists per column and can be configured via Tabular Editor.

The Two Types of VertiPaq Encoding

Internally, VertiPaq uses two encoding algorithms:

• Value Encoding: The original (numeric) value is stored directly in the forward index, after a simple linear transformation (multiply by a factor and add an offset so that all values fit into the smallest possible integer). It is extremely efficient for numeric columns with linearly growing values (sequential IDs, dates like YYYYMMDD, quantities, monetary values without many decimal places). Access is O(1); given the row ID, you go directly to the value without consulting a dictionary.
• Hash Encoding: A dictionary of unique values is created, and the forward index stores only the index in the dictionary (a small integer). It is more efficient for strings and for numbers with high cardinality and irregular distribution. Access requires a dictionary lookup, but the dictionary is highly compressed.

By default, AS tries to use Value Encoding for numbers and Hash Encoding for strings. However, for numeric columns with high cardinality and irregular values (e.g., decimal price values with many different places), AS may make the wrong choice and use Hash when Value would be better, or vice versa. This worsens compression and processing time.

When to Force Value vs. Hash Encoding

• Force Value: For integer numeric keys (IDs, surrogate keys), dates in YYYYMMDD format, times in HHMM format. Columns where values are continuous or have high density.
• Force Hash: For strings with high cardinality (product names, descriptions, emails). For decimal numbers with much variation in values (discounted prices, GPS coordinates).

To configure via C# script in Tabular Editor:

// Force Value Encoding on all Int64 columns (integer keys)
foreach (var col in Model.AllColumns.Where(c => c.DataType == DataType.Int64))
{
    col.EncodingHint = EncodingHint.Value;
}

// Force Hash Encoding on all String columns with cardinality > 10,000
// (requires VertiPaq Analyzer to get cardinality, here we use heuristics by name)
foreach (var col in Model.AllColumns.Where(c => c.DataType == DataType.String))
{
    col.EncodingHint = EncodingHint.Hash;
}

// Save changes
Model.SaveChanges();
Info("EncodingHint configurado com sucesso em " + Model.AllColumns.Count() + " colunas.");

Result: In models with many integer keys being encoded as Hash (which happens when AS misjudges the heuristic during the first processing and persists the wrong encoding), forcing Value Encoding can reduce model size by 30–50% and significantly speed up processing and queries. Wow!!

Calculated Columns vs. DAX Measures: Impact on Processing

This distinction has a direct impact on processing time and memory consumption:

• Calculated Columns: Are calculated at processing time and physically stored in the model (in memory), like any other imported column. They increase model size, increase processing time (Process Recalc recalculates all of them), and can use a lot of memory if they are complex or have high cardinality. Use when you need to filter, group, or create relationships based on the calculation result.
• Measures: Are calculated at query time, at the moment the report is generated. They do not store data. They do not impact processing. Use measures for any calculation that does not need to be used as a filter key or relationship.

Managing Segments: VertiPaq Fragmentation

Internally, VertiPaq divides each column into data blocks called segments. The ideal segment size is 8 million rows. When you perform many incremental Process Add operations over time, VertiPaq creates many small segments (one for each Process Add), which degrades scan and compression performance.

To visualize segment fragmentation:

-- Check segment fragmentation by column
SELECT
    DIMENSION_NAME   AS Tabela,
    PARTITION_NAME   AS Particao,
    ATTRIBUTE_NAME   AS Coluna,
    COUNT(*)         AS NumSegmentos,
    SUM(RECORDS_COUNT) AS TotalLinhas,
    AVG(RECORDS_COUNT) AS MediaLinhasPorSegmento,
    MIN(RECORDS_COUNT) AS MenorSegmento,
    MAX(RECORDS_COUNT) AS MaiorSegmento
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
WHERE ATTRIBUTE_NAME <> 'RowNumber'
GROUP BY DIMENSION_NAME, PARTITION_NAME, ATTRIBUTE_NAME
HAVING COUNT(*) > 1
ORDER BY NumSegmentos DESC, DIMENSION_NAME, ATTRIBUTE_NAME;

Columns with many small segments should be defragmented with Process Defrag on the corresponding table or partition. A Process Full on the partition also resolves fragmentation, rebuilding all segments from scratch.

Aggregation Tables: The Most Powerful Technique for Models with Billions of Rows

Aggregation tables are the most powerful, most ignored, and most poorly documented feature of modern Analysis Services. If you work with models that have fact tables of billions of rows and don't know this feature, this might be the most valuable part of this entire article.

The central idea: you create a pre-aggregated table with a few million rows (totals by day, by product, by region), import it into the model, and configure the mapping to the detail table. From then on, AS automatically routes each DAX query to the aggregation table when the granularity allows, without you changing a line of DAX code or a Power BI report. For queries that need transaction granularity, AS falls back to the detail table.

How AS Automatically Uses Aggregations

Routing is done at query time by the VertiPaq Storage Engine. AS evaluates whether the query can be answered with the aggregation table by checking:

• Are all GROUP BY fields in the query mapped in the aggregation table?
• Do all measure fields (SUM, COUNT, MIN, MAX) have corresponding pre-calculated columns in the aggregation?
• Are there no filters on fields that do not exist in the aggregation (which would require details)?

If all conditions are met, AS uses the aggregation (tens of millions of times faster for large models). If a condition fails, it automatically falls back to the detail table. This behavior is completely transparent to those who wrote the DAX measures and to those who use the reports.

Creating an Aggregation Table at the Source

The first step is to create the pre-calculated table at the source. It needs to contain the dimensions at the desired granularity level and the pre-summed metrics:

-- Aggregation table: daily totals by product, customer and region
-- FactSales has 5 billion rows; FactSalesAgr will have ~10 million
CREATE TABLE dbo.FatoVendasAgr
WITH (
    DISTRIBUTION = HASH(DataVendaKey),
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT
    fv.DataVendaKey,
    fv.ProdutoKey,
    fv.ClienteKey,
    dc.RegiaoKey,
    SUM(fv.VlrVenda)    AS SomaVlrVenda,
    SUM(fv.QtdVenda)    AS SomaQtdVenda,
    SUM(fv.CustoVenda)  AS SomaCusto,
    COUNT_BIG(*)        AS ContaLinhas
FROM dbo.FatoVendas fv
INNER JOIN dbo.DimCliente dc ON fv.ClienteKey = dc.ClienteKey
GROUP BY fv.DataVendaKey, fv.ProdutoKey, fv.ClienteKey, dc.RegiaoKey;

-- Update the aggregation table after each ETL load:
-- Truncate + reinsert or incremental Merge, depending on volume
TRUNCATE TABLE dbo.FatoVendasAgr;
INSERT INTO dbo.FatoVendasAgr
SELECT ... (mesma query acima)

Configuring Aggregation Mapping via TMSL

After importing FactSalesAgg as a normal table into the model, configure the alternateOf property on each column to indicate which column of the detail table it represents and which summary function it applies:

// Trecho do model.bim configurando a tabela de agregacao
// A tabela deve ser marcada como Hidden para nao aparecer no Power BI
{
  "name": "FatoVendasAgr",
  "isHidden": true,
  "columns": [
    {
      "name": "DataVendaKey",
      "dataType": "int64",
      "alternateOf": {
        "summarization": "groupBy",
        "table": "FatoVendas",
        "column": "DataVendaKey"
      }
    },
    {
      "name": "ProdutoKey",
      "dataType": "int64",
      "alternateOf": {
        "summarization": "groupBy",
        "table": "FatoVendas",
        "column": "ProdutoKey"
      }
    },
    {
      "name": "SomaVlrVenda",
      "dataType": "decimal",
      "alternateOf": {
        "summarization": "sum",
        "table": "FatoVendas",
        "column": "VlrVenda"
      }
    },
    {
      "name": "ContaLinhas",
      "dataType": "int64",
      "alternateOf": {
        "summarization": "countRows",
        "table": "FatoVendas"
      }
    }
  ]
}

In Tabular Editor 3, the graphical interface in the Aggregations tab of each table allows configuring mappings without manually editing TMSL. Tabular Editor 2 (open source) requires editing via C# scripts or directly in model.bim.

The Most Powerful Pattern: Composite Model (DirectQuery + Import)

Aggregating an Import table with another Import table already helps, but the real gain comes with the Composite Model:

• Detail table (FactSales): DirectQuery mode. Zero RAM consumption in the model. Always updated in real time. Answered only when the query needs transaction granularity.
• Aggregation table (FactSalesAgg): Import mode. Small enough to fit in memory (10 million rows instead of 5 billion). Reprocessed in minutes. Answers the vast majority of analytical queries.

With this setup, you completely eliminate the processing of billions of detail rows. Processing is now only for aggregation and dimensions, which are much smaller. The user gets Import performance for 90%+ of queries and fresh data via DirectQuery when needed, all automatically.

Verifying if Aggregation is Being Used

In DAX Studio, with Server Timings enabled, run a query and observe:

• "Direct Query" Column: Shows 0 ms for queries answered entirely by the aggregation in Import. If DQ time appears, the query was not resolved by the aggregate.
• Query Plan: Look for the AggregationHit node (used the aggregate, fast response) versus DirectQueryHit (went to the source, slower).

If a query that should use aggregation is going to DirectQuery, the most common reasons are: the query filters by a column not mapped in the aggregation, the query granularity is finer than the aggregation, or the alternateOf mapping is incorrect.

Columnstore Indexes in the Tabular Context: VertiPaq is a Columnstore

When we talk about optimizing the data source with Clustered Columnstore Indexes (CCI) in SQL Server, many people wonder: "But VertiPaq itself is also a columnstore? Why would it need CCI at the source?"

The answer is yes, and understanding how VertiPaq works internally explains why CCI at the source still helps, and how the two technologies complement each other.

How VertiPaq Stores Data Internally

VertiPaq is essentially an in-memory columnstore. Each column is compressed and stored independently of the others. When querying only 3 columns from a 100-column table, VertiPaq physically reads only the 3 necessary columns, it does not scan all 100.

Internally, each column uses two types of indexes:

• Forward Index: Maps Row ID → Column Value. Allows you to get the value of that column given a Row ID. Used in operations that need to materialize values (SUMX, ADDCOLUMNS, etc.).
• Inverted Index: Maps Value → List of Row IDs (bitmap). Allows you to get all Row IDs that have that value (or range) given a value. Used in filter and GROUP BY operations (CALCULATE, FILTER, SUMMARIZE, etc.).

The Inverted Index uses Bitmap Filter Pushdown; when multiple filters are combined (e.g., Year=2024 AND Category="Electronics"), VertiPaq combines the bitmaps of the two filters with a bitwise AND operation, obtaining the set of Row IDs that satisfy both criteria without scanning the data row by row. This is extremely efficient and is why tabular models are so fast for multidimensional filters.

Why CCI at the Source Still Matters

VertiPaq stores data in memory. To populate it, it needs to read data from the source during processing. It is in this reading that CCI at the source helps:

• Faster source reading: SQL Server with CCI can execute the read query using Batch Mode, full parallelism, and filter pushdown to the columnstore. Data arrives faster for AS to process.
• On-the-wire compression: SQL Server can transfer data already in compressed columnstore format to AS, reducing network traffic.
• Less I/O in SQL Server: CCI is stored compressed on disk. Reading CCI to feed AS generates much less I/O in SQL Server than reading an equivalent heap or rowstore index.

Aligning VertiPaq Partitions with Columnstore Segments

There is a subtle point of alignment between tabular model partitions and CCI Row Groups. Ideally, a partition query in AS should read complete CCI Row Groups; this activates Row Group Elimination optimization in SQL Server and ensures that the data arrives in the best possible format for VertiPaq to build its segments with optimal size.

To do this, design your AS partition queries to align with the CCI partition criteria at the source. If the CCI uses a partition by year/month, each AS partition query should filter the same date range.

Extra Tips for High Performance in Production

DMVs for Real-Time Processing Monitoring

During processing operations, especially in critical productions, it is important to monitor progress. AS exposes DMVs that allow this:

-- Active sessions on the server
SELECT SESSION_ID, SESSION_CURRENT_DATABASE, SESSION_ELAPSED_TIME_MS,
       SESSION_CPU_TIME_MS, SESSION_USER_NAME, SESSION_STATUS
FROM $SYSTEM.DISCOVER_SESSIONS
WHERE SESSION_CURRENT_DATABASE <> ''
ORDER BY SESSION_ELAPSED_TIME_MS DESC;

-- Processing jobs in progress
SELECT JOB_ID, JOB_DESCRIPTION, JOB_THREADPOOL_ID, JOB_READS, JOB_WRITES,
       JOB_CPU_TIME_MS, JOB_START_TIME
FROM $SYSTEM.DISCOVER_JOBS;

-- Object activity (processing by table/partition)
SELECT SPID, START_TIME, OBJECT_TYPE, OBJECT_PATH, OBJECT_ID,
       CURRENT_ACTION, CURRENT_ACTION_STEP, CURRENT_ACTION_STEP_DESCRIPTION
FROM $SYSTEM.DISCOVER_OBJECT_ACTIVITY
ORDER BY START_TIME DESC;

Memory Management: AS Settings

Analysis Services exposes a set of memory parameters that control how the engine manages VertiPaq, the query cache, and memory pressure. In SSAS on-premises, these parameters are in the msmdsrv.ini file and can be adjusted via SSMS (right-click on the server > Properties > General > Show Advanced Properties). In AAS, some of these values are platform-managed and cannot be changed, as the SQLBI article Optimizing memory settings in Analysis Services (Marco Russo) details. Understanding each parameter is essential for diagnosing memory errors and adjusting server behavior:

LowMemoryLimit (default: 65%)

Memory percentage from which AS proactively starts releasing caches. Below this limit, AS keeps all data cached without releasing anything. As usage grows above 65%, the cleaner becomes progressively more aggressive until it reaches TotalMemoryLimit, when it evicts everything not in active use.

• Default value (65%) is suitable for dedicated servers. On servers with more than 100 GB of RAM exclusively dedicated to AS, consider increasing to 70–75% to reduce premature cache evictions.
• Reduce if other services compete with AS for memory on the same server (multiple SSAS instances, SQL Server on the same host).

TotalMemoryLimit (default: 80%)

When usage exceeds this percentage, the memory manager evicts all cached data not in active use. In AAS, this value depends on the service plan and cannot be changed. In dedicated SSAS on-premises with a lot of RAM (>100 GB), it can be raised to 90–95%.

HardMemoryLimit (default: 0 = automatic)

Absolute maximum memory limit. If AS exceeds this value, the system actively cancels sessions to reduce consumption, and users receive a memory pressure error. A value of 0 makes AS automatically calculate an intermediate value between TotalMemoryLimit and total physical memory. In AAS, this value cannot be changed, as the server uses all available physical memory of the plan. For dedicated SSAS, setting 99 or 100 is appropriate.

VertiPaqPagingPolicy (default: 0 in AAS, 1 in SSAS)

Controls whether VertiPaq can page to disk when memory is insufficient:

• 0 (paging disabled, default in AAS): If there is not enough memory for processing, it fails with an out-of-memory error. The process's working set is limited to VertiPaqMemoryLimit.
• 1 (paging enabled, default in SSAS): VertiPaq can use more memory than physically available, paging to the OS pagefile. Processing completes, but with severe performance degradation if paging occurs.

QueryMemoryLimit

Limits the memory used during query execution. In AAS, this limit applies to all memory used by DAX and MDX queries, not just intermediate materializations as in SSAS 2019. A value of 0 disables the limit.

• Reduce this limit if users are receiving memory errors during queries: this makes heavy queries fail earlier, protecting other sessions that have lower memory demand.

VertiPaqMemoryLimit

Defines the amount of memory VertiPaq can use. Behavior varies depending on VertiPaqPagingPolicy:

• With paging disabled (0): defines the total memory VertiPaq can lock in the working set.
• With paging enabled (1): defines the physical memory limit VertiPaq pins in memory; above this, the remainder can be paged.
• If other services compete with AS for memory, setting VertiPaqMemoryLimit below LowMemoryLimit reduces VertiPaq's pressure on the server.

HeapTypeForObjects and MemoryHeapType

Do not change these values from the default (-1). Older versions of SSAS (2012/2014) had fragmentation and memory leak issues that required manual adjustment of these parameters. In SSAS 2016 SP1+ and AAS, the engine automatically chooses the optimal allocator via the -1 value. If you upgraded from an old instance and these values are different from -1, correct them to -1.

Automating Processing via Azure Data Factory

Azure Data Factory (ADF) is Microsoft's orchestration solution for large-scale data pipelines, and it works very well for automating the complete cycle: load data into the source → process the AAS model. But it's important to know: ADF does not have a native activity specifically for Azure Analysis Services. Integration is done via Web Activity calling the AAS REST API, or via Azure Function Activity executing PowerShell/C# code with the SqlServer module.

REST API Pattern: Web Activity + Status Loop

AAS exposes an asynchronous REST API to trigger refreshes. The flow in ADF is:

1. Web Activity (POST) → triggers the refresh and receives an operation ID.
2. Until Activity (loop) → repeats until the status is succeeded or failed.
3. Web Activity (GET) inside Until → queries the operation status by ID.
4. If Condition Activity → checks the result and, in case of failure, triggers a notification (Logic App, email).

The refresh endpoint URL is:

# POST to trigger the refresh
https://<region>.asazure.windows.net/servers/<servername>/models/<database>/refreshes

# Example body (Process Full via REST)
{
    "Type": "Full",
    "CommitMode": "transactional",
    "MaxParallelism": 4,
    "RetryCount": 2,
    "Objects": []
}

# GET to check status (uses the operationId returned by POST)
https://<region>.asazure.windows.net/servers/<servername>/models/<database>/refreshes/<operationId>

Web Activity authentication should use ADF's Managed Identity (grant this identity the Analysis Services Administrator role on the AAS server) or a Service Principal. Configure in Web Activity: Authentication = MSI, Resource = https://*.asazure.windows.net.

Azure Function Pattern: Synchronous Processing

A simpler alternative is to use an Azure Function Activity that executes PowerShell with the SqlServer module. The function blocks until processing finishes, returning success or failure directly, without needing a polling loop:

# Azure Function (PowerShell) called by ADF via Azure Function Activity
param($Request, $TriggerMetadata)

# Authenticates with the Function App's Managed Identity
# Requires Connect-AzAccount -Identity in the Function App's profile.ps1,
# or explicitly called here:
Connect-AzAccount -Identity | Out-Null
$token = (Get-AzAccessToken -ResourceUrl "https://*.asazure.windows.net").Token

$tmsl = @"
{
  "refresh": {
    "type": "full",
    "objects": [{ "database": "$($Request.Body.database)" }]
  }
}
"@

# Invoke-ASCmd blocks until processing completes (synchronous)
$result = Invoke-ASCmd `
    -Server "asazure://brazilsouth.asazure.windows.net/meuservidor" `
    -AccessToken $token `
    -Query $tmsl

if ($result -match 'error') {
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = 500; Body = $result
    })
} else {
    Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
        StatusCode = 200; Body = 'Processamento concluido com sucesso'
    })
}

Pipeline Structure in ADF

A typical production pipeline integrated with AAS in ADF has the following structure:

• Activity 1: Copy Data, extracts data from the source (on-premises, S3, APIs) and loads it into the staging layer (Azure SQL or Synapse).
• Activity 2: Stored Procedure / Script, executes transformation logic and prepares the final tables.
• Activity 3: Azure Function Activity (or Web Activity + Until Loop), triggers the AAS model refresh and waits for completion.
• Activity 4: Web Activity (conditional), notifies the team via Logic App, Teams or email in case of failure.

Automatic Scale Up/Down via REST API: The "Processing Window" Trick

Azure Analysis Services allows you to change the SKU (tier) via REST API. This opens up a powerful strategy to reduce costs:

1. During the day (business hours): Keep the server in a smaller tier (e.g., S1 or S2) sufficient for user queries.
2. Processing window (2h/day): Before processing, call the API to scale to a larger tier (e.g., S4 or S8 with more RAM and CPUs), process the data, and immediately after scale back to the smaller tier.

Depending on processing time and data volumes, the cost of temporary scale-up can be much lower than maintaining the high tier 24/7. The recommended pattern is to encapsulate scale-up and scale-down in Azure Functions that call the Azure management REST API, triggered as Azure Function Activities in ADF: one before processing (scale-up) and another in the pipeline's finalization block (scale-down), to ensure the server returns to the smaller tier even in case of failure. The Function App needs to have Managed Identity enabled and receive the Contributor role (or a custom role with Microsoft.AnalysisServices/servers/write permission) on the AAS server via Azure RBAC:

# Azure Function (PowerShell) — Scale Up or Scale Down via management REST API
# Expected body: { "sku": "S4" } for scale-up | { "sku": "S1" } for scale-down
# The Function App's Managed Identity must have Contributor role on the AAS server.
param($Request, $TriggerMetadata)

$subscriptionId = $env:AAS_SUBSCRIPTION_ID
$resourceGroup  = $env:AAS_RESOURCE_GROUP
$serverName     = $env:AAS_SERVER_NAME
$skuTarget      = $Request.Body.sku   # e.g.: "S4" or "S1"
$apiVersion     = "2017-08-01"
$serverUri      = "https://management.azure.com/subscriptions/$subscriptionId" +
                  "/resourceGroups/$resourceGroup" +
                  "/providers/Microsoft.AnalysisServices/servers/$serverName"

# Token via IMDS (the Function App's own Managed Identity)
$imdsUri   = "http://169.254.169.254/metadata/identity/oauth2/token" +
             "?api-version=2018-02-01&resource=https://management.azure.com/"
$tokenResp = Invoke-RestMethod -Method Get -Uri $imdsUri -Headers @{ Metadata = "true" }
$authHeader = @{ Authorization = "Bearer $($tokenResp.access_token)" }

# PATCH to change the SKU (asynchronous operation — Azure provisions the new tier)
$body = @{ sku = @{ name = $skuTarget; tier = "Standard" } } | ConvertTo-Json -Depth 3
Invoke-RestMethod -Method Patch -Uri "$serverUri`?api-version=$apiVersion" `
    -Headers $authHeader -Body $body -ContentType "application/json"

# Poll until provisioningState reaches Succeeded, Failed, or Canceled
do {
    Start-Sleep -Seconds 20
    $info  = Invoke-RestMethod -Method Get -Uri "$serverUri`?api-version=$apiVersion" -Headers $authHeader
    $state = $info.properties.provisioningState
    Write-Host "provisioningState: $state"
} while ($state -notin @("Succeeded", "Failed", "Canceled"))

$ok = ($state -eq "Succeeded")
Push-OutputBinding -Name Response -Value ([HttpResponseContext]@{
    StatusCode = if ($ok) { 200 } else { 500 }
    Body       = if ($ok) { "Scale para $skuTarget concluido." } else { "Scale para $skuTarget falhou: $state" }
})

In ADF, scale-down should be in the Finally block of the control flow (or connected to the success and failure branches of processing), ensuring the server is scaled down to the smaller tier regardless of the outcome.

DirectQuery Mode: When to Use and When to Avoid

DirectQuery mode in AAS (and Power BI) does not store data in memory; all queries are translated into SQL queries and sent directly to the data source. It eliminates processing but has serious trade-offs:

• Pros: Data is always current (no processing latency), no RAM consumption by the tabular model, suitable for tables with billions of rows that don't fit in memory.
• Cons: Query performance depends 100% on the data source. Complex DAX measures are difficult or impossible to optimize. Some DAX features do not work in DirectQuery. Each report page load generates multiple queries in the database.
• Recommendation: Use DirectQuery only for very large fact tables that change continuously. Combine with Import Mode for dimension tables (Composite Model). Never use pure DirectQuery across the entire solution without extensive benchmarking.

XMLA Endpoint and Power BI Premium: Best Practices

For Power BI Premium and Fabric, the XMLA Endpoint opens access to all enterprise capabilities that were previously exclusive to SSAS and AAS:

• Incremental Refresh with XMLA: Power BI has a native Incremental Refresh feature, but it has limitations. For full control, use the XMLA Endpoint with TMSL scripts directly; you can create and manage partitions exactly as in AAS.
• Deployment Pipelines via XMLA: Automate model deployment between workspaces (Dev → Staging → Production) via TMSL scripts with XMLA, integrating with your CI/CD pipeline in Azure DevOps or GitHub Actions.
• Backup/Restore: Back up models via XMLA to .abf files in Azure Storage, as a contingency for rolling back problematic changes.
• Third-Party Tools: Tabular Editor, DAX Studio, and VertiPaq Analyzer work natively with the Power BI Premium XMLA Endpoint; the same optimization flows you use in AAS apply directly.

Backup and Restore: Protection and Rollback in Minutes

AS supports backing up the model to an .abf (Analysis Services Backup File), which includes all in-memory data, metadata, and partitions. Restoring an .abf is, in many cases, much faster than reprocessing the model from scratch, especially for models with tens or hundreds of gigabytes.

To configure automatic backup in AAS, access the Azure portal, in the AAS server blade, go to Settings > Backup and point to an Azure Blob Storage account. With storage configured, you trigger backups via TMSL:

// Fazer backup do modelo para o Azure Blob configurado
{
  "backup": {
    "database": "MeuModeloAAS",
    "file": "MeuModeloAAS_20240115.abf",
    "allowOverwrite": true,
    "applyCompression": true
  }
}

// Restaurar o modelo a partir do backup
{
  "restore": {
    "database": "MeuModeloAAS",
    "file": "MeuModeloAAS_20240115.abf",
    "allowOverwrite": true,
    "readWriteMode": "readWrite"
  }
}

Scenarios where restore outperforms reprocessing:

• Deployment rollback: Before deploying structural changes to the model (adding columns, changing data types, altering relationships), perform a backup. If something goes wrong, restore in minutes instead of waiting hours for reprocessing.
• Seed new server: When creating a staging or DR AAS server, restore the production backup instead of reprocessing everything from the source. Restore via Azure Blob is much faster for models of 50GB+ than a full Process Full.
• Processing failure recovery: If processing corrupted the model or left partitions in an inconsistent state, restore the last good backup and reprocess only the affected partitions.

Secure Authentication for Data Sources: Service Account and OAuth

For production environments, never use personal or administrator credentials in the AS model's data source. The supported and documented alternatives are:

• SQL Service Account (SQL authentication): Dedicated SQL login with read-only permission (db_datareader). It is the simplest option, works at any compatibility level, and is explicitly documented by Microsoft for cloud data sources with SQL auth, impersonation must be Service Account.
• OAuth / Entra ID (Tabular 1400+, in-memory): For Azure SQL, Azure Synapse, Dynamics 365, and SharePoint List in 1400+ in-memory models, AAS supports OAuth credentials managed by the service itself (including automatic token renewal to avoid timeout in long refreshes). Configuration is done via Power Query in Visual Studio or SSMS → Edit Credentials, using an account with access to the source. For automation with Service Principal, consult the specific AAS OAuth credentials documentation, as the configuration flow is not a simple TMSL block.

Configuring SQL Service Account

Create a dedicated SQL login in Azure SQL with minimum read permission. In the AAS model, configure the data source with this login and set impersonation as Service Account:

-- In Azure SQL: create dedicated service login with minimum permission
CREATE LOGIN [aas_svc] WITH PASSWORD = 'senha-forte-aqui';

-- In the target database:
CREATE USER [aas_svc] FOR LOGIN [aas_svc];
ALTER ROLE db_datareader ADD MEMBER [aas_svc];

-- Check
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name = 'aas_svc';

In SSMS, connected to the AAS server: right-click on the database → Process → Edit Settings → Data Source Credentials tab → edit the data source and provide the service account login and password. AAS stores the encrypted credentials on the server, without exposing them in scripts or repositories.

Service Principal for AAS Operations Automation

The documented use of Service Principal in AAS is to automate operations on the server itself (refresh, scale, model deployment) via PowerShell, Azure Automation, and other orchestrators, not as a data source credential. The SP needs to be added to the server's administrator role or a database role with Process permission:

# Authenticate with Service Principal to execute Invoke-ASCmd (PS7 or PS5.1)
$appId    = $env:SP_APP_ID
$tenantId = $env:ENTRA_TENANT_ID
$secret   = ConvertTo-SecureString $env:SP_CLIENT_SECRET -AsPlainText -Force

# Using the SqlServer module with Service Principal
Invoke-ProcessTable `
    -Server  "asazure://brazilsouth.asazure.windows.net/meuservidor" `
    -Database "MeuModeloAAS" `
    -TableName "FatoVendas" `
    -RefreshType "Full" `
    -ServicePrincipal `
    -ApplicationId $appId `
    -TenantId $tenantId `
    -Credential (New-Object System.Management.Automation.PSCredential($appId, $secret))

Well folks, I hope you enjoyed this article and found it useful for deepening your knowledge of processing optimization in Azure Analysis Services, SSAS Tabular, and Power BI Premium.

We covered everything from VertiPaq's internal architecture to advanced partitioning strategies, aggregation tables, Scale-Out with read replicas, EncodingHint, segment management, automation with PowerShell and AMO, authentication best practices, and production monitoring.

There's a lot of content here, save this link, because it's the kind of thing you'll want to refer to multiple times throughout your journey.

Best regards and see you next time!