Dimensional Modeling 2

Hierarchy

represents natural, multi-level, logical relationships between the attributes of a dimension. They define the pathways for users to navigate from a summary level to a detailed level and back.

Roll-Up

Aggregating data to a higher level of a hierarchy.

Drill-Down

Navigating to a lower, more detailed level of a hierarchy.

Balanced Hierarchy

has a uniform structure. Hierarchy has a fixed number of levels and every branch reaches the same level.

Unbalanced Hierarchy

has branches that can descend to different levels (depths) which allows for real world scenarios where not all branches are uniformed.

Balanced Ragged Hierarchy

has branches that descend to same levels, but some branches may skip some in-between levels. Helps with handling incomplete or irregular data.

Network Hierarchy

allows nodes to have multiple parent nodes which offers great flexibility for representing relationships.

Slowly Changing Dimensions

techniques used to address dimension attribute changes over time

SCD Type 1: Overwrite Old Value

Find the dimension record and simply update the attribute with the new value. History is lost and is meant to use when correcting data entry errors.

SCD Type 2: Add New Row

preserve the original record by "expiring" it, and create a new, active record with a new surrogate key. Preserves full history.

SCD Type 3: Maintain Linking Attribute

When an attribute of analytical importance changes, Dimension maintains linking attribute which has same value for old and new record, Preserve the original record by "expiring" it, and create a new, active record with a new surrogate key.

Slice

Filtering the data to look at a single value of one dimension.

Dice

Filtering on two or more dimensions to look at a specific intersection of data