In a dimensional design, the fact table is the locus for process measurement. It is the place where measurements are stored. The measurements are called facts, which is where the fact table gets its name. Paradoxically, a fact table does not always require facts to measure a process.
A fact table that contains no facts is called a factless fact table. This oxymoron aptly describes the design technique discussed in this chapter. Although no facts are explicitly recorded in a factless fact table, it does support measurement. A factless fact table is useful in two kinds of situations:
- Factless fact tables for events record the occurrence of activities. Although no facts are stored explicitly, these events can be counted, producing meaningful process measurements. Examples include the number of documents processed or approved, the number of calls to a customer support center, or the number of impressions of an advertisement.
- Factless fact tables for conditions are used to capture significant information that is not part of a business activity. Conditions associate various dimensions at a point in time. When compared with activities, they provide valuable insight. Examples of conditions include eligibility of people for programs, the assignment of salesreps to customers, active marketing programs for a product, or special weather conditions in effect.
Differentiating between facts and dimensions
Essentially, facts are numbers. They are numbers that we will add up, average, count, or apply some other calculation to. For example, sales value, sales quantity, and monthly balance are all facts.
Dimensions are the values that give context to our facts. So, customer or product are both examples of dimensions. Date is also a good example of a dimension—almost every fact that you will come across will have a date context.
We store dimensions in a table of attributes. For example, a customer table might have attributes of name, city, or country. A date table will have attributes such as year, month, quarter, and week.
A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information. Common examples of factless fact tables include:
- Identifying product promotion events (to determine promoted products that didn’t sell)
- Tracking student attendance or registration events
- Tracking insurance-related accident events
- Identifying building, facility, and equipment schedules for a hospital or university.
Factless fact tables are used for tracking a process or collecting stats. They are called so because, the fact table does not have aggregatable numeric values or information. 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 tables for Events
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. Sometimes there seem to be no facts 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.
The above fact is used to capture the leave taken by an employee.Whenever an employee takes leave a record is created with the dimensions.Using the fact FACT_LEAVE we can answer many questions like
- Number of leaves taken by an employee
- The type of leave an employee takes
- Details of the employee who took leave
Factless fact tables for Conditions
Factless fact tables are also used to model conditions or other important relationships among dimensions. In these cases, there are no clear transactions or events.It is used to support negative analysis report. For example, a store that did not sell a product for a given period. To produce such report, you need to have a fact table to capture all the possible combinations. You can then figure out what is missing.
For eg, fact_promo gives the information about the products which have promotions but still did not sell.
This fact answers the below questions:
- To find out products that have promotions.
- To find out products that have promotion that sell.
- The list of products that have promotion but did not sell.
This kind of factless fact table is used to track conditions, coverage or eligibility. In Kimball terminology, it is called a “coverage table.”
We may have the question that why we cannot include this information in the actual fact table .The problem is that if we do so then the fact size will increase enormously.
Factless fact table is crucial in many complex business processes. By applying you can design a dimensional model that has no clear facts to produce more meaningful information for your business processes. The factless fact table itself can be used to generate the useful reports.