Why some many different types of dimensions?


  • How many of the following are useful? And supported by 2012.?

    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


    lunedì 1 luglio 2013 18:31


Tutte le risposte

  • Hi Navind,

    Thank you for your question.

    I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

    Thank you for your understanding and support.

    Mike Yin

    If you have any feedback on our support, please click here

    Mike Yin
    TechNet Community Support

    mercoledì 3 luglio 2013 02:54
  • Hello Navind,

    Thanks for your post.

    Typically speaking, this MSDN article shows the types of Dimensions in SSAS 2012 or prior:

    Lots of them on your list would match this types if we try to find the definitions for them. We typically see Time, Regular, Parent-Child, Role-playing, slowly changing etc. type being common while others are possible as well.

    Another post that talks about the most common types of dimensions, is:

    Also, the following article shows the dimensions we choose and how we choose them. This article was written for SSAS 2000 but still applies for SSAS 2012

    Hope this helps.


    Meer Al - MSFT

    mercoledì 3 luglio 2013 17:00
  • Like all the levels of normalization and the different slow changing dimensions rules the types of dimensions are many. The academia world theorizes most of this but in the real world very few are used in most implementations. I like conformed dimensions because those could be one or all dimensions you mentioned but they are used as a single source across all implementations.

    Hope this is Helpfull. J Alverson Microsoft BI Solutions Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    martedì 9 luglio 2013 23:53