Multi-dimensional design involves dividing the world into dimensions and facts. However, like many aspects of language, the term “fact” is used in multiple ways. Initially, the term referred to the table structure housing the numeric values for the metrics to be analyzed. But “fact” also is used to refer to the metric values themselves. Therefore, when the unique circumstances arise wherein a fact table is defined that does not contain specific numeric measures, such a structure is referred to by the superficially oxymoronic characterization of a "factless fact.”
Factless facts can occur when attempting to track an event. As an event, there may be no metric involved other than the idea that the event occurred. To force the issue, the existence of a count metric containing the value “1” might be implied, as the row’s mere existence designates that the event happened once. It is a stylistic decision as to whether one should physically create a count column, or have a column dynamically created by queries extracting information by using a COUNT(*) function. These factless events could be something as simple as clicking a link on a web page, or closing an account, or receiving a call from a customer. But regardless of how subtle, there really is some kind of a business event that drives the instantiation of a fact row.
At other times, there may be the temptation to use a factless fact construct for capturing the interrelationship of various dimensions across points in time. For example, a factless fact table configuration may be used to record sales-staff-to-sales-territory-to-customer-account assignments on a monthly or quarterly basis. Using a factless fact in this alternate fashion is much more obtuse and its use could possibly be a mistake.
When choosing to establish a fact table, one should keep in mind that a fact table supports online analytic processing. Therefore, the expectation is that this is a fact which requires that its implied or explicit count should be the subject of the analysis. If one establishes a factless fact, but ends up using the factless fact structure only in conjunction with additional fact tables and never alone, then one has likely created an erroneous fact table. If one is never aggregating counts of these factless event rows, then the creation of the factless fact is not serving its true purpose.
Equating the normalized approach’s associative entity with a multi-dimensional factless fact is an inappropriate mental exercise. There may be times and circumstance where structure may seem equivalent; but occasional equivalences in expression does not mean everything equates. Multi-dimensional design and normalized design are two very different and distinct approaches to organizing one’s databases.
Some vendors, e.g., vendors with their own agendas, have tried to make a case that the two design approaches are actually the same, and may even offer examples that superficially present the appearance of such overlap. One should be cautious about falling into such traps. Such musings are mere chimera meant to confuse, as if to say that gears are circles, bicycle wheels are circles, and oranges are circles therefore gears, wheels and oranges are equivalent. Normalization expresses semantics, multi-dimensional optimizes analysis, which equates to differing objectives, and very different ideas.
Factless facts are not facts without meaning. For without independent meaning, there is nothing to create. One is only wasting time building a meaningless structure; if one finds oneself doing so, one should stop immediately. Factless facts are not a back-door approach to supporting “historical reference data” in the data warehouse.
Factless facts are meaningful structures, tracking events that may have no explicit measures, or at least no apparent measures. Even without explicit measures these facts represent something of analytical value to be quantified, aggregated, sliced and diced.