bridge table design

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.

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.

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

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

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

Yes.