A process can have 1 or more validations.
I would like to have 2 fact tables, factProcess and factValidation.
factProcess has facts such as the start date & time, end date & time, and links to a number dimensions such as dimUser dimCalendar dimProcessType etc.
As for factValidation it will have links to dimensions such as dimUser dimCalendar but also dimValidationType etc.
Now, intuitively I would like to define a one to many relationship between factProcess and factValidation such that when filtering factProcess with one of it's dimensions, the Validations would be filtered also.
But I understand that I'm not supposed to create relationships directly between fact tables.
So what can I do?
I don't want to combine both Process and Validation in the same fact table, as Process is my main fact table and I wouldn't want to 'pollute' it with all the additional lines from the Validations.
Would I need to include all the Id column values in the Process table in each of the related Validation rows?
I feel like this should be straightforward, but I can't work it out. I need to end up with a Power Pivot such that the end user can filter on any dimension and have both fact tables filtered.