Add a relationship between tables

Contents[Hide]

1. Overview

Dundas BI relies on a relational database structure to enable functionality such as automatic joins and user hierarchies. You can use the Relationships dialog to view the native (i.e. defined by the source database) and system-defined relationships between the tables in your database, or create user-defined ones.

Once the relationships are in place, it becomes much easier to create a new hierarchy 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. Similarly, with defined relationships between two data cubes, the cubes can be automatically joined.

Note
To maintain the functionality when a relationship is not defined, Dundas BI will guess and create a system-defined relationship. Unlike native relationships, you can modify a system-defined relationship from the Relationships dialog.

2. Data preparation

To follow the examples in this article, you need three data connectors:

  1. The Adventure Works 2014 database.
  2. An Excel table containing the ProductSubcategoryID, ProductCategoryID, and Name columns from the [Production].[ProductSubcategory] table of the Adventure Works database.
  3. An Excel table containing the ProductID, Name, and ProductSubcategoryID columns from the [Production].[Product] table of the Adventure Works database.

3. Review existing relationships

From the Explore panel, right-click on a database table or data cube. From the menu, select Relationships.

Select Relationships from the menu
Select Relationships from the menu

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

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

Native relationship between two tables
Native relationship between two tables

Note
You can modify Native relationships only in the source database.

4. Create a relationship

In the Relationships dialog for the [Production].[ProductCategory] table of the Adventure Works database, 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

Expand the Target Structure tree view and select the Excel table ProductSubcategory. Then, match elements by dragging the ProductCategoryID Source Element onto the ProductCategoryID Referenced Element.

Match category relationship elements
Match category relationship elements

Click Submit to add the relationship and close the Relationships dialog.

Open the Relationships dialog for the ProductSubcategory Excel table. You will see the user defined relationship created above.

Click Add Relationship.

Set up a relationship with the Excel Product table based on the ProductSubcategoryID element.

Add a relationship between subcategory and product
Add a relationship between subcategory and product

5. Using relationships

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

Drag the Name column from [Production].[ProductCategory] to the canvas.

Create a table with the category name
Create a table with the category name

Rename the caption to Category for convenience.

Drag the Name column from the Excel Subcategory table to the ROWS field of the Data Analysis Panel.

Add the subcategory name to the table
Add the subcategory name to the table

Rename the caption to Subcategory for convenience.

Drag the Name column from the Excel Product table to the ROWS field of the Data Analysis Panel.

Add the product name to the table
Add the product name to the table

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, and then promoting the source data cube.

Resulting data cube
Resulting data cube

6. 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