Dimensional Data Model

Event - Factless Fact Table



Relational Database is support Binary and Unary relationship that is not support Ternary and N-ary relationships. Factless fact table is used to captures many-to-many relationships between fact and dimensions, but contains no numeric or textual facts that help to record events or coverage information.

By default, Fact table is used to generate analytical report based on “What happened?” and Factless table is used to generate analytical report based on “What didn’t happen?” in the particular Business Process. Factless fact table can implement in the following scenarios…

Identifying Product Promotion Events

Tracking Student Attendance Events

Tracking Insurance related Accident Events

Identifying Building for a Hospital / University

Identifying Facility for a Hospital / University

Identifying Equipment Schedules for a Hospital / University

Event Factless Fact Table

Event Factless fact table is used to captures many-to-many relationships between fact and dimensions, but no numeric or textual facts. The existence of the relationship itself is the fact. Boolean data type attribute should add in this factless table that helps to stores 1 or 0 data value.



An instance, when we are tracking student classroom attendance, it’s reasonable to make records both for students who do attend as well as for students who don’t. Student_Attendance_Fact table is mapped with Time, Course, Student, Teacher and Facility dimension tables. Attendance attribute is used to store 1 or 0 Boolean data to differentiate the attended and absence student information.

The combination of COUNT() function and Group By clause is used in SQL Query to generate Student Class Attendance and Absence Analytical Report. The following generic query is an example to generate analytical reports.

SELECT Column(s), COUNT(Attendance) FROM FACT, Dimension Tables
FACT.Column(s) JOIN DIMENSION Column(s)
WHERE Attendance = 1 GROUP BY Column(s)

Email Your Comment To AUTHOR