DataWarehouse - Fact Tables with different granularity / Dimension measures

Question

I've got data in the following format.

RoadAccidents (pk_accidentIdentifier) with relationship 1..* to Vehicles(fk_accidentIdentifier, ordinalNumber) -> natural PK (compound) and surrogate, which is simply a rowID).

ordinalumber is just a number to iterate through vehicles that were involved in an accident.

--Sample Row
Accident001, 1, [list of attributes of a vehicle] -- 1st car involved in an inc
Accident001, 2, [list of attributes of a vehicle] -- 2nd car involved in an inc

Vehicles with 1..* relationship with table Casualties which is connected in the same way as vehicles are with RoadAccidents Meaning:

Casualties (Compound PK: (fk_vehicleID, casualtyOrdinalNumber)

--Sample Row:
Vehicle001, 1, [list of attributes of a casualty] -- 1st casualty involved in inc
Vehicle001, 2, [list of attributes of a casualty] -- 2st casualty involved in inc

So the relationship looks as follows.

RoadAccidents 1..* Vehicles 1..* Casualties

In addition table RoadAccidents has already aggregated measures of numberOfVehiclesInvolved and numberOfCasualties .

There are 3(possibly more) approaches here for the fact tables

Two JOINS on RoadAccident -> Vehicles -> Casualties -> delete columns with aggregated numbers -> extract attributes to respective dimensions. Loose aggregated data -> have no meaningful measure -> create measures in SQL Server Data Tools.
Have 3 fact tables. FactRoadAccident, FactVehiclesCollision, FactCasaultiesInvInCollision

The problem I face: Fact tables should not be linked by foreign keys directly. Therefore, I can extract dimensions, such as.

DimRoadAccident *..1 DimRoadAccidentLocation 
DimRoadAccident` *..1 DimWeatherConditions

Fact table would look as follows:

FactAccident (fk_DimRoadAccidentPK, measure_numberOfVehicles, measure_numberOfCasualties).

With this approach. Fact tables for Vehicles and Casualties would be just a surrogate FK with the id of a Vehicle or Casualty. The only measures would be row counts, meaning - number of Vehicles or number of Casualties.

Keep only 1 fact table

FactAccident (fk_DimRoadAccidentPK, measure_numberOfVehicles, measure_numberOfCasualties).

Have dimensions linked DimRoadAccident 1..* DimVehicle 1..* DimCasualty and have hierarchies created in Data Tools and possibly create some new semi-additive measures on dimensions.

What appraoch would you suggest?

There's a very similar question here (possibly even based on the same data set?), my answer to that may help you: stackoverflow.com/q/40774305/3964881. If you're still stuck I'll look to write a more detailed answer, as you have given more information here than the other post did. Think about the lowest granularity of data before deciding what your fact or dimension tables are. Once you understand the lowest granularity, the facts and dimensions become more obvious. — Jo Douglass
Thank you your answer Joe, it indeed seems to be the same data set. I referred to the grain of the data in my comment in the post below this. — Plump Worm

Jon Hanlon Jon Hanlon · Accepted Answer · 2017-01-17T01:40:20

Use a Bridge table. Bridge tables are used when you have a many-to-many relationship. See here http://www.kimballgroup.com/2012/02/design-tip-142-building-bridges/

DataWarehouse - Fact Tables with different granularity / Dimension measures

1 Answers