Have a modeling problem here... first the back-story...
We deal with accident data on our project, 1 vehicle-crash can have multiple people and vehicles involved, and many associated factors across the people involved. We have an established star-schema db and resultant OLAP cube. The cube we are looking to add to is at the crash-person level, so every person involved has a fact record.
The problem I'm encountering is modeling the following scenario... I have 1 crash, with 3 drivers, 3 vehicles, and 4 passengers. 2 injured drivers, 3 fatal passengers. Now we have a driver attribute called driver distraction, and to simplify it we'll say there is only 2 distractions Day Dreaming and Texting while driving.
In my example 1 driver was texting, another was day dreaming, the other had no distraction. Our users are requesting that we sum the people involved to the multiple factors involved.
One solution is to create one dimension named 'Crash Involved Texting' - where everyone in the crash would get 'Yes', and another dimension named 'Crash Involved Day Dreaming' again everyone would get 'Yes'.
Is there a way to model this with a single dimension? I've tried a simplified example, but can't get the numbers to work, snow-flake or a combination of repeating id values and their corresponding distraction that factored into their 1 accident.
Hope that makes sense - appreciate the feedback...