Datawarehouse - star schema for cas accidents

To be honest I am completely new to Data Warehouse practices and as an assignment, I need to design a data warehouse using star-schema for car accidents in a country. At first, I have designed the FactTable as Accident with measures QtyCars and QtyCasulties with dimensions: "Driver", "Car", "Casualties", "Location", "Condtitions"..
but then I have realized that they are many to many relationships for example:
Accident 1: there is only one vehicle with two casualties.
Acc 2: there are two vehicles, the first with one casualty the second with two.
Acc 3: there were three vehicles. The first with three casualties, the second with two but, the third vehicle has no casualties. What should I change in the schema below?
My star schema

1 Answer
1

One of the hardest things in determining a fact table is to work out the granularity. And that is driven by your requirements.

I suspect that the granularity in your case is at the individual casualty level, which means you would not include the CarQty and CasualtyQty in the fact table.

One of the other things about a fact table is that you should be able to sum it up across any dimension. If you have it at the casualty level, then you need to always count(distinct car_id) to get number of cars, count(distinct accident_id) to get number of accidents, or count(*) to get number of casualties.

That's probably the best way to do it in your case because it would allow a query like "How many cars where involved in accidents that happened in June involving BWMs" and just about anything else.

Some of these queries are not so simple, for example you need to look at all casualties for the accident if any one of the cars is a BMW, but all such queries are at least answerable with some work.

Any consolidated aggregate tables would then be added, such as one containing counts, on as as needs basis depending on reporting needs.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.