Monday, 5 October 2015

The ‘Fact-less’ Fact Table

The ‘Fact-less’ Fact Table

Every ‘noob’ to data warehousing is aware of the simple concepts of classic star schema model.  

I have a previous blog that describes it found here:


In some of my recent research for other blog entries I came across a term that I have read about in the past but haven’t come seen in a while.  I think terms like this come up when we as data professionals study the true concepts and theory of a subject but in our day to day work life they don’t come up as often.  I’m speaking of the term ‘fact-less fact’ table.

Just as a review, we know the fact table as the table in the middle of the star schema that contains the measurable numeric fields as well as a series of foreign keys that point to the qualifying dimension tables.  This allows us to roll up numeric additive values and relate them to other entities that give the aggregation a context that makes sense.  For example:  ‘28’ by itself doesn’t mean much but when you say ’28 kg of rice’ it makes a lot more sense.  You are applying the dimensions of ‘unit of measure’ (kg) and ‘product’ (rice) to the fact field aggregate of ‘28’.

So what does all this have to do with a fact-less fact table?  

A fact-less fact table is really just an intersection of dimensions.  It contains none of the familiar additive numeric fields we are accustomed to seeing in a fact table.  It consists only of foreign keys that point to dimension tables.  It represents a many to many relationship between dimensions.

The classic example is that of a student information system that tracks attendance.  Imagine a fact table called Student_Attendance and dimension tables called Date, Student, Location, Teacher and Class.  



The event of a student attending a class doesn’t really need a recorded numeric representation in the fact table.  Just by merit of a fact table containing foreign keys from the fact table to the dimension tables means that the student did attend the particular class on that particular day at that particular location.  It just isn’t necessary to record a ‘1’ to represent the event.   To record the numeric additive field would just increase the size of the fact table for no good reason.

Thanks for reading, 


SQL Canuck

No comments:

Post a Comment