Data Warehouse: Facts and Dimensions

• Dimensional design concerns stars and cubes.

Modeling the Data Warehouse

• Analysis of a business process is supported by modeling.
• Measurement and context form two simple concepts.
• Measurements are called "Facts".
• Context descriptors are called "Dimensions."

Dimensional Design Process

• Business processes are typically supported by an operational system like billing/purchasing.
• The grain should be declared.
• Focus on identifying dimensions and facts.

Identifying Facts & Dimensions

• The word "by" is almost always followed by a dimension.
• The word "for" is a good indicator of a dimension, where the next word is likely an instance value of said dimension.
• Facts are usually numeric in value.

Facts & Dimensions in Reports

• Facts and dimensions can be recognized, by how they would be used, if stored in a database and shown on a report.
• Dimensions serve as "filters" or "query predicates".
• Filters imply dimensions, such as region, month and year.
• Dimensions specify groupings or "break levels" to identify levels of sub-totals.
• Facts can be found in queries or reports based on their use.
• Elements aggregated, summarized, or subtotaled are facts.

Grouping Dimensions and Facts

• Dimensional designs organize facts and dimensions for storage in a database.
• Dimensions can share relationships independent of facts.
• Dimensions may be grouped together.
• Facts available at the same level of detail are grouped together.
• 1..n means that "This is very important"

Fact Table

• It lies at the core of a star schema
• It acts as the engine for business process measurement
• The facts are accompanied by foreign keys.
• Fact table includes surrogate keys referring to associated dimension tables.

Querying a Fact Table

• Dimension values provide context.
• Dimensions specify the scope of SUM() aggregation.
• The fact is aggregated.
• Each row summarizes order lines in the fact table.

Additive & Nonadditive Facts

• A fact table stores facts at a specific level of detail.
• Facts can be expressed at various levels of summarization.
• Facts can be combined with dimensions to answer business questions.
• Some facts are fully additive and may be summed across dimensions.
• Not all measurements exhibit additivity.
• Rates or percentages are not additive.

Degenerate Dimensions

• Sometimes, all the dimensions cannot be sorted into a neat set of tables.
• In such cases, one or more dimensions may be in the fact table.
• A dimension column, when in the fact table, becomes a dimensions.
• The dimension's values can filter, control aggregation, order data, and define master-detail relationships.

Slowly Changing Dimensions (SCD)

• Dimension tables get their data from operational systems.
• In dimensional data warehouses or stand-alone data marts, it is gotten directly from the operational system.
• Dimensions may change in the operational source.
• It is crucial to identify how changes in source data transform into dimension tables.
• The phenomenon is referred to as slowly changing dimensions (SCD).

Types of SCD’s

• Type 0: Dimension values never change and nothing needs to be done.
• Type 1: The dimension is overwritten with the new value to preserve the history in the star schema.
• Type 2: A new record/row is inserted into the dimension table.
• All previously existing records are kept.
• Type 3: The affected field is updated with the new data value (overwrite), and a new column is added containing the old value.

Dimension tables with Slow change rules

• For documentation purposes: To document the slow changes rules for a dimension table is a good practice.
• For each dimension attribute, choose and document the appropriate slow change response.
• If uncertain, the type 2 response is the safest.
• When a source system captures the reason for a change, a single attribute may drive either type of response.

Cardinality of a Star Schema

• A common relationship cardinality is one-to-many or its inverse, many-to-one.
• The "one" side is always a dimension table.
• The "many" side is always a fact table.
• A retention period, for example, is 5 Years.

Storage Sizing of a Star Schema

• Relationships have a cardinality of one-to-many or its inverse many-to-one.
  • "One" side: Dimension table.
  • "Many" side: fact table.
• Retention Period Example is 5 Years.

Summary

• Dimensional modeling is a design approach optimized for analytic systems.
• A dimensional model captures how a process is measured.
• Data elements that represent measurements are called facts.
• Data elements that provide context for measurements are called dimensions.
• Dimensions and Facts are grouped into dimension tables and fact tables.
• Implemented in a relational database, the design is called a star schema.
• Dimension tables in a star schema use surrogate keys in order to enable the analytic system to respond to changes in the operational data
• The granular facts can be queried at various levels of detail.
• Exploring the details within a dimension is called browsing.