Dimension tables contain attributes that describe business entities. For example, Customer Name, Region, Address, Country and so on.
Dimension tables also contain primary keys that identify each member. Dimension table attributes provide context to numeric data, such as, the numeric data (measure) “# of Service Requests”. Attributes stored in this example dimension are Service Request Owner, Area, Account, and so on.
In the two following figures below, Sales Facts and Facts are Fact tables while Markets, Periods, Products, Account Hierarchy, Account Region Hierarchy and Account are dimension tables. Any logical table that is not a fact table or a bridge table is a dimension table.
The figure below illustrates a true star schema:
The next figure illustrates a logical snowflake schema which defines hierarchies by using multiple dimension tables. The value of the snowflake schema is that the server treats outer joins differently from outer joins that are within a logical table source. Within a logical table source, the joins are always executed. When between logical tables, the joins are only performed when required.
Dimension tables are expected to store columns that cannot be aggregated whereas fact tables are expected to store columns that can be aggregated.