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.
Star Schema:
In the star schema design, a single object (the fact table) sits in the middle and is radially connected
to other surrounding objects (dimension lookup tables) like a star. A star schema can be simple or
complex. A simple star consists of one fact table; a complex star can have more than one fact table.
Snowflake Schema: The snowflake schema is an extension of the star
schema, where each point of the star explodes into more points. The main
advantage of the snowflake schema is the improvement in query performance
due to minimized disk storage requirements and joining smaller lookup tables.
The main disadvantage of the snowflake schema is the additional
maintenance efforts needed due to the increase number of lookup tables.
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.