Data Warehousing >
Concepts >
Dimensional Data Model
Dimensional data model is most often used in data warehousing systems.
This is different from the 3rd normal form, commonly used for transactional
(OLTP) type systems. As you can imagine, the same data would then be stored
differently in a dimensional model than in a 3rd normal form model.
To understand dimensional data modeling, let's define some of the terms commonly used in this type of modeling:
Dimension: A category of information. For example, the time dimension.
Attribute: A unique level within a dimension. For example, Month is an attribute in the Time
Dimension.
Hierarchy: The specification of levels that represents relationship between different
attributes within a dimension. For example, one possible hierarchy in the Time dimension is
Year → Quarter → Month → Day.
Fact Table: A fact table is a table that contains the measures of interest. For example, sales
amount would be such a measure. This measure is stored in the fact table with the appropriate
granularity. For example, it can be sales amount by store by day. In this case, the fact table
would contain three columns: A date column, a store column, and a sales amount column.
Lookup Table: The lookup table provides the detailed information about the attributes. For example,
the lookup table for the Quarter attribute would include a list of all of the quarters available in
the data warehouse. Each row (each quarter) may have several fields, one for the unique ID that identifies
the quarter, and one or more additional fields that specifies how that particular quarter is represented
on a report (for example, first quarter of 2001 may be represented as "Q1 2001" or "2001 Q1").
A dimensional model includes fact tables and lookup tables. Fact tables
connect to one or more lookup tables, but fact tables do not have direct
relationships to one another. Dimensions and hierarchies are represented
by lookup tables. Attributes are the non-key columns in the lookup tables.
In designing data models for data warehouses / data marts, the most commonly used schema types
are Star Schema and Snowflake Schema.
Whether one uses a star or a snowflake largely depends on personal
preference and business needs. Personally, I am partial to snowflakes,
when there is a business case to analyze the information at that
particular level.