Relationships and Tables
What are Relationships?
Your model likely contains a large number of tables, many of which are related in some way. End users will want to create queries that return related data from different tables.
For instance, a retail company may have a table that lists products, product color, and product style, another table that lists brands and store locations, and another table that lists sales and net profit.
Say you want to create a list showing sales by product and brand; each of these columns is stored in a different table. To create the required list, there must be a logical relationship (a join) defined between all three of these tables.
These relationships are usually created by a matching a primary key column in one table (the 'owner side' of the join) to an identical foreign key column in another table (the 'inverse side' of the join). For example, the primary key column in the Products table may be 'Product Key'. If this same column exists as a foreign key in the Brands table and the Facts table, we can use it to create a join from Products to Brands and from Products to Facts.
Relationships Diagram
The relationship diagram displays all the tables in the data model, and the relationships between the tables. By default, Pyramid uses heuristics to define relationships according to each table's primary key column.
The direction of each join is indicated by the icons at each end of the join:
: indicates that the join comes from this table; this is the primary table in the join, as it contains the primary key column.
: indicates that the join goes to this table; this is the foreign table in the join, as it contains the foreign key column.
You'll notice that Pyramid automatically adds joins to the model; this is done via heuristic algorithms called 'auto-relationships'. The heuristic model can be changed (you can select from three algorithms) or disabled, and joins can be deleted, added, or edited as required.
- Click here to learn about the heuristic models used for defining auto-relationships.
- Click here to learn more about joins, including join types, and adding, editing, and deleting joins.
- Click here to learn about join key columns.
Navigate the Relationship Diagram
Each table in the model is displayed as a widget in the diagram. Each table widget lists the columns in that table. The relationships between the tables are represented by the lines connecting them.
From the ribbon, you can use the tools grouped under Diagram (red highlight below) to rearrange the relationship diagram.
- Rearrange: if you've moved any tables around on the diagram, the Rearrange function will restore them to their original position on the canvas.
- Scale to Fit: scale the relationship diagram down to fit on the canvas.
- Expand All: expand all tables in the diagram.
- Collapse All: collapse all tables in the diagram.
- Connector Type: select the type of line that will connect joined tables. The default connector type is 'path', but can be changed to 'direct' (straight lines).
- Validate Joins: ensure that the joins are valid.
- Delete: delete the selected join.
Table Properties
Click on a widget's borders to show the properties for that table in the Properties panel (green arrow below).
- Click here to learn about table properties and editing table metadata.
Column Properties
Click a column to show its properties in the Properties panel (green arrow below).
- Click here to learn about columns.
Resize Tables
To resize a table, click on it, then use the small white boxes that appear (see image above) to resize as needed.
Reverse Order
Toggle the table widget's arrows (red arrow below) to reverse the order of the columns.
Collapse and Expand
Toggle the expand/ collapse button (green arrow below) to collapse and expand the table.
Preview
Click a table widget's preview button (blue arrow below) to see a preview of that table in the Preview panel.
Joins
Left click on a join to open the Join Type context menu (red highlight below), and to simultaneously show the join in the Properties panel (green highlight below).
Left click on a join to delete it, switch the join direction, or validate the join.
- Click here to learn more about joins.