Factless Fact Tables

Factless facts are those fact tables that have no measures associated with the transaction.  Factless facts are a simple collection of dimensional keys which define the transactions or describing condition for the time period of the fact.

You may question the need for a factless fact but they are important dimensional data structures which capture important information which can be leveraged into rollup measures or as information presented to a user.

The most common example used for factless facts are student attendance in a class. As you can see from the dimensional diagram below the FACT_ATTENDANCE is an amalgamation of the DATE_KEY, the STUDENT_KEY, and the CLASS_KEY.

As you can see there is nothing we can measure about a student’s attendance at a class. The student was there and the attendance was recorded or the student was not there and no record is recorded. It is a fact, plain and simple. There is a derivation of this fact where you can always load the full roster of individuals registered for the class and add a flag stating the person was in attendance.

Could you say the flag is the measure? Some people consider the flag a measure because it can now be used as an additive measure of attendance. For example: how many students were in attendance for class 1 on April 10, 2019?

SELECT SUM(fa_prsnt_fl)  stdnt_cnt
FROM FACT_ATTENDANCE
WHERE fa_class_key = 1
AND fa_date_key = 20190410;

I could also find the average student attendance for the class for the semester with the following SQL:

SELECT AVG(fa_stdnt_prsnt_fl)  stdnt_avg
FROM FACT_ATTENDANCE
JOIN DIM_DATE on fa_dt_key = dt_key
WHERE fa_class_key = 1
AND dt_semester = 201901;

In conclusion, factless fact tables are important dimensional data structures use to convey transactional information which contain no measures. These tables are occasionally necessary for capturing important dimensional relationships which are critical to the meeting the defined business reporting requirements.