August 12, 2021 at 2:17 pm
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.
Cheers,
David McKinney.
August 12, 2021 at 2:35 pm
I just read a post in the 'Strategies and Ideas' forum (which is maybe where I should have posted this!). It's making me think that I should combine the two facts into a single fact table. So I might try this. But I'm still perplexed. I want to incorporate documents next into the data model where 1 process has 0..many documents. So from 1 process I have many Validations and many documents. I can't imagine putting all of these in the same fact table, can I?
Also at Process level I will have the duration of the process in Working Days. If the grain of my fact table is at the more granular level of the validation then what becomes of my duration field? I don't want to repeat the duration of the Process for each Validation, and neither do I want to distribute the duration across several Validations.
August 12, 2021 at 2:56 pm
Just watched a compelling youtube video which makes me think that I need to incorporate all the IDs from the Process fact table also in the Validations fact table, and link both tables to the same Product level dimensions. The Validations table would have all the Product dimension IDs plus a couple of it's own.
I think that doing it this way will allow me to achieve what I'm targeting....?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply