Aggregations & Incremental Refresh for Big Data Analytics

When an enterprise data warehouse contains hundreds of millions or billions of rows (e.g., retail point-of-sale transactions, web traffic logs, IoT telemetry), loading the entire dataset via standard Import mode is technically or commercially impossible.

Advanced BI architects solve the Big Data challenge using two enterprise techniques:

1. 1
   Incremental Refresh (Pulls only newly modified partitions).
2. 2
   User-Defined Aggregations (Pre-computes summary tables in memory).

1. Incremental Refresh: Partitioning Big Data

Instead of re-importing 10 years of historical data every single morning, Incremental Refresh divides the dataset into historical partitions:

Implementing Incremental Refresh:

1. 1
   In Power Query, create two reserved parameters:

• RangeStart (Data type: Date/Time)
• RangeEnd (Data type: Date/Time)

1. 1
   Filter your Fact table's date column using these parameters:

1. 1
   Ensure Query Folding is active!
2. 2
   In Power BI Desktop Model View, right-click the Fact table $\to$ Incremental refresh.
3. 3
   Define the policy:

• Archive data starting: 5 Years.
• Incrementally refresh data starting: 7 Days.
• Check Detect data changes (uses a timestamp column like LastModifiedDate to refresh only changed rows).

2. User-Defined Aggregations (Composite Performance)

User-defined aggregations let you maintain a hidden in-memory summary table alongside a massive DirectQuery fact table:

Managing Aggregations:

1. 1
   In Model View, right-click your aggregate table $\to$ Manage aggregations.
2. 2
   Map aggregate columns to the detail table (e.g., Agg_Sales[TotalSales] maps to SUM of Fact_Sales[SalesAmount]).
3. 3
   Hide the aggregate table from end-users so they interact only with the clean dimensional model.