Relationships and Tables

A data mashup requires users to connect the various data sets they have (coming from one or more data sources) in order to build a singular framework that allows them to cross-analyze the data from the various data points. To do this, users must define logical relationships (joins) between the data sets. This provides the modeling tools with instructions on how the data sets fit together, making the data discovery process simpler and more meaningful.

Note that unlike join operations used in the Data Flow portion of the ETL, the joins that are defined in the Data Model component will not be added to the schema of the source database. Rather, they are only used in the querying process.

Defining Relationships

Pyramid uses heuristics to automatically determine the relationships between tables. The heuristic model can be changed from the ribbon; open the Auto Relationship ribbon to select a heuristic model.

None: remove all joins.

Primary key is the first column: add joins based on the primary key columns, assuming the primary key is the first column.

Match by table name: add joins based on the table names.

Match by column name: add joins based on the column names.

Edit Relationships

  • You can edit the relationships where necessary by clicking on the join
  • From the Relationship panel, add and edit the join columns and determine if a join should be bi-directional
  • You can also right click on a join to delete it or change the direction.
  • From the Auto Relationship button in the ribbon, you can determine the logic behind how the tables should be joined
  • Clicking the preview button on the far right of the table header will also show you a preview of the table's rendered data.

Edit Joins

  • To edit a join, click on it and choose the required join type
  • When a join is selected, the Properties panel enables you to:
    • Change the join type
    • Make the join bidirectional
    • Add and edit join columns
  • In the Properties panel, under Join Columns, the left table appears on the left-hand side, and the right table on the right.
  • Right click on a join to change the join direction, or to delete the join
  • From the Auto Relationship menu in the ribbon you can:
    • disable auto relationship
    • set the primary as the first column
    • match the tables by table name
    • match by column name