Dimensional Data Warehouse / Star Schema

The Dimensional Data Warehouse is a database that supports Business Intelligence operations by organizing data into Dimensions and Facts, also known as a Star Schema. The “Dimensional Data Model” was popularized by Ralph Kimball and others in the 1980s to support business needs for analytical operations. This approach has stood the test of time and is the recommended way to organize data for business intelligence query and analysis. This is the structure that supports: drill down, roll up, flexible filtering, and "Slice and Dice".

The Dimensional Warehouse tends to be composed of the following zones or similar zones with different names:

• Ingest: zone where data is input to the database. It is best practice to limit database inputs to the Ingest Zone.
• Core: zone where subject content is stored. This is central focus of the database. It is best practice to isolate the Core Zone from external processes and databases.
• Expose: zone where data is made available outside of the database. Business Intelligence and Data Visualization tools typically access this zone.
• Process: zone where database processes are tracked and controlled. It is best practice to use the same Process Zone schema across databases.
• Archive: zone where history data is stored in Raw Immutable form. This means that data is stored in the Ingest form and not is not altered which makes for an effective audit trail.
• Metadata: zone where data describing database content and structure are stored. Glossaries and Data Lineage are examples of data managed here. It is best practice to use the same Metadata Zone schema across databases or to share a Metadata repository across the enterprise.
• Notify: zone where logs of events are stored. It is best practice to use the same Process Zone schema across databases. Notifications may be sent to a centralized Notifications System or Database.

The Dimensional Warehouse Core is organized around these types of tables:

• Dimension: contains descriptive information often used as labels and filter criteria.
• Fact: contains measurements / numeric information.
• Bridge: an associative table that implements a many to many relationship between fact and dimension tables.
• Guide: tables that enable efficient and effective data access such as: hierarchy navigation, supertype / subtype and use conditions.
• Reference: tables that contains static lookup data such as: calendards, currencies, countries and transaction codes.