Bridge table help

View previous topic View next topic Go down

Bridge table help

Post  mrd on Mon Jun 01, 2015 11:06 am

Hi folks

I work in Higher Education and I'm modelling a snapshot fact for academic research awards (grants essentially).

Now an award can have multiple award holders (employees) and award holders can hold multiple awards so I was going to create a bridge between the award fact and my employee dimension to model this. However, the business also wants to view the facts against the employee's department and job (which exist as separate dimensions as employees can have multiple jobs/departments, although we're only dealing with their primary ones in this case).

What's the best way to approach this? Do i create separate bridge tables linking the award fact to each dimension: employee, department, and job?

Thanks for any help



mrd

Posts : 2
Join date : 2015-05-07

View user profile

Back to top Go down

Re: Bridge table help

Post  ngalemmo on Tue Jun 02, 2015 4:57 pm

That is the cleanest approach. If you try to consolidate to a single bridge (one with three dimensional references) you run the risk of a M:M relationship if multiple employees are in the same department or job for a given award. You may consider adding an employee count (total and #for the job or department) to the job and department bridges for allocation/contribution calculations.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Bridge table help

Post  mrd on Wed Jun 03, 2015 9:06 am

Great thanks for that; I appreciate the tip about adding the employee count on the job/dept bridges.

mrd

Posts : 2
Join date : 2015-05-07

View user profile

Back to top Go down

Re: Bridge table help

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