Add a relationship between tables

Contents[Hide]

1. Overview

Relationships allow you to work with data from multiple data sources 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.

Note
If there is no relationship defined, Dundas BI will guess the relationship when you use unrelated data in a metric set. You can then modify how the data was joined as shown in Automatic joins and hierarchies.

2. Review existing relationships

Go to the Explore window, right-click on a table and choose Relationships.

Select Relationships from the menu
Select Relationships from the menu

Note
For data cubes, you cannot define a relationship before combining its data with other data, but you can edit the automatic join afterward.

In the Relationships dialog, you will see a diagram displaying the existing relationships for that data structure.

Relationship between two tables
Relationship between two tables

Hover over each node to view details about the relationship in a tooltip.

Hover over a node to view details
Hover over a node to view details

If needed, to view the relationships as a list, click List view. The Category column displaying in list view 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 prior to version 7.
  • User-defined relationships were created by a Dundas BI user.

Return to Diagram view, then right-click a node and click Edit to see its details.

Note
Native relationships can only be modified in the source database.

3. Create a relationship

By creating a relationship ahead of time, you can define how two tables are related rather than allow Dundas BI to choose for you when you or others drag data from those tables onto one metric set.

Start in the Relationships dialog opened for one of the tables as described above, and click Add relationship. The Set Up a Relationship dialog is displayed.

Enter a Name for the relationship.

Add a relationship between category and subcategory
Add a relationship between category and subcategory

Under Target Structure, find the data connector containing related data and select the related table. This data can be from a different data source.

Match elements from each structure by dragging one from the left under Source Elements to the equivalent under Referenced Elements.

Match category relationship elements
Match category relationship elements

Click the submit button at the bottom of the dialog to add the relationship.

The new relationship will now be add to the diagram in the Relationships dialog as a user-defined relationship.

A user-defined relationship is added to the diagram
A user-defined relationship is added to the diagram

4. Using relationships

Relationships are used when selecting data from different tables and structures 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.

Creating a table with categories from a database
Creating a table with categories from a database

Drag a column from an Excel sheet to Rows in the Data Analysis Panel.

Adding Excel subcategories to the table
Adding Excel subcategories to the table

Drag a column containing additional details from a different Excel data connector.

Add Excel details to the table
Add Excel details to the table

In the background, Dundas BI created automatic joins between the three tables based on the created relationships. The Automatic joins and hierarchies article shows you how you can view and edit the automatic joins, along with more examples of combining data.

You can also 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.

Resulting data cube
Resulting data cube

5. See also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri