Factless Fact Table

weather.com

In Data Warehouse parlance a “Fact Table” is the central data repository for all transactions related to a subject area. From this perspective, the concept of a fact table without fact data sounds like an oxymoron, but this concept of “factless fact” has special place within BI context wherein the analytical scenarios vary slightly from traditional reporting.

As the title suggests, a factless fact table is a star schema model does not contain any measures. In reality, it is common to have a single measure that acts as a flag or value depending on actual data it contains. The most common examples for this model are the Attendance tracking system and Student Enrollment system.

In the Leave tracking example, a measure is not required becausefactlessfact1 a valid record is created only when an employee actually avails leave. In majority of cases a single measure with constant value as is updated. At times, having a single measure to act as flag can help in invalidating leave records, for example if an employee cancels a leave, a valid leave will be flagged as 1 and a cancellation will be flagged as -1.

Similarly, the student enrollment example too factlessfact2draws several analogies with the previous example. In order to identify which student has enrolled for which course, a simple factless fact would suffice. This model can be easily extended to track more than just enrollment with additional measures such as attendance, GPA etc, which is typically adopted during actual implementation.

Factless fact models are also applied in queue based transaction environments. A queue based transaction is similar to an activity that has to pass through multiple stages before completion. Process of interviewing a candidate for job opening is an example of this type. transactional_queue_exampleSomer examples are Insurance Claims processing, Customer Service Request Interaction, E-Commerce Order shipment tracking etc.

Although conceptually a factless fact table exists, but in real-world scenarios they are used rarely. A factless fact data model provides only COUNT aggregation method to be used, which is a big drawback whereas a robust data model can provide the much more analytical scenarios.

Two major reasons for factless fact data models to be ignored by data architects today are

  • Overhead of maintaining multiple models for analysis and its associated maintenence
  • Increase in processing power (i.e. query execution time) of systems today has made this redundant