| The design principles of the dimensional model, which | | | | hand, maintaining aggregate facts, limits the analysis |
| is commonly used in data warehousing, are described | | | | (or drill down) capability on certain dimensions. The |
| in this article series. Dimensional models capture | | | | model in the figure (see resource), captures an |
| business performance measurements, which are used | | | | event: the sale of a product at a given time and all |
| to support decision making. Dimensional model The | | | | (or most) related to the sales event dimensions. This |
| descriptive simplicity and high performance in query | | | | fact table type, is called a transaction fact table. |
| execution, are characteristics which have contributed | | | | Dimension tables Dimension tables describe the |
| to the increased use of the dimensional model in data | | | | dimensions of a measurement on a business process. |
| warehouse infrastructures. The symmetry and | | | | The features of each dimension should be as rich and |
| descriptive simplicity can be seen at the conceptual | | | | flexibly described as possible (with many descriptive |
| model (see resource link) which relates to retail sales | | | | fields on the dimension table). Attribute names of the |
| monitoring (data warehousing technology has been | | | | dimension tables should be sufficiently descriptive, so |
| introduced initially in retailing). | | | | as to be easily and unambiguously understood. |
| Relational data models are use to implement the | | | | Codes which are used in operational systems, should |
| above conceptual model (as depicted in the resource | | | | be replaced with descriptive names of the |
| link). | | | | characteristics. Numerical (quantitative) measurements |
| This model is easily understood by Business analysts, | | | | should not be entered in dimension tables (given that |
| in contrast with other operational systems models | | | | these facts should be stored in fact tables). Non |
| ('normalized data models' in relational modeling | | | | numerical measurements (e.g. measurements which |
| language). In a relational modeling representation, the | | | | can be described in text) which are derived from a |
| model in its simple form, consists of a central 'fact | | | | list of discrete values, should be entered in a |
| table' and 'dimension tables' which are connected to | | | | dimension table. The dimension tables usually maintain |
| the 'fact table' via reference keys (foreign keys in | | | | a limited number of records (the different |
| relational modeling language). This form is called 'star | | | | descriptions that a dimensional entity may take) (the |
| schema'. Fact table The 'fact table' is the central table | | | | number of rows is known as the cardinality of the |
| in a dimensional model, which stores the | | | | table). The attributes of dimension tables, play an |
| measurements (facts) on which analytical processing | | | | important role in dimensional analytical processing, |
| takes place. All measurements adhere to the same | | | | given that they form the base of all 'restriction |
| level of detail. The most useful measurements are | | | | operations' which are applied. (e.g. sales that took |
| usually additive or semi-additive, in order to allow | | | | place on a specific branch and date are derived by |
| analytical processing (numerical calculations which | | | | restricting on the branch and the date dimension). |
| produce additional 'derived' facts). During analytical | | | | Moreover, they form the headings in the reports |
| processing, thousands or millions of fact rows are | | | | produced. Therefore, dimensional attributes are the |
| retrieved and numerical processing is applied on facts | | | | 'entry points' to the measurements which are |
| or fact combinations. Facts which are stored in a fact | | | | captured in the fact table. The value of a dimensional |
| table, should be captured at the most detailed (or | | | | model is directly proportional to the quality and depth |
| most granular) level (also called 'atomic level', meaning | | | | of its dimension tables. |
| something that cannot be divided). On the other | | | | |