FACT LESS FACT TABLE

A fact less fact table is fact table that does not contain facts. It contains only dimension surrogate key and it records events that happen only at information level but not included any calculations. Just an information about an event that happen over a period of time.

A fact less fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of fact less fact tables include:

Ø  Identify  promotional events

Ø  Identify college and university events

Fact less fact tables are used for tracking an events or collecting statistics. They are called so because, the fact table does not have aggregate table numeric values or information. There are two types of fact less fact tables: 

1.       Which describes events.

2.      Which is having certain conditions.

Fact less fact tables for events
the fact less fact table is a table that records an event. Many event-tracking tables in dimensional data warehouses turn out to be fact less. Sometimes there seem to be no facts associated with an important business process. Events or activities occur that you wish to track, but you find no measurements. In situations like this, build a standard transaction-grained fact table that contains no facts.

The above fact is used to capture the leave taken by an employee. Whenever an employee takes leave a record is created with the dimensions. Using the fact FACT_LEAVE we can answer many questions like

Ø  Number of leaves taken by an employee

Ø  The type of leave an employee takes

Ø  Details of the employee who took leave

Fact less fact tables for conditions
Fact less fact tables are also used to model conditions or other important relationships among dimensions. In these cases, there are no clear transactions or events. It is used to support negative analysis report. For example a Store that did not sell a product for a given period.  To produce such report, you need to have a fact table to capture all the possible combinations.  You can then figure out what is missing.

For eg, fact_promo gives the information about the products which have promotions but still did not sell

This fact answers the below questions:

Ø  To find out products that have promotions.

Ø  To find out products that have promotion that sell.

Ø  The list of products that have promotion but did not sell.

This kind of fact less fact table is used to track conditions, coverage or eligibility.  In Kimball terminology, it is called a “coverage table.”

Note:

We may have the question that why we cannot include these information in the actual fact table .The problem is that if we do so then the fact size will increase enormously.

Fact less fact table is crucial in many complex business processes. By applying you can design a dimensional model that has no clear facts to produce more meaningful information for your business processes. Fact less fact table itself can be used to generate the useful reports.