Add a relationship between tables
Relationships allow you to work with data from multiple data sources or cubes as if they had already been prepared into a single data cube for you. For example, with relationships defined, data from an Excel file and a SQL table can be selected together and automatically joined, even if the matching columns have different names.
You can use the Relationships dialog to review existing relationships and add new ones. These are used when selecting data in a metric set, or when creating a multi-level hierarchy.
2. Review existing relationships
Go to the Explore window, right-click on a table or cube, and choose Relationships.
In the Relationships dialog, you will see the list of existing relationships for that data structure. The Category column indicates how each relationship was created:
- Native relationships are defined in the database (e.g., foreign keys).
- System-defined relationships were created automatically by Dundas BI.
- User-defined relationships were created by a Dundas BI user.
Click the Edit icon beside a relationship to see its details.
3. Create a relationship
In the Relationships dialog for one table or data structure, click Add Relationship. The Set Up a Relationship dialog is displayed.
Enter a Name for the relationship.
Under Target Structure, find the data connector or cube containing related data and select the related data structure (e.g., table or cube).
Match elements from each structure by dragging one from the left under Source Elements to the equivalent under Referenced Elements.
Click the submit button at the bottom of the dialog to add the relationship.
The new relationship will now be listed in the Relationships dialog for both data structures as a user-defined relationship.
4. Using relationships
Relationships are used when selecting data from different data sources or cubes in a metric set, whether in the full-screen metric set editor or when working with metric sets on a dashboard or another view. They are also used when creating a hierarchy that contains multiple levels.
To demonstrate, we will create a single metric set as shown below based on a database table and two Excel spreadsheet files, after relationships have been defined.
In a new dashboard, drag a column from the table [Production].[ProductCategory] to the canvas.
Drag a column from an Excel sheet to Rows in the Data Analysis Panel.
Drag a column containing additional details from a different Excel data connector.
In the background, Dundas BI created automatic joins between the three tables based on the created relationships. You can view these joins by promoting the auto-generated metric set (if applicable), and then promoting the auto-generated source data cube behind the metric set. Edit this data cube (as a Developer user) to see the join transforms.
More examples of using relationships can be found in the Automatic joins and hierarchies article.