How aggregate providers can speed query time

Aggregating data at query time can be slow because the system must fetch data from the datastore or an external database (DirectQuery). Aggregate providers allow pre-aggregation, so queries retrieve results from optimized structures instead of recalculating from scratch. Aggregate providers define the strategy for aggregating measures in a cube. They determine whether data is aggregated on demand or pre-aggregated during data loading. This choice directly impacts query speed, memory usage and transaction speed.

What are the types of aggregate providers and their impact?

Just-in-time (JIT) provider

Behavior: Aggregates data at query time.
Memory usage for storage: None.
Memory usage at query time: Higher than other aggregate providers
Query speed: Slower.
Use case: Small datasets or infrequent queries.
Impact: No pre-aggregation; queries depend on datastore scans.

Leaf provider

Behavior: Pre-aggregates data during loading at leaf level.
Memory usage for storage: Moderate.
Query speed: Fast.
Memory usage at query time: Moderate.
Use case: Balanced performance for most cubes.
Impact: Queries retrieve pre-aggregated values using point indexes, reducing computation time.

Bitmap provider

A bitmap aggregate provider pre-aggregates data using a leaf provider. They use a bitmap index for faster data retrieval.

Behavior: Pre-aggregates data and builds bitmap indexes.
Memory usage for storage: Higher than a leaf provider.
Query speed: Fastest.
Memory usage at query time: Lower than JIT or leaf.
Use case: High query concurrency and large datasets.
Impact: Queries use bitmap submasks for instant retrieval of aggregated values.