Dynamic Aggregation

Dynamic aggregation is an OLAP Services feature that allows you to change the level of report aggregation on the fly, while you are reviewing the report results. This feature allows metric values to be aggregated at different levels based on the attributes included on the report without having to re-execute the report against the data warehouse. Dynamic aggregation occurs when you move attributes and metrics between the Report Objects pane and the report grid.

To better understand dynamic aggregation, consider a report that includes two attributes, Region and Category, and two metrics, Revenue and Units Sold. A subset of the report results are shown below.

To remove an attribute from the report without using dynamic aggregation, right-clicking it (the Category attribute is used for this example) and select Remove from Report. The data for Revenue and Units Sold is aggregated to the Region level, resulting in the report below.

Notice that the Category attribute is not in the Report Objects pane, and the report remains a Standard report as indicated in the bottom right corner.

When you remove an attribute or metric from the report, a message is displayed that indicates the manipulation causes the report to be re-executed. This is because standard metric aggregation needs to be re-executed against the data warehouse, and therefore requires new report SQL to be generated.

Now consider the same report, but this time you use dynamic aggregation to return a different view of the data. Instead of removing Category from the report, Category is moved to the Report Objects pane, as shown in the image below.

Although the report data is exactly the same as the previous report with standard aggregation, dynamic aggregation has the following differences:

No re-execution and no new SQL required: Dynamic aggregation does not require re-execution against the data warehouse. Aggregating data within the report can improve the performance of your system by reducing the load on the data warehouse. Dynamic aggregation also improves performance by returning the new report results as soon as the attribute or metric is moved to Report Objects, instead of having to wait for the results to be returned from the data warehouse.
Attributes remain in Report Objects: Category is now in the Report Objects pane and is no longer displayed with bold formatting. Since Category is part of the report definition, it can be used to define the view of the report. For example, you can build a view filter to return Revenue only for the electronics category. A standard report would have to use the report filter to filter on an attribute not on the report, which would also require re-execution against the data warehouse.

With attributes available in the Report Objects pane, you can easily add them back onto the report grid.
OLAP report: The report is marked as OLAP (as indicated in the bottom right corner of the Report Editor) because of the dynamic aggregation performed.

This section discusses the following aspects of dynamic aggregation: