I have an OLAP fact table which records purchases. The grain of the fact_purchases is one row per purchase. So if Bob buys a computer and a printer, there are 2 rows for Bob - one for the computer and one for the printer. Zero to many salespeople may be linked to the purchase. So maybe Alice and Susie were the salespeople who sold the computer to Bob (or maybe no salesperson was involved).

The problem is that salespeople can have zero or many certifications. So maybe Alice is a certified printer salesperson and Sue is a certified computer salesperson. Customers are not required to buy a product from a salesperson certified in that area (so Bob could buy a printer from Sue even though Sue does not have a printer certification, but we might want to run a report to see what percent of printers are sold by certified printer salespeople).

I'm confused about how to map the certifications to the fact table. Since there can be multiple salespeople and not all the salespeople will have the same certifications, how would I map this to the data warehouse?

Here are the tables that I have so far:

fact_purchases table

dim_customer_id
dim_product_id
dim_salesperson_id
dim_date_for_sales_id
price

dim_salesperson table

dim_salesperson_id
first_name
last_name

dim_product table

dim_product_id
product_name

Note - I don't want to create an outrigger with the dim_certification off the dim_salesperson table since I'm worried about double counting if a salesperson has multiple certifications. I also don't think that I can just add a 'certifications' column to dim_salesperson since salespeople can have multiple certifications and salespeople can gain/lose certifications. We could create a new salesperson row whenever a salesperson changes their certifications, but that would create confusion if we wanted to find all sales for a salesperson (since they would have multiple rows).

Any ideas about how to approach this problem? Thanks.