Data Modelling Multiple Fact Tables

Power BI Data Model
1

Hi

I have a data model where i have 4 fact tables with transactions

Incident Data, Injury Data, Safety Rules data and cause analysis data. The incident fact table has hierarchy over the injury table, it is linked by the form number and injury classification, the cause analysis and safety rules table can link to both the incident and injury tables.

Some of the supporting tables can relate to all 4 fact tables and others are specific to a particular table for example the division, region, branch tables support all 4 fact tables but the injury classification, injury type supporting tables only relate to the injury tables.

My current data model looks like this there is one fact table missing which i have yet to add in, it largely works in terms with being able run calculations but i feel i can simplify the model to optimise the performance.

Current model design

image
image.png1247×479 41.9 KB

i have drawn a rough sketch on how i think i can simplify the model, what i am unsure of is whether i need to link the supporting tables that are currently only linked to the Incident table to also link to the Injury, cause and safety tables. And will my 1 to many relationship from the incident table to the injury table an the incident to the cause analysis table and the safety tables work, these are 1 to many linked by a unique form number.

Sketch

image
image.jpg1960×4032 1.94 MB

i’ve read the other forums on this subject and i think im close but would like some feedback from the forum experts

Thanks

2

Hi @AoakeP,

Filter flow through one-to-many directions by default as you are expecting. So, as per your sketch, if you will apply filter on Division/Region etc, it will flow through to other fact tables through incident form transaction table.

Do you have any other specific question which I have missed?

3

Thank you yes that is what i was wanting confirmed and i have tested it and it works in hindsight i should have tested it first .

1 Like
4

Glad that it worked for you. By the way, it seems good optimization of model :slight_smile:

5

Thank you its taken me a few rebuilds to get it to this point :smile:)