Data Warehouse Concepts #3/5

Fact and Dimensions..

What is Fact Table?

A Fact table in a data warehousing data model consists of one or more numeric facts of importance for a business .Facts must be consistent with the granularity.
Examples:
1. Number of service call received.
2. Amount of sales collection.
3. No of items sold.

Types of facts?

Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

What is Factless Fact Table?

Factless fact table captures the many-to-many relationship between dimensions but contains no numeric or text facts.
They are often used to record events or coverage information.

Common examples of factless fact tables include:

• Tracking student attendance or registration events
• Tracking insurance-related accident events

What is Granularity?

The level of detail available in a given fact table as well as to the level of detail provided by a star schema.
It is usually given as the number of records per key within the table. In short, the grain of the fact table is the grain of the star schema.

What are Dimensions?

A dimension is essentially an entry point for getting at the facts. Dimensions are things of interest to the business.

What is slowly Changing Dimension?

Slowly changing dimensions refers to the change in dimensional attributes over time.
An example of slowly changing dimension is a Resource dimension where attributes of a particular employee change over time like their designation changes or department changes etc.

What is Conformed Dimension?

These dimensions are something that is built once in your model and can be reused multiple times with different fact tables. For example, consider a model containing multiple fact tables, representing different data marts. Now look for a dimension that is common to these facts tables.
Some of the examples are time dimension, customer dimensions, product dimension.

What is Junk Dimension?

A Junk dimension is a collection of random transactional codes, flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes.

When you consolidate lots of small dimensions and instead of having 100s of small dimensions, that will have few records in them, cluttering your database with these mini ‘identifier’ tables, all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table.
Example: a company might have handful of manufacture plants, handful of order types, and so on, so forth, and we can consolidate them in one dimension table called junked dimension table.

What is De Generated Dimension?

A dimension which is located in fact table known as Degenerated dimension.

An item that is in the fact table but is stripped off of its description, because the description belongs in dimension table, is referred to as Degenerated Dimension. Since it looks like dimension, but is really in fact table and has been degenerated of its description, hence is called degenerated dimension.

Thank you for reading !! Hit a like..

— Pawan Kumar