What are the types of dimension tables in star schema design?
When reading about star schema design I have seen that many people uses various names for different types of dimension tables.
Please list the names and a small description of each type. If any list also an alias name.
Answer
I have come across these types of dimension tables so far:
Regular dimension
Standard star dimension.
Time Dimension
A special case of the standard star dimension.
Parent-child dimension
Used to model hierarchical structures, fx BOM (bill of materials).
Snowflake dimension
Can also be used to model hierarchical structures.
Degenerate dimensions
When the dimension attribute is stored as part of fact table, and not in a separate dimension table. Typically used for high cardinality dimensions like "transaction number".
Junk dimension
A single table with a combination of different and unrelated attributes to avoid having a large number of foreign keys in the fact table. Junk dimensions are often created to manage the foreign keys created by Rapidly Changing Dimensions. Typically used for low cardinality, non-related dimensions like gender or other booleans.
Role playing dimensions
For instance, a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire".
Mini dimensions
For rapidly changing large dimensions. Typically used for managing high frequency, low cardinality change in a dimension.
Conformed dimensions
Implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. Also seen under the name Shared dimension.
Monster Dimension
A very large dimension.
Shrunk dimension
Is a subset of a dimension’s attributes that apply to a higher level of summary. For example, a Month dimension would be a shrunken dimension of the Date dimension. The Month dimension could be connected to a forecast fact table whose grain is at the monthly level.
Dimension.
Inferred Dimensions
While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes. This should technically be called an inferred member, but is often called an inferred dimension.
Static Dimension
It not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually — for example with Status codes — or it can be generated by a procedure, such as a Date or Time dimension.
Multi value Dimension
Is simply a bridge table between the entities involved in the many-to-many relationship. It is also possible that the many-to-many is between a fact and dimension.
Then there is a group of dimension tables I will call Dynamic dimensions.
These can be further divided into 2 groups.
Slowly changing dimension/Rapidly changing dimension
Attributes of a dimension that would undergo changes over time
Slowly Growing Dimension/Rapidly Growing Dimension
Relates to the growth of records/elements in the dimension.
NB: These can then be combined with the size of the dimension table, resulting in "Rapidly Changing Monster Dimension", "Slowly changing mini dimension" etc.
Special cases:
I'm not sure about these ones, so please help with a description/use scenario.
Data Mining Dimensions
Virtual dimension
Demographic Dimensions
Write-Enabled Dimensions
Dependent Dimensions
Independent Dimensions
Primary Dimensions
Secondary Dimensions
Tertiary Dimensions
Informational dimension
Dimension triage dimension
Non-conforming dimensions from the general ledger