DW @ Type of Dimensions and Facts

In a DW, a Dim is a data element that categories each item in a data set and used for slicing and dicing the data in DW. The primary function of Dim is to provide the filtering, grouping and labeling. And a Fact table is a table that have the numerical data (Measures) that can be aggregated based on Dim data elements.

Type of Dims

• Confirmed Dim: These are dims that have the same meaning for all the fact with which those are related (Ex. Date Dim that have the same meaning for all the facts)
• Junk Dim:  These are dims that have the miscellaneous data (like flag & indicator) that do not fit in the base Dim table (Some time we need to optimize a Dim that have some attributes that are important but not required so these attributes are moved in to a New Dim table that Dim table called Junk Dim)
• Degenerated Dim: These are dims that are derived from the fact table and does not have its own Dim table (Sometime there are some attributes coming in fact table which are not fixed but we need to categories the data based on these attributes, so we create a Dim from Fact table for those attributes and that Dim table called Degenerated Dim)
• Role-Playing Dim: A Dim that can play different roles in a fact table. (Ex. Date Dim can be used for the Order Date, Shipping Date, Invoice Date in a Order Line fact table)  
• Slow Changing Dim (SCD): A Dim is called SCD that can change over time. It may change immediately and sometimes rapidly. It has 3 type of data handling methods (Depend on project requirement): 

1. Type-1) That will not maintain any history will replace the existing data.
2. Type-2) That will have only previous history 
3. Type-3) That will maintain all the history along With Effective From 

• Rapid Changing Dim (RCD) : A SCD that will change rapidly is called Rapid Changing Dim.

Type of Facts:

• Additive: These facts can be aggregated for all the Dims in fact table to make any information 
• Semi Additive: These facts can be aggregated for some of the Dims not for all Dim in fact table to make any information
• Non-Additive: These facts cannot be aggregated for any of the Dims in fact table to make any information
• Fact Less fact Table: These fact table does not have any Fact and these table are created only to get the occurrence of actions/events.