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 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 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.