Mastering Data Warehouse Aggregates: Solutions for Star Schema Performance

9.2. Aggregates and Security

Security requirements may dictate that not all users are permitted access to all data in a fact table. Dimensionally driven security enforcement is often used to constrain access to fact table detail. When the aggregate navigator is properly considered in the implementation of such a scheme, dimensional aggregates will continue to preserve restrictions. Restricted access to detailed data is often accompanied by unrestricted access to summary data. In this case, an aggregate can be implemented as derived schema to present summary data. This aggregate must be designed so that it does not expose performance at the detailed level.

9.2.1. Dimensionally Driven Security and Aggregates

Often, information contained in a fact table is provided on a need-to-know basis. An individual user may be authorized to access only a subset of the facts. When such a limitation can be expressed dimensionally, the warehouse architects develop a plan that limits access based on data in the star schema. Applied to transaction systems, this technique is often referred to as row-level security. Applied to a dimensional schema, it can be described as dimensionally driven security.

A dimensionally driven security scheme automatically constrains queries against a fact table for specific dimensional values associated with the user. This enforcement is most often applied directly to fact table keys. For example, business rules may dictate that a salesperson is allowed to access only ...