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

View previous topic View next topic Go down

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

Post  kclark on Wed Feb 19, 2014 5:19 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Feb 19, 2014 7:20 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  BoxesAndLines on Fri Feb 21, 2014 10:06 am

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

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

Post  kclark on Fri Feb 21, 2014 12:17 pm

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

kclark

Posts : 70
Join date : 2010-08-13

View user profile

Back to top Go down

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

Post  ngalemmo on Fri Feb 21, 2014 1:14 pm

Mmmmm, perfect… :-)
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum