Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

Chapter 9. Related Topics

The previous chapters in this book have organized the discussion of aggregates around fundamental topics: selection, design, use, construction, and the project. This chapter collects together four remaining topics: archives, security, derived schemas, and aggregates that are deployed before detail.

Once added to the data warehouse, aggregate tables are an integral part of its lifecycle. One aspect of the lifecycle that has only been touched upon until now is the archive process, which governs the removal of historic data from the data warehouse. This chapter outlines requirements for the archival process that are introduced by aggregates, and also shows how they can enhance the archival process by summarizing off-line data.

Row-level security is often applied to a star schema by conditionally filtering on specific dimension values based on who is executing a query. This chapter looks at how the introduction of aggregates affects row-based security, and provides guidelines to ensure that aggregate navigation will not disrupt security enforcement. We will also see that aggregates can be deployed as supplemental base schema tables, providing unrestricted access to summarized data.

The aggregates discussed through most of this book have served as invisible summarizations of base schema tables. But not all aggregates are invisible. Next, this chapter looks at three types of derived schemas—merged fact tables, sliced fact tables, and pivoted fact tables—that ...