The OLTP systems normally use 3rd normal form. To retrieve any data from OLTP would need joins made between the Tables containing different information.
Dimensional data model is most often used in data warehousing systems. To understand dimensional data modelling need to know some of the terms used in modelling:
Dimension: A category of information. Ex: Like a time dimension.
Attribute: A unique level within a dimension. Ex: Like a Month is an attribute in the Time Dimension.
Hierarchy: The specification of levels that represents relationship between different attributes within a dimension. Ex: one possible hierarchy in the Time dimension which can be
Year → Quarter → Month → Day.
Fact Table: A fact table is a table that contains the measures of interest. Ex: Internet sales and Store Sales amount would be such measures. These measures are stored in the fact table with the appropriate granularity.
We can choose granularity as we like as sales amount to be stored by Month/day/hour.
Dimensions and hierarchies defined represents the different lookup tables.
Lookup Table: The lookup table provides the detailed information about the attributes. Ex: The lookup table for the Quarter attribute would include a list of all of the quarters available in the data warehouse with the unique ID that identifies the quarter like”Q1 2001″ represents Quater1 for 2001.
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. Attributes are the non-key columns in the lookup tables.
In designing data models for data warehouses or data marts, the two most commonly used schema models are Snowflake Schema and Star Schema.
The star schema is perhaps the simplest data warehouse schema. It is called a star schema because the entity-relationship diagram of this schema resembles a star, with points radiating from a central table. The center of the star consists of a large fact table and the points of the star are the dimension tables.
The snowflake schema is a more complex data warehouse model than a star schema, and is a type of star schema. It is called a snowflake schema because the diagram of the schema resembles a snowflake.
Snowflake schemas normalize dimensions to eliminate redundancy. That is dimensions are grouped into multiple tables instead of one large table. For example, a product dimension table in a star schema might be normalized into a products table, a product_category table, and a product_manufacturer table in a snowflake schema.
While this saves space, it increases the number of dimension tables and requires more foreign key joins. The result is more complex queries and reduced query performance.