Types of Dimensions

qTypes of Dimensions-I

vConfirmed: Shared by two or more fact tables

        Eg: The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.

vJunk: A junk dimension is a collection of random transactional codes it gives convenient place to store  Junk Attributes

  Eg: instead of using two different key Combine gender and marital dimension and create one junk dimension and use that key in the fact table .

vDe-generated : Derived from Fact table. Does not have it own dimension

  Eg: Transational Code in a fact table(Invoice)

vRole_playing: Dimension often used for multiple purpose and can be joined to more than one foreign key in the fact table.

  Eg: fact table may include foreign keys for both ship date and delivery date

vInferred: Solution to not yet ready dimension.

  Eg: Generate a surrogate Key as null for other attributes 

vStatic : Not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually

  Eg: status codes or it can be generated by a procedure, such as a date or time dimension.

vRapidly Changing Dimension(RCD):Changes frequently. lot of changes happens in a attributes of a dimension table

vSlowly Changing Dimension(SCD):Changes slowly over a period of time

Types:

• SCD1: To every insert in source it will insert into target and to every update in the source it will update in the target. Contains only current records. override old value

•SCD2: To every insert in source it will insert into target and to every update in the source it will insert in the target with different Surrogate key. It contains historical record.(It can be implemented using flagging ,versioning or date range methods).it add a new row.

•SCD3: It contains partially historical records(like last 2 or 3 history).it adds a new row