DW Concepts: What is a Factless Fact table ? Usage and Examples

So any new BI/ETL developer starting out his career would come across this term, Factless Fact. So what is a factless fact, Let us see with the help of a few examples,

A Factless fact table is defined by Ralph Kimball as,

Fact tables that have no facts but captures the many-to-many relationship between dimension keys.

A simplified definition would be

A table in Data warehouse capturing the relation between two or more entities and doesn't have any measurable quantities or facts.

Imagine the example of a school- class - student - stream enrollment scenario where one row captures information of a student enrolled to a particular class.

ROW_WID	CLASS_WID	STUDENT_WID	STREAM_WID
1	30	25	1
2	31	31	1

As you can see from the above table structure, this table captures or the grain( lowest level of detail ) is enrollment details of a particular student assigned to a class and a particular stream. The WID columns are nothing but surrogate keys of individual dimensions and ROW_WID column is the surrogate key of the fact. 

This table as you can see doesn't contain any measurable entities like Marks. This table acts as an entity to identify the students enrolled in a particular class. and stream We can create derived measures from this fact by taking count of individual ROW_WID items, for eg:

1. Taking count of students(STUDENT_WID) grouped by Class will give you the number of Students assigned to a class. 
2. Taking count of students(STUDENT_WID) grouped by Stream gives you the number of students under a single stream. 
3. Taking the count of classes(CLASS_WID) grouped by Stream gives you the number of Classes under a particular Stream.

The star schema diagram for this factless fact is below,

Student Enrollment Factless Fact Schema Diagram

Naturally you will doubt that same information can be obtained from other fact tables such as marks, This is true, however imagine a scenario where a student no longer attends school and has not attended exams. In such a case there are chances that his mark details are not present in the marks fact table. Hence you will not be able to get accurately the information of all students assigned to a class or stream. This is where a factless fact table is significant in a Data warehouse.  

Other similar examples of factless fact tables are, 

• Insurance - Coverage - Membership
• Sales - Product Promotion Details