bridge table design

View previous topic View next topic Go down

bridge table design

Post  kjfischer on Thu Feb 23, 2012 4:23 pm

We have a data mart with
fact table:

SALES_FACT
part_dim_id
vendor_dim_id
customer_dim_id
date
quantity
$$_amount

DIMENSIONS:
PART_DIM
VENDOR_DIM
CUSTOMER_DIM
EMPLOYEE_DIM

part 1-to-1 vendor - part comes from one and only one vendor

vendor m-to-m employee - a vendor has assigned one or many employee_users with different roles (Project manager, Sales manager, etc...) Likewise, an employee can be assigned many vendors

How do I find the $$ from sales_fact for the assigned project manager employee?

I can write this with straight SQL, but our BI team uses Microstrategy and they would like an table prebuilt with the relationship between part and employee, i.e. for part 123456 has project_manager Joe and sales_manager Steve.



kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: bridge table design

Post  ngalemmo on Thu Feb 23, 2012 5:04 pm

If you do a bridge, you cannot use vendor ID to access it, since that vendor ID may appear in other facts with different employees assigned. You need to have a new key on the fact to reference the bridge. The bridge will contain the new key, employee key, and role.

If, on the other hand, there are a small number of roles, you could simply include multiple employee keys on the fact row with role based column names.
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 design

Post  bciampa on Sat Mar 10, 2012 5:38 pm

So, the new key and the employee key will act as a composite primary key in the bridge table?

bciampa

Posts : 8
Join date : 2012-02-24

View user profile http://valuabledata.blogspot.com

Back to top Go down

Re: bridge table design

Post  kjfischer on Sat Mar 10, 2012 6:39 pm

Thanks ngalemmo,

I decided to add three employee dimension keys to the fact table representing the proejct_manager, sales_manager, and pricing_manager. This seemed to be the clearer approach. Thanks, Kim

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: bridge table design

Post  ngalemmo on Sun Mar 11, 2012 6:27 am

bciampa wrote:So, the new key and the employee key will act as a composite primary key in the bridge table?

Yes.
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 design

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