An aggregation table stores precomputed results at a coarser grain than a detail fact table. After you map its columns to the detail model, Datafor can use the aggregation table automatically for compatible report queries. Queries that require data outside the mapped grain can continue to use the detail tables.

Aggregation tables can reduce the amount of data scanned for frequently used totals. Datafor maps and queries an existing physical table; it does not create, load, or refresh that table in the datasource.

When to use an aggregation table

Consider an aggregation table when:

• A fact table contains a large number of rows.
• Users repeatedly analyze the same Dimensions or time grain.
• The required Measures can be stored with well-defined aggregations such as Sum, Count, Min, or Max.
• The physical aggregation can be refreshed whenever the detail data changes.

For example, a sales fact table can be summarized by customer and day with precomputed sales, cost, unit, and row-count columns.

Before you begin

Enable aggregation tables on the server

The query engine ships with aggregation tables turned off. While either UseAggregates (mondrian.rolap.aggregates.Use) or ReadAggregates (mondrian.rolap.aggregates.Read) is off, the engine ignores every aggregation mapping and always queries the detail tables.

1. As an administrator, open Settings › Data › Query engine (see Query Engine), turn on UseAggregates and ReadAggregates, and click Save.
2. Clear the model cache or restart the Datafor server. Models that are already loaded keep ignoring aggregation tables until then.

Alternatively, edit the files on the server: in both bi-server/pentaho-solutions/system/datafor/mondrian.properties and bi-server/pentaho-solutions/system/mondrian/mondrian.properties, set the lines below, then restart the Datafor server. Both files are read only at startup.

mondrian.rolap.aggregates.Use=true
mondrian.rolap.aggregates.Read=true

The setting applies to every Analysis Model on the server.

Prepare the aggregation table

Prepare the physical aggregation table in the datasource, then confirm:

• Its grain is clear. Each row must represent one unique combination of the grouping columns.
• Its measure columns use the same definitions as the Measures in the detail model.
• A row-count column, if present, contains the number of contributing detail rows.
• The detail tables and aggregation table are added to the same Analysis Model.
• The aggregation table has no regular relationships on the model canvas. Manage aggregations is unavailable while the selected table has a relationship.

Group-by columns can map to keys on any non-aggregation table in the model. Summarized columns must map to a detail Measure that uses the same source column and aggregation type.

Configure the aggregation table

1. Open the Analysis Model and add the physical aggregation table to the canvas.
2. Open the table's More menu and select Manage aggregations.

3. Configure the required columns in the mapping dialog.

Field	What to select
Aggregation column	The column stored in the aggregation table.
Summarization	How the value was produced from detail data.
Detail table	The non-aggregation table that supplies the corresponding key or Measure.
Detail column	The matching detail column. This is not required for Count table rows.

Use these summarization options:

Option	Use it for
Group by	A column that defines the aggregation table's grain, such as customer, product, or date key.
Sum	A stored sum of a detail Measure.
Count	A stored count that matches a Count Measure.
Min / Max	A stored minimum or maximum that matches the detail Measure.
Avg	A stored average calculated with the same definition as the detail Measure.
Distinct count	A stored distinct count calculated with the same definition and grain as the detail Measure.
Count table rows	A stored count of contributing detail rows. Select a detail table and leave Detail column empty.

You can leave a column unmapped when it is not needed for aggregation-table routing. A partially completed row must be finished or cleared before you continue.

Example mapping

The following example maps agg_l_03_sales_fact_1997 to sales_fact_1997:

Aggregation column	Summarization	Detail table	Detail column
customer_id	Group by	sales_fact_1997	customer_id
fact_count	Count table rows	sales_fact_1997	—
store_cost	Sum	sales_fact_1997	store_cost
store_sales	Sum	sales_fact_1997	store_sales
time_id	Group by	sales_fact_1997	time_id
unit_sales	Sum	sales_fact_1997	unit_sales

4. Select Ok. The table card displays an Aggregate badge and a summary beside each mapped column.
5. Review the Analysis Model tree, then save the model.

The aggregation table does not expose its own Dimensions or Measure Group after the mapping is applied. Reports continue to use the Dimensions and Measures from the detail model; the query engine chooses the physical aggregation table when the query is compatible.

Validate the configuration in a report

Always validate both query routing and business results before relying on an aggregation table.

1. Create a report and add a Pivot table.
2. Select the Analysis Model that contains the aggregation mapping.
3. Add only Dimensions represented by the mapped Group by columns.
4. Add one or more mapped Measures.
5. Confirm that the result matches an expected total or a query against the detail data.

To confirm that the query used the aggregation table, select the Pivot table in edit mode, open More (…), and select Execution cost. If the SQL references the physical aggregation table, that query used the mapping. The menu is not available in read-only preview mode.

Understand query matching and fallback

The query engine can use the aggregation table when the requested Dimensions, filters, and Measures can be answered from its mapped grain and columns. It checks the conditions in this order, and the first one that fails sends the query to the detail tables. When several aggregation tables qualify, it reads the smallest.

If a report adds a Dimension, filter, or Measure that the aggregation table cannot answer, the engine can fall back to the detail tables. This is expected and preserves query correctness. For example, an aggregation grouped only by customer and time cannot answer a query grouped by store unless store is also represented in its mappings.

Do not assume that every query against the Analysis Model will use the aggregation table. Check representative report queries after changing the model or the physical aggregation.

Maintain aggregation tables

Datafor does not refresh the physical table. Reload it whenever the detail data changes, and update the mapping when a source column or table alias changes.

Troubleshooting

Manage aggregations is unavailable

The selected table has one or more regular relationships. Remove relationships that do not belong on the aggregation table, then open the table menu again.

The mapping dialog cannot be submitted

Complete or clear every partially configured row. All mapping types require a summarization and detail table. Every type except Count table rows also requires a detail column.

A report uses the detail table

Check that:

• The required Dimensions are covered by Group by mappings.
• Each summarized column maps to the correct detail table, column, and aggregation type.
• Report filters do not require a column outside the aggregation grain.
• The model was saved after the mapping changed.

If the mapping appears correct, ask an administrator to confirm that mondrian.rolap.aggregates.Use and mondrian.rolap.aggregates.Read are both true and that the server was restarted afterwards (see Before you begin).

Aggregated results differ from detail results

Check the physical table's refresh time, grouping keys, Measure definitions, filters, null handling, distinct-count logic, and row-count column. Rebuild or refresh the physical aggregation, then compare the same grain against the detail data again.

Related topics

• Working with Tables and the Canvas
• Establishing Table Relationships
• Measures and Calculated Measures
• Model Diagnostics