My first Publication Agile-Data-Warehouse-Design-eBook | Page 307

288
Appendix B
Dimension Table Types
C ODE M EANING /U SAGE
C HAPTERS
[CV] Current Value. Table contains only current value dimensional attributes.
Also known as a type 1 slowly changing dimension. 4, 5, 6
[HV] Historic Value. Table contains at least one historical value dimensional
attribute. Also known as a type 2 slowly changing dimension. 4, 5, 6
[RP] Role-Playing. Dimension is used to play multiple roles; for example,
Salesperson and Manager are both roles of the Employee
[RP]dimension. Calendar[RP] is the most common role-playing dimen-
sion. 4
[RU] Rollup. Dimension is derived from a more granular dimension. For example,
Month[RU] is a rollup of the Calendar dimension containing conformed
dimensional attributes Month, Quarter, and Year. 4
[SD] Swappable Dimension. Part of a set of dimensions with a common surro-
gate key that can be used in place of each other. Swappable dimensions are
often used to provide subsets of a large dimension population for efficiency;
for example, Business Customer is a swappable subset of Customer.
Swappable dimensions can also be used to provide alternative historical
views and national language support. 6
[ML] Multi-Level. A dimension containing additional members representing
higher levels in the dimension’s hierarchy. Used when a fact table can be
attached to a dimension at different levels. For example, sales transactions
can be assigned to an individual Employee or a Team/Branch, and web
advertisements can be for a specific product or a product category. 6
[HM] Hierarchy Map. A table used to resolve a recursive relationship. Represents
a variable-depth hierarchy. For example, Company Structure[CV,HM] is
a current value hierarchy map (does not track hierarchy history). 6
[MV] Multi-Valued. A bridge table used to resolve a many-to-many relationship
between a fact table and a multi-valued dimension.
Or
A hierarchy map [HM] that contains child members with multiple direct
parents. For example, Reporting Structure [MV,HM] is a hierarchy
map that connects employees to more than one direct manager.
MV tables often contain a weighting factor that allows facts to be allocated
across the multiple values at query time. 6, 9
[PD] Pivoted Dimension. A dimension that represents multiple row values as a
set of column (bit) flags—used to simplify combination selection. Often built
by pivoting a multi-valued bridge table or a fact table. 9
{Source} Data source. Default source system table or filename. 5