DWH Tutorial – Part 2


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

Advertisements

Comment please...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s