October 23, 2014 \ Ananth TM What is Dimensional Modelling Dimensional Modelling is the set of techniques and concepts used in data warehouse design. Different Modelling techniques give different advantages in the way we store data. Dimensional modelling aims to make is relatively easy to retrieve data, especially querying large data sets. The other popular data modelling technique, Entity-Relationship (ER) modelling, is used for OLTP and aims at reducing redundancy and better performance of both change and retrieval of data. At a high level, effective dimensional models reflect the natural performance measurements and descriptive attributes of a business. Goals of Dimensional modelling Understandability: grouping by business categories to understand better Query performance: Denormalized for query performance Extensibility: can add new unexpected data How to do dimensional modelling? In dimensional modelling, everything is divided into 2 categories – facts/measures and dimensions/contexts. Dimension: A category of information. For example, the time dimension. Attribute: A unique level within a dimension. For example, Month is an attribute in the Time Dimension. 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. Step 1: Identify the dimensions Step 2: Identify the measures Step 3: Identify the attributes or properties of dimensions Step 4: Identify the granularity of the measures Step 5: History Preservation (Optional)