Factless Fact Tables

PostgreSQL – Why You’d Want to Use It
July 11, 2019
5 Tips and Workarounds for Angular/JS Extension Development
July 22, 2019
Show all

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
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
                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.

Jim McHugh
Jim McHugh
An accomplished Sr. IT leader with over 27 years of professional experience applying technology solutions to business challenges. Mr. McHugh has expertise in data modeling, data governance, business intelligence, predictive analytics and data science. His responsibilities include establishing and executing a strategy that ensures the application of data management & analytics to enable an organization to strategically leverage and fully realize the value of their data.

Leave a Reply

Your email address will not be published. Required fields are marked *