Channels

- For Consultants
- For Developers
- For Managers
- For Professors
- For Researchers
- For Students

Concepts of Dimensional Modeling

Dimensional modeling is an approach used by many data warehouse designers. A dimensional model is the underlying data model used by many commercial OLAP products. In this model, all data is contained in either the Fact Table and in a Dimension Table.

The Fact table contains the measurements or metrics or facts associated with a business domain or processes. If you are examining a Sales business process, then a relevant measurem associated with this business process such as "daily sales" is captured in the fact table. In addition to the measures, the only other fields in a Fact table are foreign keys linking to the dimension tables.

The Dimension tables provide the context for understanding the facts. You can think of the context of a measure as the relevant characteristics such as who, what, where, when, how of a measure (subject). In your business process Sales, the characteristics of the 'daily sales' measurem can be a Location (Where), Time (When), and Product Sold (What).

Dimension Attributes are the various columns in a dimension table. In the Location dimension, the attributes can be Location Code, State, Country, Zip code. Generally the Dimension Attributes are used in report labels, and query constraints such as where Country='USA'. The dimension attributes also contain one or more hierarchical relationships.

Before designing a data warehouse, one must decide upon the subject(s). If one wants to build a Sales Data Mart containing daily sales facts across multiple store locations, across time and across products then the relevant dimensions are:

  • Location
  • Time
  • Product

    Each dimension table contains data for only that dimension. In the above example, all store location information is put into one single table called Location.

    All Dimension tables contain a key column called the dimension key. In this example, DimID is the Dimension Identifier. This is the unique key into the Location dimension table.

    The actual data in the Location Table may look like:

    Location Dimension Table Example
    DimID LocCode Name StateName CountryName
    1001            IL01 Chicago Loop Illinois USA
    1002            IL02 Arlington Hts Illinois USA
    1003 NY01 Brooklyn New York USA
    1004 TO01 Toronto Ontario Canada
    1005 MX01 Mexico City Distrito Federal Mexico

    Some of the information is repeated in the above dimension table. For example, the StateName and CountryName are repeated in each row. This is a minor waste of storage space and a violation of normalization principles for transaction processing. In decision support data base design this approach improves query performance.