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