Menu Close

Advanced Dimensional Data Warehouse Design Techniques

In an earlier blog post, I walked you through the basics of dimensional data warehouse design by introducing you to dimension tables, fact tables and star schema design. For this blog post, I will introduce some of the more widely used advanced dimensional design techniques which I will describe in detail in the coming weeks.


Conformed Dimensions

Conformed dimensions are those dimensions which have been designed in such a way that the dimension can be used across many fact tables in different subject areas of the warehouse.


Slowly Changing Dimensions

Slowly changing dimensions are the second most commonly used advanced dimensional technique used in dimensional data warehouses. Slowly changing dimensions are used when you wish to capture the changing data within the dimension over time.


Hierarchical Dimensions

Hierarchical dimensions are those dimensions which have an associated hierarchy. A date (day, month, quarter, year) is an example of a hierarchical dimension and one that is often used by many data modelers and data warehouses.


Snowflake Dimension Tables

The removing all redundant dimensional attributes into separate dimension tables linked to the main dimensional table are snowflakes.


Multivalued Dimensions and Bridge Tables

Multivalued dimensions are those dimensions which you associate more than one value in the table.  Bridge tables are a design technique used to address this issue.


Degenerate Dimension Tables

Degenerate dimensions are not actually dimensions since they don’t exist as physical data structures. Degenerate dimensions are a part of the primary key of the fact table but have no context outside of this primary key.


Junk Dimension Tables

Junk dimensions are used to reduce the number of dimensions in the dimensional model and reduce the number of columns in the fact table.



Types of Fact Tables

The three basic fact table grains are the transactional, the periodic snapshot and the accumulating snapshot.

Transactional Fact tables are the most common dimensional fact modeling design. Transactional fact tables capture the measurement at its most atomic level dimensional level at the point-in-time of the transaction.

Periodic Snapshot Fact tables capture the state of the measures at a specified point-in-time. Periodic Snapshot Fact tables are used to quickly measure and review the performance of the measures over specified time intervals (week, month, quarter, year, etc.).

Accumulated Fact tables are used to show the activity of progress through a well-defined business process and are most often used to research the time between milestones.  These fact tables are updated as the business process unfolds and each milestone is completed.


Fact Table Joins

Because fact tables have unique dimensional characteristics to address the specific business measurements for which they were created, fact tables are like magnets sharing the same poles, there is no joining these two fact tables together.


Fact Table Surrogate Keys

There can arise a situation where there are duplicate fact records. Although dimensional data warehouse designers try to avoid this issue it can occur based on the business requirements.


Numeric Values as Attributes or Facts

Instances where some numeric values can fall into both a fact measurement field and dimension attribute field.


Period-to-Date Facts

Period-to-Date facts should be avoided as this would violate the grain of the fact table since the period-to-date and the daily transaction data are at different time grains.

Posted in Blog

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *