Add a relationship between tables

Contents[Hide]

1. Overview

If your database tables do not have existing native relationships (e.g., foreign key) defined, you can use the Relationships dialog to add user-defined relationships. Relationships can also be defined between data cubes.

Once the user-defined relationships are in place, it becomes much easier to create a new hierarchy in Dundas BI based on the now-related tables. Relationships also facilitate automatic join functionality in Dundas BI. For example, with relationships defined, an Excel file can be automatically joined with a SQL table even if the matching columns have different names. Two data cubes can also be automatically joined.

If your database tables do have existing native relationships defined, you can use the Relationships dialog to review them (but not edit). 

2. Review existing native relationships

A native relationship is an existing relationship from your database, such as a foreign key relationship.

Go to the EXPLORE window, expand your data connector, and right-click (or long-tap) on a native database table which you know has existing relationships defined. From the menu, select Relationships.

Select Relationships from the menu
Select Relationships from the menu

In the Relationships dialog, you'll see a list of the existing relationships for your database table. The Category column indicates whether the corresponding relationship is a native one or user-defined.

Click the Edit icon beside a relationship to see its details.

Native relationship between two tables that matches ComponentID to ProductID
Native relationship between two tables that matches ComponentID to ProductID

3. Create a user-defined relationship

The following example shows how to create a user-defined relationship between a source table and a target table.

First, go to the EXPLORE window, expand your data connector, and right-click (or long-tap) over the source table (e.g., Products).

Select Relationships from the menu
Select Relationships from the menu

In the Relationships dialog, click Add Relationship. The Set up a Relationship dialog is displayed.

There are 3 steps to setting up a relationship:

  1. Enter a name for the relationship.
  2. Expand the Target Structure treeview and locate the target table (e.g., ProductGroup).
  3. Match columns from both tables by dragging a column from the source table onto a column from the target table (i.e., Referenced Elements).

Set up the user-defined relationship
Set up the user-defined relationship

Click Submit to add the relationship.

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

4. Using relationships

From the main menu, click New, and then click Hierarchy.

Locate your source table (from the relationship) in the EXPLORE window and drag it to the canvas.

Drag your source table to the canvas
Drag your source table to the canvas

A two-level hierarchy is defined for you automatically because of the relationship between the Products and ProductGroup tables. You didn't even need to specify the second table (ProductGroup).

Hierarchy is automatically defined using the relationship
Hierarchy is automatically defined using the relationship

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: 7am-6pm, ET, Mon-Fri