OLAP Design Question - Certifications for Salespeople
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?
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.