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.
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.
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.
Click a column to show its properties in the Properties panel (green arrow below).
- Click here to learn about columns.
To resize a table, click on it, then use the small white boxes that appear (see image above) to resize as needed.
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.
Click a table widget's preview button (blue arrow below) to see a preview of that table in the Preview panel.
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.
A bidirectional join is used to enable the data to flow in both directions: the data can then flow from the primary table to the foreign table, and from the foreign table to the primary table. This allows users to aggregate a dimension column by a measure. This is in contrast to a typical unidirectional join, where the measure is aggregated by the dimension.
Bidirectional joins are typically not needed, as the database tends to be built with the relationships in mind. Users should take care when adding bidirectional joins, as they affect the aggregation of measures in the query.
Many to Many Joins
When the relationship between the primary and foreign tables is "one-to-many" and doesn't validate due to duplications of unique keys it will replace the relationship to a many-to-many relation and as a result will be a valid connection (the user should understand his data and understand that it can be a many to many relationships).
Currently, this is the only usage of many to many joins, and combine it in the query engine in more meaningful ways.
- Click here to learn more about joins.