Understanding Incremental Refresh

Incremental refresh is a powerful feature in SQL Server Analysis Services (SSAS) and Azure Analysis Services (AAS) that allows you to refresh only the new or changed data in your models, rather than reprocessing the entire dataset. This significantly reduces refresh times, lowers resource consumption, and ensures your data remains up-to-date with minimal latency.

This technique is particularly beneficial for large fact tables where new data is constantly being added. By implementing incremental refresh, you can dramatically improve the efficiency of your data model updates.

Key Concepts

  • Partitioning: Incremental refresh relies on table partitioning. Each partition represents a specific period of data (e.g., a day, week, or month).
  • Range Columns: A date or datetime column is used to define the ranges for partitioning. This column should represent the creation or modification date of the data.
  • Full/Archive Partitions: These partitions contain historical data that is typically not refreshed frequently or at all.
  • Intelligent/Rolling Partitions: These partitions contain recent data that is refreshed periodically. The number and duration of these partitions are configurable.
  • Staging Table: A separate table in your data source that holds the new or changed data to be merged into the Analysis Services model.

Steps to Implement Incremental Refresh

1. Prepare Your Data Source

Ensure your data source has a date or datetime column that can be used for partitioning. This column will be used to filter and segment your data.

2. Configure Table Partitioning in SSAS/AAS

In SQL Server Data Tools (SSDT) or Visual Studio, navigate to your tabular model. Right-click on the table you want to configure for incremental refresh and select "Manage Partitions".

You will need to set up your partitioning strategy:

  • Define a range column.
  • Create partitions for historical (full) data.
  • Create partitions for recent (rolling) data.

3. Define Refresh Policies

Once partitions are set up, you need to define the refresh policy. This policy specifies how the rolling partitions should be managed:

  • Maximum number of full partitions: How many historical partitions to keep.
  • Maximum age of rolling partitions: How old the data in rolling partitions can be before it's moved to a full partition.
  • Periodic full partitions: Optionally, you can have a periodic full partition that covers a longer duration.

4. Implement the Refresh Logic

The actual refresh process typically involves:

  • Identifying new or changed data in your source system since the last refresh.
  • Using Power Query (M language) to select only the relevant data for the current refresh cycle.
  • Using a tool like Tabular Editor or custom scripts to automate the partition management and data refresh process.

Example M Query Snippet for Filtering:


let
    // Get the start and end date for the current refresh cycle
    // This logic would typically be dynamic based on last refresh date
    StartDate = #datetime(2023, 10, 26, 0, 0, 0),
    EndDate = DateTime.LocalNow(),

    // Source data query
    Source = Sql.Database("your_server", "your_database", [Query="SELECT * FROM YourFactTable WHERE YourDateColumn >= '" & Date.ToText(StartDate, "yyyy-MM-dd") & "' AND YourDateColumn < '" & Date.ToText(EndDate, "yyyy-MM-dd") & "'"])
in
    Source

5. Automate and Schedule

Use tools like SQL Server Agent, Azure Data Factory, or other scheduling mechanisms to automate the execution of your refresh processes. This ensures your data is consistently updated.

Benefits of Incremental Refresh

  • Reduced Refresh Times: Significantly faster data updates.
  • Lower Resource Usage: Less CPU, memory, and network bandwidth required.
  • Improved Data Freshness: Data can be updated more frequently, closer to real-time.
  • Enhanced Scalability: Handles massive datasets more effectively.

Considerations

  • Requires careful planning and configuration of partitions.
  • The data source must support efficient filtering based on date/time.
  • Changes to historical data require manual intervention or a more complex strategy.
Get Started with Incremental Refresh