Factless Fact Table

Summary: in this article, you will learn about factless fact table and when to use them effectively in dimensional modeling.

By definition, the factless fact table is a fact table that does not contain any facts. There are two kinds of factless fact tables:

  1. Factless fact table describes event or activity.
  2. Factless fact table describes a condition, eligibility or coverage.

Both kinds of factless fact tables play a very important role in your dimensional model design. Let’s examine each of them in detail and see the situations when you can apply them to make your design more robust.

Factless fact table for event or activity

When designing dimensional model, you often find that you want to track events or activities that occur in your business process but you can’t find measures to track.  In these situations, you can create a transaction-grained fact table that has no facts to describe that events or activities. Even though there are no facts storing in the fact table, the event can be counted to produce very meaningful process measurements.

Factless fact table for event or activity example

For example, you may want to track employee leaves. How often and why your employee leaves are very important for you to plan your daily activities and resources.

At the center of the diagram below is the FACT_LEAVE table that has no facts at all. However, the FACT_LEAVE table is used to measure employee leave event when it occurs.

Factless Fact Table - Example 1

The following SQL statement is used to count a number of leaves that  an employee has been taken:

Executing the SQL query above, it would give you the following result:

name

leave

Doe, John

7

Doe, Sam

9

Walker Mike

8

Factless fact table for condition, eligibility or coverage

Factless fact table can be also used in these situations:

  • Tracking salesperson assigned to each prospect or customer
  • Logging the eligibility of employees for a compensation program
  • Capturing the promotion campaigns that are active at specific times such as holidays.

Those examples above describe conditions, eligibility or coverage. The factless fact table can be used to model conditions, eligibility or coverage.  Typically information is captured by this star will not be studied alone but used with other business processes to produce meaningful information.

Let’s take a look at the sale star below. By looking only at the star, we don’t know what product has a promotion that did not sell.

Sales Star Schema

Sales Star Schema

In order to track this kind of information, we can create a star that has a factless fact table which is known as coverage table (according to Kimball).

Factless Fact Table - Example 2

Factless Fact Table – Example 2

In order to answer the question: what product that has promotion did not sell, we need to do as follows:

  • Look at the second star to find out products that have promotions.
  • Look at the first star to find out products that have a promotion that sold.
  • The difference between is the list of products that have promotion but did not sell.

Factless fact table Example 2

The factless fact table is crucial in many complex business processes. By applying concepts and techniques about factless fact table in this tutorial, you can design a dimensional model that has no clear facts to produce more meaningful information for your business processes.