Dimension Tables and Star Schema
Databases configured for OLAP use a multidimensional data model, allowing for complex analytical and ad-hoc queries with a rapid execution time. It consists of numeric facts called measures which are categorized by dimensions.
In Star Schema all of the information about a dimension is stored in a single table. Each level of a hierarchy is represented by a column or column set in the dimension table
In Snowflake Schema each level of a hierarchy is stored in a separate table by normalizing the dimension table.
A dimension is a category of information. Among the most common dimensions for sales-oriented data are time, geography, and product. The dimension tables contain descriptive attributes (or fields) which are typically textual fields or discrete numbers that behave like text. Most dimensions have hierarchies. Each dimension in a data warehouse may have one or more hierarchies applied to it. For the “Date” dimension, there are several possible hierarchies: “Day > Month > Year”, “Day > Week > Year”, “Day > Month > Quarter > Year”, etc.
A dimension key, such as a transaction number, invoice number, ticket number that has no attributes and hence does not join to an actual dimension table are called Degenerate dimensions.
A “Date” dimension can be used for “Date of Sale”, as well as “Date of Delivery”, or “Date of Hire”. This is often referred to as a “role-playing dimension”.
Conformed dimensions are either identical or strict mathematical subsets of the most granular, detailed dimension.
A Junk Dimension is a dimension table consisting of attributes that do not belong in the fact table or in any of the existing dimension tables. The nature of these attributes is usually text or various flags, e.g. non-generic comments or just simple yes/no or true/false indicators.