Different types of dimensions

Conformed Dimension


A conformed dimension is a set of data attributes that have been physically implemented in multiple database tables using the same structure, attributes, domain values, definitions and concepts in each implementation. A conformed dimension cuts across many facts.


Example: Date Dimension table can be shared by multiple fact tables. Date Dimension table connected to sales fact is identical to the one connected to inventory facts.


Junk Dimension


A junk dimension is a convenient grouping of typically low-cardinality flags and indicators. 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,


Example: non-generic comments or just simple yes/no or true/false indicators.


Degenerated dimension


A degenerated dimension is data that is dimensional in nature but stored in fact table, doesn’t have any dimensional table on its own.


Example: A dimension having Order Number and Order line number having 1:1 relationship with fact table. Instead of creating as a separate dimension, containing billions of rows, those 2 columns can be included as part of the fact table.


Role Playing dimension


Dimensions that can be joined to the same fact table multiple times, each time to a different column. These dimensions are referred to as role playing dimensions, as the same dimension plays different roles.


Example: Suppose, if you want to examine sales by order date, ship date and delivery date. Instead of creating 3 different date dimensions, create a single date dimension and join it to the fact table 3 times in data source view.


Parent-child dimension


Based on self-referencing relationship


Example: If you have a employee dimension and want to add parent child relationship to employee manager, you must include both employee key and parent employee key in your dimension as attributes.


Time Dimension


SSAS provides 2 ways of creating time dimension


• Base the time dimension on a dimension table that exists in a relational warehouse


• Create the time dimension based on system generated time values. (server time dimension)