Data Warehouse fact-less fact Tables and Examples

A Data Warehouse fact-less fact table is a fact that does not have any measures stored in it. This table will only contain keys from different dimension tables. The fact-less fact is often used to resolve a many-to-many cardinality issue.

Types of Fact-less fact tables in Data Warehouse?

There are two types of fact-less fact tables

Event capturing fact-less fact

This type of fact table establishes the relationship among the various dimension members from various dimension tables without any measured value.

For examples, Student attendance (student-teacher relation table) capturing table is the fact-less fact. Table will have entry into it whenever student attend class.

Following questions can be answered by the student attendance table:

Which student is taught by the maximum number of teachers?

Which class has maximum number of attendance?

Which teacher teaches maximum number of students?

All the above queries are based on the COUNT (), MAX () with GROUP BY.

Coverage table – Describing condition

This is another kind of fact-less fact. A fact-less-fact table can only answer ‘optimistic’ queries (positive query) but cannot answer a negative query. Coverage fact is used to support negative analysis reports. For example, an electronic store did not sell any product for give period of time.

If you consider the student-teacher relation table, the event capturing fact table cannot answer ‘which teacher did not teach any student?’ Coverage fact attempts to answer this question by adding extra flag 0 for negative condition and 1 for positive condition.

If the student table has 20 records and teacher table has 3 records then coverage fact table will store 20 * 3 = 60 records for all possible combinations. If any teacher is not teaching particular student then that record will have flag 0 in it.

Read:

• Data Warehouse Two-tier Architecture in Details
• Data Warehouse Project Life Cycle and Design
• Types of Fact Tables in a Data Warehouse
• Database ACID Properties and Explanation
• Data Warehouse Fact Constellation Schema and Design
• Types of Dimension Tables in a Data Warehouse
• Various Data Warehouse Design Approaches:Top-Down and Bottom-Up