November 12, 2003, 12:00 AM — You are building a Star Schema for the user community to query and you
don't see any "real" facts that are commonly thought of based on the
requirements - facts like quantity, amount, etc. But there is still a
need to do some querying. Maybe what you are looking at is a Factless
Fact table.
The Factless Fact Table provides you with the ability to determine the
existence or non-existence of something.
Following the rule that the Fact is the intersection of the dimensions,
then it is easy to say whether a fact exists or not. A good example of a
Factless Fact Table is a school ATTENDANCE data mart. In an ATTENDANCE
data mart, you might have STUDENT, COURSE, TIME dimensions and a Fact
Table. If there is a row in the Fact table, it means that a specific
STUDENT (Chuck Kelley) attended a specific COURSE (Basket Weaving 101)
at a specific TIME (October 17, 2003 at 11:00am). If there is not a row
in the fact table, then it means that that attendance did not occur. It
may not have occurred for many reasons - sick, no class scheduled,
skipped school, etc. - but we know that attendance did not happen that
day. Maybe we can add a new dimension for class schedule!
Next you can take the results of this data mart and tie it with a GRADES
data mart to see if there is any correlation between Grades and
Attendance.
If you add FACULTY to the dimensions, then you can see if there is a
trend between grades and attendance, grades and faculty, students and
faculty, time and student, etc. I am sure that you can think of many
other dimensions that would help make this a better data mart.
Where do you use Factless Fact Tables? You use them wherever you need
only counts instead of data (data being broadly defined to include
functions like MAX, MIN, SUM, AVG, etc. since they are based on data and
not counts of rows).
