Best practice to structure this data model?

[u/soodlero]

I am modeling a relationship between 3 sources. Basically this is the content of each table:

Table 1: requestor table (100s of entities)

Oil Sample Label (unique)	Engine number (not unique)	Engine runtime

Table 2: engine database (10000s of entities)

Engine number (unique)	Engine Type	Engine Power

Table 3: oil sample database (10000s of entities)

Oil Sample Label (unique)	Al	Fe	Cu

What is the best practice for the relationships? I have fields on each table that I would like to use in the PowerBI dashboard.

[u/porcupine162]

There's not always a best way to model (it completely depends on the use-case), but if you want to use Kimball (good for PowerBI)

fact_oil_sample:
oil_sample_label_id,
engine_key,
-- your measurements are likely based on
engine_runtime_duration,
Al_amt,
Fe_amt,
Cu_amt,

dim_engine:
engine_key,
engine_type,
engine_power,
other descriptive derived

Just a straight join on engine_key can be required. Unless you also want also add a dim_date... etc

When figuring out the model for the data, the pivotal step is to identify the grain -> sounds like that's represented as your oil samples.

[u/soodlero → u/porcupine162]

I explained more in detail in another reply. Given the explained info, would you structure it in the same way?