Factless fact and history for fact table

Question

According to Kimball, Factless fact table are“fact tables that have no facts but captures the many-to-many relationship between dimension keys.” A factless fact table is a fact table that does not have any measures. It is essentially an intersection of dimensions (it contains nothing but dimensional keys). In my case, I am creating a fact table which captures for each employee :

• their function
• their role
• their main manager
• their department
• their status
• EntryDate
• ExitDate

The event related to my fact table are : - when any change is applied to the function, role main manager.. of an already existing employee - or a new employee has arrived

I am adding for historical need in my fact :

• BI_StartDate
• BI_EndDate

Is my fact table a Factless fact ?
The fact is containing the history : How can I track the date of updates if I am having an update of function and type of an employee of the same period ?

Answer 1

This is an example of a Type II dimension.

Notes: The current record should have a null BI_EndDate

You can either join on Current Info by joining on EmpID and BI_EndDate is null

or

You can join on the record at the time

EmpID and [Comparison date]>=BI_StartDate and [Comparison date] <= ISNULL(BI_EndDate,'20991231')

Futhermore, I think your example of a factless fact seems more in line with many to many relationships.

As an example, think of students and classes. There are many students and many classes but the intersection of these two is a studentClass table. (with the official title of studentEnrollment but that not important).

I don't necessarily call this factless as the measure coming from this table are counts.