Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Drill down design

4 posters

Go down

Drill down design Empty Drill down design

Post  schnedar Tue Jun 02, 2009 3:48 pm

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.


Posts : 4
Join date : 2009-04-23

Back to top Go down

Drill down design Empty Re: Drill down design

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

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

Back to top Go down

Drill down design Empty Interesting case for machine learning

Post  colorfool 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.


Posts : 3
Join date : 2009-06-01
Location : Nürnberg, Germany

Back to top Go down

Drill down design Empty Re: Drill down design

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

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

Back to top Go down

Drill down design Empty Re: Drill down design

Post  Sponsored content

Sponsored content

Back to top Go down

Back to top

- Similar topics

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