Types Of Dimensions:

Dimensions are classified as followings:


1. Slowly Changing Dimensions or SCD (Type0,Type1,Type2,Type3,Type4,Type6)
2. Slowly Growing Dimensions or SGD (Type1,Type2,Type3 from SCD)


And Other Types are...


1. Conformed Dimension
2. Junk Dimension
3. Degenerated Dimension
4. Role Playing Dimension

Conceptual Data Model or Conceptual Data Schema:-

Conceptual Data Model Identifies the highest level of relationship between important entities of Data Model. It contains only important entities, no attribute, and no key specification. It contains only entity name and entity relationship as shown below

Degenerate Dimension (DD)

It is a dimension which is derived from fact table. DD doesn’t have separate dimension table. It is mostly used when we planned use transactional level information on the fact table. The main purpose of DD is to provide the info directly without referring other table.

Primary key of a fact table is subset of table's foreign key. In some cases, we do not need the foreign key in the fact guarantee the uniqueness, in that scenario, DD plays the role.


Dimension keys without corresponding dimension tables is known as Degenerated Dimensions

Process of Preparing Dimensional Model

• Preparing ER Data Model
• ER à split into discrete business process
• Model Each Process separately
• Select Many to many relationship in the ER Model which contains numeric and additive nonkey facts, convert them to fact table
• De-normalize remaining table in to flat table with single part keys which will join directly to the fact table. These table become dimension table

Dimension Tables

• It is integral companion to the fact table
• It contain descriptive information of the business
• Each dimension is defined by its single primary key
• It is Entry point of the fact table
• Dimension attribute deliver robust analytic slicing and dicing capability
• Dimension implements the UI to the DWH

Bus Architecture:

• Conformed dimension are the bus architecture linking different data mart
• It is a dimensional model with common dimension linking to different fact table.
• This is belongs to fact constellation schema
• Distributed DWH systems
• DWH developed in Decentralized way