This topic contains a general discussion about creating
and managing a security association table for use with dimensional
target data. BI row-level security does not require that target data
conform to a particular structure. The description in this topic is
for dimensional data, because that is a frequently used structure
for query and reporting.
A security associations
table is usually created as a new object by traversing an existing
sparse table and filling in the indirect relationships to create a
fully articulated (or robust) version of the table. If you do not
have an existing sparse table, then you must create that object first.
Note: If you want to enhance an
existing sparse table rather than creating a new table, you should
first review current uses of the sparse table to determine whether
the additional rows will negatively affect those uses.
In most cases it is
helpful to have an index on the column in the security associations
table that is used for filtering. In some cases, factors such as the
size of the security associations table or query optimization features
in a particular data source might negate the need for this index.
The security associations
table must be maintained as security relationships change. This maintenance
should be on a schedule that is appropriate for your environment.
Typically, this maintenance is accomplished by a batch process (such
as a nightly ETL process against the existing tables). In some cases,
updates might be entered directly by an administrator.