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 events or activities.
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 a 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 the event when it occurs.

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

SELECT employee_name AS name, COUNT(leave_type_id) AS leave
FROM fact_leave
INNER JOIN dim_employee…
WHERE employee_id =Code language: PHP (php)

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

Factless fact table for condition, eligibility, or coverage

A 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.

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

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 is the list of products that have promotions but did not sell.

The factless fact table is crucial in many complex business processes. By applying concepts and techniques about the 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.