Add a relationship between tables
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.
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.
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).
In the Relationships dialog, click Add Relationship. The Set up a Relationship dialog is displayed.
There are 3 steps to setting up a relationship:
- Enter a name for the relationship.
- Expand the Target Structure treeview and locate the target table (e.g., ProductGroup).
- Match columns from both tables by dragging a column from the source table onto a column from the target table (i.e., Referenced Elements).
Click Submit to add the relationship.
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.
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).