Just Give Me the Factless Facts, Ma'am

nvisia is an award-winning software development partner driving competitive edge for clients.

We all know that in the world of dimensional modeling, the central table of a star schema is the fact table. This fact table contains both keys to join with dimensions as well as business measures. There are times, however, when measures are not needed and the fact only contain the pertinent keys. This we call a factless fact table. You are probably thinking oxymoron here but in reality, they do have their advantages. 

There are two types of factless facts; events and coverage.

The first type of factless fact table is a table that records an event. Many event-tracking tables in dimensional data warehouses turn out to be factless where no facts are associated with an important business process. Events or activities occur that you wish to track, but you find no measurements. In situations like this, build a standard transaction-grained fact table that contains no facts.

Diagram 1 below depicts an example of an event tracking factless fact. This model shows award nominations for all historical entertainment awards such as the SAGs, Emmy's and of course the Oscars. There are no business measures supporting this model but just the tracking of nominations. Dimensions are the Actor/Actresses, Movies/Shows, the category of the nomination, the ceremony type and date of ceremony as well as the winning award if they have won. This model would answer a lot of interesting questions such as the following:

Diagram1

What movie had the most nominations in the '65 Oscars?
How many times did Jack Nicholson get nominated?
How many awards did Breaking Bad win last year?

The SQL to this model would simply be a SELECT COUNT(*). Alternatively, you can add an INT field in the fact that will always have a value of 1. In this case, a NOMINATIONS field would be added in the end of the fact and your SQL would be a SELECT SUM(NOMINATIONS)

In another case, there may not be clear events or transactions but you want to support negative analysis. This is where a "coverage fact table" comes handy. Coined from Ralph Kimball, coverage fact tables are used to model conditions or other important relationships among dimensions. Take a look at Diagram 2. This model depicts car sales at a local car dealership. Based on the model, you can come up with top sales reps for any given time. The model, however, does not provide information on the biggest slacker based on sales effectiveness (customers sold / customers assigned) or if assignments are done evenly among the sales force. Diagram 3 depicts the Customer SalesRep Assignment. Notice the sales amount measure is pulled out of the fact table and the grain of the data is finer at the assignment level as opposed to actual sales. The difference between the two fact table results set would then answer the negative analysis questions.

Diagram 2:

Diagram2

 

Diagram 3:

Diagram3

In closing, we all want the facts...even if they are factless!

http://www.kimballgroup.com/1996/09/factless-fact-tables/

 

 

For Additional Blog Post please check out NVISIA's Enterprise Data Management Page

Related Articles