Drill down design
4 posters
Page 1 of 1
Drill down design
Hello,
I am modeling traffic safety. For an accident, we can several vehicles and occupants. A vehicle can have several safety features. A person can have several injuries, toxicology results, safety equipment used, etc.
I need to answer questions like:
How many accidents with motorcycle riders with head injuries and no helmet worn?
How many accidents where head and chest injuries occurred for a single person?
Total cost of accident from an intersection crash? For at-fault car? For victim car?
Time of day and alcoholic driver involved? Two or more drivers alcohol or drug used? (alcohol and drug are toxicology results)
Compare injuries from drivers using seatbelts vs. no seatbelts for various types of crashes.
and so forth.
I am getting confused for the number of fact table, bridges, and so forth. I do have an accident fact, which contains vehicles and occupants as a bridge table. But, injuries, safety features, and so forth, do I need another fact table for them? For them indiviudally? Would I have an occupant fact table with bridges for injuries, toxicology results, safety equipment used, etc.? All of the above? (which I'm leaning toward)
Thanks for your help.
I am modeling traffic safety. For an accident, we can several vehicles and occupants. A vehicle can have several safety features. A person can have several injuries, toxicology results, safety equipment used, etc.
I need to answer questions like:
How many accidents with motorcycle riders with head injuries and no helmet worn?
How many accidents where head and chest injuries occurred for a single person?
Total cost of accident from an intersection crash? For at-fault car? For victim car?
Time of day and alcoholic driver involved? Two or more drivers alcohol or drug used? (alcohol and drug are toxicology results)
Compare injuries from drivers using seatbelts vs. no seatbelts for various types of crashes.
and so forth.
I am getting confused for the number of fact table, bridges, and so forth. I do have an accident fact, which contains vehicles and occupants as a bridge table. But, injuries, safety features, and so forth, do I need another fact table for them? For them indiviudally? Would I have an occupant fact table with bridges for injuries, toxicology results, safety equipment used, etc.? All of the above? (which I'm leaning toward)
Thanks for your help.
schnedar- Posts : 4
Join date : 2009-04-23
Re: Drill down design
Seems like you need to go lower on the grain of the fact table to the person level. Even so, you will still have many bridge tables to handle the many to many relationships between a person and an accident. At least with this lower level fact table, you can build an aggregate fact with the higher level metrics.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Interesting case for machine learning
I am only a beginner in DataWarehousing and wouldn't know how to handle this case there but reading your post I think that your data could also reveal interesting facts to you if you let it run through a machine learning tool like WEKA. Once you let some data run through, it might answer some of your actual questions because it will basically calculate all kinds of probabilities.
colorfool- Posts : 3
Join date : 2009-06-01
Location : Nürnberg, Germany
Re: Drill down design
I would probably take a number of different approaches to attack the problem.
First, I would have a characteristics dimension with individual rows for each characteristic of an accident. I would then treat it as a multi-variate dimension and construct grouper with each unique combination of characteristics that occur. Store the grouper key on the fact and have a bridge between the grouper key and the individual characteristics.
I would also construct a junk dimension which contains flags and counts that identify characteristics of interest relating to the incident and carry that key on the fact as well. Flags such as: at intersection, alcohol involved, drugs involved, seat belts, etc.
The latter junk dimension would serve most queries while the grouper/detail combination could be used to support more detailed or specific analysis.
First, I would have a characteristics dimension with individual rows for each characteristic of an accident. I would then treat it as a multi-variate dimension and construct grouper with each unique combination of characteristics that occur. Store the grouper key on the fact and have a bridge between the grouper key and the individual characteristics.
I would also construct a junk dimension which contains flags and counts that identify characteristics of interest relating to the incident and carry that key on the fact as well. Flags such as: at intersection, alcohol involved, drugs involved, seat belts, etc.
The latter junk dimension would serve most queries while the grouper/detail combination could be used to support more detailed or specific analysis.
Similar topics
» Drill down in cubes
» Drill Around - Examples, Relevancy
» How do I connect fact tables for drill down
» What to do if BI tool doesn't support drill-across?
» Drill-across relationships (Invoices -> Payments etc.)
» Drill Around - Examples, Relevancy
» How do I connect fact tables for drill down
» What to do if BI tool doesn't support drill-across?
» Drill-across relationships (Invoices -> Payments etc.)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|