1) Base Table
The base level dimension is the lowest level of a dimension. It is the entry point to all levels, hierarchies and attributes of a dimension.
2) Language Table
To support multi language, a separate language outrigger table is created to supplement the language requirement for Dimension table that has translated description fields.
3) Hierarchy Table
There are two kinds of hierarchy:
a. Level based hierarchy, e.g. Location -> City -> State -> Country -> Region.
b. Value based hierarchy, e.g. Commodity Code -> Commodity Code Level 4 -> Commodity Code Level 3 -> Commodity Code Level 2 -> Commodity Code Level 1.
Hierarchy defined as level-based is recommended to use star schema design that is, defining all level information within the base dimension.Hierarchy defined as value-based, a.k.a. rugged hierarchy, recursive hierarchy should use the hierarchy helper table.
4) Bridge Table
Use bridge table to support multi –to – multi relationship between Dimensions or Attributes. For Example: a product can have multiple colors; a color may belong to multiple products.
5) Dimension Index
Surrogate key is the unique index for a Dimension if _SID is used. Business Key(s) in dimension table should be the primary index for a Dimension.
BIT MAP INDEX