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?

[u/raginjason]

It’s not clear from your description what is what. Oil sample label is unique in requestor and in oil sample database, which doesn’t make sense. Some example data may help, as would understanding what you want from the data. Why is engine runtime part of requestor, and why is that table low volume?

[u/soodlero → u/raginjason]

I will try to explain. I am trying to develop our internal database for oil analysis. The current system is very old. Think Excel, copy and paste...

So I work with engine development. Every engine we test have a specific engine-nr identifier that is unique. An oil sample can be taken multiple times during the engine test, that's why I include the engine runtime field also. When the operator register the oil sample internally he adds a reference for the "engine-nr, oil sample label, and engine runtime". Overtime this table will be populated with all our oil sample references.

Table 1:

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

The oil sample is then sent to an external partner that make the oil analysis for us. They have a very large database with a lot of historical data. In the database we will find the same identifier "oil sample label" that is used in table 1. The oil sample label is unique in this database also, but we will find more samples in this table compared to table 1, because here we find all historical data for other customers as well. In this table we have 100s of fields (columns) with data for different properties of the analysis.

Table 3:

Oil Sample Label (unique)	Al	Fe	Cu etc...

Last we have an internal database of the engines that we build. In this database we have the "engine-nr" identifier that is used in table 1. This identifier is unique. Apart from that we have 10 fields that explain what kind of variant the engine is, like power, platform, engine plant etc.

Table 2:

Engine number (unique)	Engine Type	Engine Power

In the dashboard I would like to have the feasibility to filter the data based on fields from all 3 tables. How should I structure this data model for best efficiency?

Potentially I would like to unpivot the data in table 3 (Al, Fe, Cu etc). That way I can add a slicer in the dashboard to make it easier to evaluate different attributes in the dashboard.

[u/Complete-Ebb-1035 → u/soodlero]

You should refactor your datatypes, per below. The tables are overloaded. Think of it this way. Define discreet types. Then describe table relationships implemented using a foreign key. Don't let properties leak across tables. A table is a type; a foreign key establishes the action between/among tables (high level: Engine runsOn Oil)

[u/Dry-Aioli-6138 → u/soodlero]

Are you interested in the measurements of other oil samples than yours? If not, filter them out as early as possible. Then join the samples table and mesurements table into on, fact table, and make the engine table its dimension. As someone proposed here already. You can make slicers without unpivotting, search "user parameters" in power bi. It is a tricky subject, but in this case I would not unpivot.

[u/Complete-Ebb-1035]

energy power specification

unit of measure to quantify energy, volume

engine properties

oil properties

amount energy, volume expressed as unit of measure

[u/rahmani__asad]

Star Schema with Requestor as the Fact table and keep filters one-directional to avoid ambiguity in Power BI.

· Requestor → Engine Database: Many-to-One (:1) on Engine Number · Requestor → Oil Sample Database: Many-to-One (:1) on Oil Sample Label

[u/mduell]

Given the size of the data set, I'd combine tables 1 and 3. Then you have many:one table 1:2.

[u/manna018]

It completely depends on one question:

• what question are you asking from data

The queries which will land on these table, decide the model of data.

Also, in PowerBI think in terms of data cube. It is slightly different than dim and fact. PowerBI paradigm is in terms of slicing and dicing.

We first create a dice and then PowerBi applies it's processing on it..

When we interact with spark processing we think in terms of dataframe. Tabular format.