hi,
I'm doing data models for Human Resources. As I'm looking at the various reports, could you not say that the EMPLOYEE is the basic unit of fact data?
Originally, we have designed a fact table for headcount (fact is count=1 for each emp row).
Then, as we add a fact table to Compensation, each row is again really an employee, just with different facts (salary, bonus, % to Market, etc).
Again, another fact table for performance reviews in the same way - each row really is an employee, just with different facts (and some specific dimensions)
In fact, could you not have ALL Employee-based HR data (most of it) be in ONE FACT TABLE?
Of course, you would need a ton of dimensions to deal with different timeslices for different applications, location, business type, etc...
Just looking for opinions, thanks!
HR models are very much about factless fact data. Real facts only come into play if you are reporting on time registration data. THings like salary and bonus CAN be considered as attributes to a slowly changing dimension I guess. (or a semi-additive facts)
Another factless fact is the relationship between an employee and the department he/she is working for. If you do not capture this right then you will wind up with historically faulty reports over time..