Relationships
A relationship is the link between a fact table and a dimension group. It tells the system how to join the dimension group to the fact table when SQL is generated for measures and data marts. Relationships of the current project version are listed on the Relationships tab of the RMD screen.
The two sides of a relationship
| Side | Where it lives | Description |
|---|---|---|
| Key in fact table (foreign key) | On the relationship itself | The physical column of the fact table that stores the reference to the dimension group. Each relationship has its own foreign key |
| Key in dimension group (primary key) | On the dimension group | The physical column that uniquely identifies a row of the dimension group. It is a property of the dimension group and is shared by every relationship that uses that group |
Both sides are expressed as a physical database location: connection / table (or schema.table) / column.
The relationship type is always Many-to-one: many rows of the fact table refer to one row of the dimension group. There is no other type and the type cannot be changed.
How a relationship is created
There is no separate "add relationship" form. A relationship is created when a dimension group is attached to a fact table:
- On the Configuration tab of a fact table, add the dimension group to the canvas and save. One relationship is created for that fact table and that dimension group, initially with no foreign key
- Populating a fact table proposes the dimension groups that match the base database table; saving the result creates the same relationships
Removing the dimension group from the fact table deletes the relationship and invalidates the data marts that depend on it.
Setting the keys
The keys are mapped on the Connections layer of the fact table configuration. Each attached dimension group produces two key nodes:
- A foreign key node inside the fact table block. Connecting it to a database column sets the Key in fact table of the relationship
- A primary key node inside the dimension group block. Connecting it to a database column sets the Key in dimension group of the dimension group
The tooltips on those nodes explain their purpose:
- Primary key: "Auto-generated primary key. This key should be linked to the primary key of the connected dimension table to ensure proper SQL script generation for fact tables and data marts"
- Foreign key: "Auto-generated foreign key. This key should be linked to the dimension table's foreign key in the connected fact table to ensure proper SQL script generation for fact tables and data marts"
When a key is not mapped, or the column it points at is unavailable, the key node carries a warning:
| Message | Meaning |
|---|---|
| "Connected data source field in the RMD is not configured" | No database column is mapped to the key |
| "Connected data source field in the RMD references a database field that cannot be found in configured database connections" | The mapped column no longer exists in any configured connection |
| "Connected data source field in the RMD references a database field from a database connection that is currently inactive" | The connection holding the mapped column is inactive |
A relationship without a foreign key is kept, but SQL generation for measures and data marts that need the join fails until the key is set.
The Relationships tab
The tab lists every relationship of the current project version. Switching the project or the project version reloads the list.
| Column | Description |
|---|---|
| Fact table | The fact table side of the relationship |
| Dimension group | The dimension group side of the relationship |
| Key in fact table | The foreign key, as connection / table / column. Empty when it is not mapped |
| Key in dimension group | The primary key of the dimension group, as connection / table / column |
| Relationship type | Always Many-to-one |
| Actions | Edit and delete |
Working with the list:
- Each column can be sorted ascending and descending
- Each column has a search field; Relationship type is filtered by selecting a value from a list
- By default the list is ordered by fact table name and then by dimension group name
- When there are no relationships, the tab shows "No relationships found"
Editing a relationship
The Edit action opens the Edit relationship dialog. Only one field is editable.
| Field | Editable | Description |
|---|---|---|
| Fact table | No | Shown for reference |
| Dimension group | No | Shown for reference |
| Key in fact table | Yes | Choose the column in three steps: connection, then table, then column. The value can be cleared |
| Key in dimension group | No | Belongs to the dimension group and is changed on the Connections layer |
| Relationship type | No | Always Many-to-one |
Save becomes active only after the foreign key actually changes. On success the notification "Relationship updated" is shown.
Deleting a relationship
The Delete action asks for confirmation: "Are you sure you want to delete this relationship? This will affect the fact table and invalidate all data marts and measures that depend on this relationship". After confirmation the link between the fact table and the dimension group is removed and the notification "Relationship deleted" is shown. The fact table, the dimension group and its dimensions are not changed.
Access rights
- All roles can view the Relationships tab
- Editing and deleting require the Developer access level or above in the project (Developer, Administrator, Super administrator) and a valid license. For the Analyst and Viewer roles the tab is read-only and the action buttons are not shown
Limitations:
- Relationships cannot be created or duplicated from this tab, and they have no name or description
- The relationship type cannot be changed
- The fact table, the dimension group and the dimension group key cannot be changed from the Edit relationship dialog