Drill down design

View previous topic View next topic Go down

Drill down design

Post  schnedar on Tue Jun 02, 2009 3:48 pm

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.

schnedar

Posts : 4
Join date : 2009-04-23

View user profile

Back to top Go down

Re: Drill down design

Post  BoxesAndLines on Wed Jun 03, 2009 8:37 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Interesting case for machine learning

Post  colorfool on Thu Jun 04, 2009 4:01 am

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

View user profile

Back to top Go down

Re: Drill down design

Post  ngalemmo on Thu Jun 04, 2009 11:58 am

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Drill down design

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum