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

What is Fact Table?

• It is a primary table of a dimension model
• It contains numerical information of the business
• It contains aggregate values of duplicating records
• It represents business measure
• Fact table built with list of dimension along with measures
• Measurement is taken at the intersection of all the dimension

Types of Facts:
Additive Facts: - Values are summed up combination of all the dimensions
Semi Additive Facts: - It can be summed up only some of the dimension, not with others
Non Additive Facts: - It can not be summed up with any specific dimension
Types of Fact Tables:
Cumulative Fact Table:- It contains mostly additive facts
Snapshot Fact Table:- it includes more semi additive and non additive facts
Factless Fact Table: - It contains no measures or facts, only key relations (FK)