Which of the following statement is true with respect to aggregation?
-
Aggregate data should be stored in the original fact and dimension tables that contain the detail data
-
Aggregate data has to be stored in separate fact tables but the same dimension tables can be used.
-
Each level of aggregate data should be stored in separate set of fact and dimension tables.
-
All levels of aggregation should be stored in a set of fact and dimension tables and the detailed data should be stored in separate fact and dimension table.
In data warehouse design, each aggregation level should have its own set of fact and dimension tables (Option C). This is because aggregated data has different granularity than detailed data. For example, monthly sales aggregates have different dimensions and grain than daily transactions. Option A is wrong because mixing aggregate and detail data degrades query performance. Option B is incorrect because aggregate data requires appropriate dimension tables at the same grain. Option D is inefficient - storing all aggregation levels together doesn't optimize for different query patterns.