Hi – I’m struggling with the best way to model this given all of the "multiple fact" strategies I've read. Really hoping someone could give some guidance. The frustrating thing for me is that I can do this all in SQL very quickly, but we lose flexibility the more calculations we push back to our warehouse, so we’re looking for the best middle ground.
Background: we require contacts to be certified before they can sell our product. Every contact has a role and every role has different tests that need to be completed in order for a contact to be certified.
Goal: For all contacts, see which tests are required and which have been completed.
Issues:
- For one, our contact table is historical by quarter, so CONTACT_ID is not unique here. A contact that existed for 3 quarters would be in the table 3 times.
- I’ve tried to join the fact tables together using common dimensions, but I’m unable to get the full picture of Contact -> Requirement -> Test
- Ideally the test data would relate to a separate DATE dimension, as we would want flexibility on seeing when the tests were completed.
- Sometimes a contact may take tests outside of their requirements
Tables in model:
Historical Contacts |
QUARTER |
CONTACT_ID |
ROLE_ID |
We get the requirements for every contact based on the role of the contact and the year of the contact.
Contact Certification Requirements |
ROLE_ID |
YEAR |
TEST_ID |
We know what tests need to be completed based on contact's role in the requirement table and which contacts did the tests from the contact table.
Test Data |
CONTACT_ID |
TEST_ID |
DATE |
