Relationship Optionality
Relationship optionality is the property of a SqlJoin which describes the Optional or Mandatory presence of a
row in the target table.
For instance, let's take the following example schema :
Sample schema
There is a join from a source table Sales to a target table Products.
This join is made along the date and product fields.
This join is a many-to-one because we can sell the same product several times per day.
Relationship Optional
The relationship is Optional if there could be sales on products which are missing from Products table.
The relation is many-to-(one or zero). The join is "broken".
On this example the unit price of P3 at 2024-01-01 is missing from the Products table :
Sales
| date | product | quantity |
|---|---|---|
| 2024-01-01 | P1 | 10 |
| 2024-01-01 | P2 | 20 |
| 2024-01-01 | P3 | 30 |
Products
| date | product | unit_price |
|---|---|---|
| 2024-01-01 | P1 | 5.0 |
| 2024-01-01 | P2 | 2.0 |
Relationship Mandatory
You can describe the target end of the join as Mandatory, if you are certain that at any point in time, the
relationship will always be satisfied.
All the rows in Sales will match a row in Products. The relation is many-to-one.
This tells nothing about the other hand of the join (Products table).
There could still be some (product,date) keys in the Products table which are not referenced in the Sales
table (orphans).
The Mandatory nature of the relationship could allow some optimizations in some DirectQuery connector features
like Incremental refresh.