Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

9.5. Summary

The data warehouse may include an archive process by which historic data is removed from database tables and placed in offline storage. To preserve the conformance of aggregates with the base schema, this process must remove aggregate data as well. Aggregations within the time dimension that do not fit neatly into archive periods will confound this process, and should be avoided. Aggregate data need not be archived with the base schema data; instead it may be purged. Aggregate data may also be moved into an on-line summarization of the archived detail; this summary must stand separately from aggregate tables that represent data currently on-line.

Dimensionally driven security enforcement is used to constrain access to fact table detail based on a user's identity. When the aggregate navigator is properly considered in the implementation of such a scheme, dimensional aggregates will continue to preserve restrictions. The requirement for restricted access to detailed data is often accompanied by one of unrestricted access to summary data. An aggregate, implemented as derived schema, can meet this requirement. This aggregate must be designed so that it does not expose performance at the detailed level.

While aggregates are almost always derived from other warehouse tables, not all meet our definition of invisible aggregates. Although not serviced by an aggregate navigator, three types of derived tables provide increased accessibility and performance by simplifying the ...