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