Tuesday, May 24, 2011

Factless Fact Table

Factless Fact Table

A factless fact table is a table that contains nothing but dimensional keys.  Ralph Kimball’s earlier article is still the best source to learn this.
There are two types of factless tables.  One is for capturing the event.  An event establishes the relationship among the dimension members from various dimension but there is no measured value.  The existence of the relationship itself is the fact.
This type of fact table itself can be used to generate the useful reports.  You can count the number of occurrences  with various criteria.  For example, you can have a factless fact table to capture the student attendance (the example used by Ralph).  The following questions can be answered:
  • Which class has the least attendance?
  • Which teachers taugh the most students?
  • What is the average number of attendance of a given course?
All the queries are based on the COUNT() with the GROUP BY queries.  I think that the interesting metrics are the nested GROUP BY so you can first count and then apply other aggregate functions such as AVERAGE, MAX, MIX.
The other type of factless table is  called Coverage table by Ralph.  It is used to support negative analysis report. For example a Store that did not sell a product for a given period.  To produce such report, you need to have a fact table to capture all the possible combinations.  You can then figure out what is missing.

No comments:

Post a Comment