Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension

Hi Experts,

I am wondering how to model this scenario in the Data Warehouse. I have an Employee table which contains different types of employees (Marketers, Sales Reps, and Service Reps). If I were to associate that to say a fact table of activity regarding a marketing opportunity which is assigned to a sales rep. That would mean I would have two records in the fact table which reference 2 records of the Employee dimension. I was thinking of modeling this in the Data Warehouse with a dimension for each of the types of employees with joins to the fact. Is that too normalized, should it just be one Employee dimension in the warehouse and then in the modeling layer where I create the separate dimensions to the fact table?

Appreciate any advice.

Thanks,
Krystal

Usually you just handle this with multiple role-based foreign keys in the fact rather than build out separate dimensions. For example, a SALES_REP_EMPL_KEY, MARKETER_EMPL_KEY and so on.

Why would the same employee have two records in the employee dimension? Your employee dimension sounds like an Employee Role dimension.

I think you might have misread and I might have miswrote. My apologies.  The fact table can contain TWO employees from the Employee dimension on the SAME record.  The Employee Dimension itself has an employee only ONCE in it.  The answer above was perfect.

Thanks,
Krystal

Mmmmm, perfect… :-)