Be confused with Outtrigger

Hi experts,

I have refered to "The Datawarehouse Toolkit Olap Solutions - Chapter 6".
I'm confused how we track employee skills by using Outtrigger.

EMPLOYEE SKILL GROUP OUTTRIGGER as B
B.Employee Skill Group Key int PK
B.Employee Skill Category
B.Employee Skill Key varchar
B.Employee Skill Description

Data of B
1,DBA,MSSQL\ORALCE\MYSQL,rs1
2,DBA,MSSQL\ORACLE,rs2
3,DBA,ORACLE\DB2\CONGROS,rs3
4,BANKING,CASATD\CL,rs4


EMPLOYEE DIMENSION as A
A.Employee Key PK
A.Employee Attributes ...
A.Is_Available
A.Employee Skill Group Key FK


If employee E got skills belonging to 2 skill groups, which one will be our solution?
1.insert a new definition of skill group with into B. And E traking-record will have new Employee Skill Group Key. Then it looks like difficult to understand if both skill groups dont have any relationships

2.duplicate E tracking-data with 2 appropriate foreign keys with values of Is_Available = 1. Is it ok?

3.Then we answer the question: How many employees have got skill DBA of MSSQL and CONGROS? like [Employee Skill Key] LIKE ‘%\MSSQL\% OR [Employee Skill Key] ‘%\CONGROS\%’
Or let's count employee bases on every specific skill --> how can we get the report?

Why dont we use dimension table containing skills with level for each skill? And fact table just constains foreign keys to tracking employee' skills. Although it makes the report clearer, I think it's not a good choice. With this way, we will have a set of records with the same "time key" reflecting current skills of an employee.

I'm sorry. I'm not good at English. And I'm newbe in DW design.


Thank you for your help!

magaret luong

If all you need to do is answer, "how many employees have skill A?" then I would create a dimension table of Skills and the fact table would contain a row for Employee/Skill. If an Employee had 6 skills, they would be in the fact table 6 times - once with each of their skills.

Jeff Smith wrote:If all you need to do is answer, "how many employees have skill A?" then I would create a dimension table of Skills and the fact table would contain a row for Employee/Skill. If an Employee had 6 skills, they would be in the fact table 6 times - once with each of their skills.

Hi Jeff,

I agree with you. I will modify my model following your suggestion.

Thank you!