database - Data modeling : Employees and their skills

Question

I'm building a datawarehouse model about human resources data, and can't decide myself about a fact table which will contain the evolution of employee's skills :

I have an Employee table and a Skill table. Every employee can have multiple skills (I hope for them!), at a different level (100, 200, 300).

I need to keep the historic of each combination Employee/Skill/SkillLevel, so they can analyze their own evolution over time.

1- I was thinking of a transactional fact table, but it'll have only a few row every year for each employee, for example James got a new skill with the level of 100 the 8th of January, then a row with the skill level at 200 let's say 3 months later, and so on... A graphic analyze would show many empty spaces (when there aren't any new skills/skill level), or I'll need to make sophisticated queries to get something interesting, like if I want to know what are all the skills of one employee at a point in time ? This fact table seems to me very unnatural to use...

2- So I'm thinking of a snapshot fact table, which would help me to easily answer the previous question. Knowing that I would need to store a snapshot of every employee's skills every day, to make sure I don't lose any data change. Doesn't it make a huge table for only a few changes over the year ? Is it worth doing a snapshot to make the analyze easier, while I would have the same information in smaller transactional fact table ?

What is the best approach ?

Many thanks for your advices !

Take care of yourselves

Answer 1

The best-practice approach is normally to create a fact table at the lowest level of granularity. You don't have to expose this to your end users (unless you want to, of course) but you can then build any other fact tables off this base.

In your case, if you create a transactional fact table (containing a record every time someone gains a skill), you can easily query it to get the current position of all skills and you can create snapshots if needed.

I wouldn't create a daily snapshot - but having a month-end snapshot, for example, might be helpful. Given that skills are unlikely to change on a daily basis, for any individual, creating a daily snapshot would create a significant volume of duplicated data