There are two types of factless fact tables: those that describe events, and those that describe conditions. Both may play important roles in your dimensional models.
Factless fact table for event or activity
When designing dimensional model, you often find that you want to track events or activities that occurs 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 diagram below is the
FACT_LEAVE table that has no facts at all. However theFACT_LEAVE table is used to measure employee leave event when it occurs.
Query to retrieve the leave details of an employee:
SELECT ENAME,COUNT(LEAVE_ID)
FROM FACT_LEAVE JOIN DIM_EMPLOYEE
ON FACT_LEAVE.EMPNO=DIM_EMPLOYEE.EMPNO
GROUP BY ENAME;
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 promotion that did not sell.
Sales Star Schema
In order to track this kind of information, we can create a star that has factless fact table which is known as coverage table (according to Kimball).
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 promotion that sell.
- The difference between is the list of products that have promotion but did not sell.
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.
Courtesy:zentut
