Add a relationship between tables
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.
2. Data preparation
To follow the examples in this article, you need three data connectors:
- The Adventure Works 2014 database.
- An Excel table containing the ProductSubcategoryID, ProductCategoryID, and Name columns from the [Production].[ProductSubcategory] table of the Adventure Works database.
- 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.
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.
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.
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.
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.
5. Using relationships
From the main menu, click New, and then click Dashboard.
Drag the Name column from [Production].[ProductCategory] to the canvas.
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.
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.
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.