Factless Fact Tables

Over the past three weeks, we've looked at three ways to design fact tables.

1.) Transaction Fact Table

2.) Period Snapshot Fact Table

3.) Accumulating Snapshot Fact Table

As the term suggests, all of these tables contain facts (measures) that pertain to the business process.  There are instances in which a fact table can be designed to contain no measures but only events.  This is referred to as a factless fact table.  Consider a user who wishes to see the org chart on a particular day.  In this case, a factless fact table may be designed to contain one row per day (if that is the grain) and the keys to the Human Resources descriptors that existed on that day.  Every column in this table will contain a foreign key to a dimension table or a degenerate dimension value.  Consider this plain-english version of this table:

Date	Name	Department	Supervisor	Job Title
1/15/2013	Jason Smith	Human Resources	Allen Wells	HR Associate I
…
5/31/2013	Jason Smith	Human Resources	Allen Wells	HR Benefits Coordinator
…
3/15/2014	Jason Smith	Information Technology	Mike Williams	HR Systems Analyst

This tells us that Jason Smith was hired as an HR Associate I on 1/15/2013.  He was promoted to a Benefits Coordinator on 5/31/2013 and then moved to Information Technology on 3/15/2014.  FYI, although the table above only shows three rows there will probably be several more.  This table will probably contain one row per day with the same data until it changes, indicated by the ellipses in the table above.

In the future we will look at writing some ETL to populate a factless fact table.

Image courtesy of watcharakun / FreeDigitalPhotos.net