The Dimensional Data Model
The Dimensional Data Model is the data modeling methodology most commonly used in data warehousing systems. The Dimensional Data Model differs substantially from the Third Normal Form, more commonly used for transactional systems. As you can imagine, the same data would then be stored much differently in a dimensional model than in a 3NF model.
The Dimensional Data Model consists of Fact and Dimension tables. The Fact tables store the numerical values of the business unit and contain numerical or additive measures of the business like Gross Sales, Gross Units. The Fact table also contains columns which link to the Dimension table. The Dimension table stores the descriptive information about the dimension and some times these are joined to other dimension tables to define the hierarchy of a dimension like Market (Geographical information) or Time information.
To understand Dimensional Data Modeling, we'll define some of the terms commonly used. Pay attention here as you may notice a definite similarity here with the terms used to describe data in an Essbase database
- Dimension: A category of information, for example, the
Timedimension. TheTimedimension would contain data relative to time periods such as days or months or years. - Attribute: A distinct level within a dimension. For example, Year is an attribute in the
Timedimension. - Hierarchy: The specification of levels that represents relationship between different attributes within a dimension. For example, one possible hierarchy in the
Timedimension is Year | Quarter | Month | Day.
When the data in the data warehouse is modelled using the Dimension Data Model method instead of being organized like the 3NF method, which is in neat rows and columns with primary keys to identify everything, it usually follows the line of the dimensions that are included as necessary components of your data. The resultant structure of the dimensional data method resembles more of a multidimensional cube than two dimensional rows and columns.