Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

3 posters

Go down

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

Post  kclark 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

Back to top Go down

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

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

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

http://aginity.com

Back to top Go down

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

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

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

Post  kclark 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

Back to top Go down

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

Post  ngalemmo Fri Feb 21, 2014 1:14 pm

Mmmmm, perfect… :-)
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension Empty 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

Back to top

- Similar topics

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