Add a relationship between tables
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 theRelationshipsdialog 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.
2. Review existing relationships
Go to theExplorewindow, right-click on a table and chooseRelationships.
In theRelationshipsdialog, you will see a diagram displaying the existing relationships between that table and other tables/data structures.
Hover over or long-tap a node (circle) to view details about how it's related in a tooltip.
For each table node in the diagram, you can right-click (or long-tap) and chooseExpandto add all of the additional tables related to that table to the diagram. This allows you to explore and discover other relevant data that can be made available by the intermediate tables.
If needed, to view the relationships as a list, clickList view. TheCategorycolumn displaying in list view indicates how each relationship was created:
- Nativerelationships are defined in the database (e.g., foreign keys).
- System-defined关系是由邓达斯自动创建的BI prior to version 7.
- User-definedrelationships were created by a Dundas BI user.
Return toDiagram view, then right-click a node and clickEditto see its details.
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 theRelationshipsdialog opened for one of the tables as described above, and clickAdd relationship. TheSet Up a Relationshipdialog is displayed.
Enter aNamefor the relationship.
UnderTarget 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 underSource Elementsto the equivalent underReferenced 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 theRelationshipsdialog as a user-defined relationship.
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 ahierarchythat 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(产品ion].[ProductCategory]to the canvas.
Drag a column from an Excel sheet toRowsin the Data Analysis Panel.
Drag a column containing additional details from a different Excel data connector.
In the background, Dundas BI created automatic joins between the three tables based on the created relationships. TheAutomatic joins and hierarchiesarticle shows you how you can view and edit the automatic joins, along with more examples of combining data.
You can also view these joins bypromoting the auto-generated metric setif applicable, and then promoting the auto-generated source data cube behind the metric set. Edit this data cube (as aDeveloper user) to see the join transforms.
