Automatic joins and hierarchies
This article demonstrates how to use automatic joining and user hierarchies to combine related data together.
In keeping with the self-serve paradigm in Dundas BI, as you drag-and-drop items from the Explore window to the canvas, some things are done automatically to save you time, such as joining together columns from different tables.
A hierarchy can be set up ahead of time to define various settings you can apply to a matching column of data, including optional multiple levels and additional details made available as attributes. You can define one based on a data source or data cube, or the system can define one for you automatically based on existing data relationships. For more details about hierarchies, see Understanding the Dundas BI data model.
The Adventure Works 2014 database is used as an example.
Related video: Creating Custom Categorical Hierarchies
This example begins with a table visualization like the one in the View data with a chart and drill down article, which displays OrderQty as a measure, and a date-time hierarchy on the Rows axis.
In the Explore window, expand the data connector to find the table [Sales].[SalesOrderDetail], and expand it as well.
Drag the ProductID column onto Columns in the Data Analysis Panel.
The table visualization is reconfigured to additionally display Product IDs along the columns axis.
2.2. Replace ProductID with Name
Since showing product ID values is not very meaningful, find the [Production].[Product] table in the Explore window and expand it. Drag the Name column onto Columns in the Data Analysis Panel, replacing the ProductID column.
The table visualization is reconfigured to show product names on the columns axis instead of product IDs (in alphabetical order, by default).
Behind the scenes, Dundas BI identified a relationship automatically and performed a join between the SalesOrderDetail and Product tables based on their common column, ProductID. You can also define a relationship yourself ahead of time to ensure this join works as intended, as described in the article Add a relationship between tables.
You could choose to drag additional data from this table to take advantage of auto-joining, for example by dragging Color to Slicers in the Data Analysis Panel.
If the automatic join is not as expected, you can edit the join by using the Data Flow button on the Data Analysis Panel while editing a metric set full screen.
2.3. Edit automatic joins
While editing your metric set full screen, click the Data Flow button to view the existing relationships powering the metric set as a diagram. Each node represents an individual table and any additional relationships that are not used are displayed as gray nodes which can be added to the visualization as a join if required.
View the relationship details by right-clicking one of the nodes and selecting Relationship Details.
The relationships window lists the columns of the two input tables and includes settings to determine how they are joined.
The relationship between the two tables was already defined and the key elements are set, as indicated by a key icon shown next to the Country column.
To define a key binding, drag a column from one input table and drop it onto the corresponding column in the the other input table.
Choose the join type to determine which records should be included.
- Inner Join - Only records with matching key values in both input tables.
- Left Join - Only records with a key value in Left Input.
- Right Join - Only records with a key value in Right Input.
- Full Join - All original records will be included.
To add new table relationships, drag a table from the Explore window. This will add a new relationship to the existing relationships and is represented as a new node in the diagram.
To add a table to the visualization, right-click a grey node and select Add to Visualization.
You can right-click a grey node and select Expand to view that table's relationships. The relationships are represented by additional nodes added to the diagram.
Click the Data Flow button to return to the metric set settings on the Data Analysis Panel.
Now we will create a hierarchy to allow us to do more with this column of products.
3.1. Create a Product hierarchy
Use the main menu to create a new user hierarchy, and choose a Standard hierarchy.
This opens the Hierarchy editor, which lets you define a hierarchy by dragging items from the Explore window or a data file from your desktop.
As an example, expand your data connector in the Explore window and find the [Production].[Product] table. Drag the table (or one of its columns) to the canvas.
An automatically-defined hierarchy Structure is displayed with a Preview to the right.
Under Structure, you can select a level to modify it, or click to add a new level in the toolbar and it will appear here. The Preview lets you explore the resulting hierarchy as a set of members arranged in a tree view. The name of your new hierarchy is displayed in the status bar at the bottom.
In this example, three levels of a Product hierarchy have been automatically defined by Dundas BI. When you drag the Product table to the canvas, Dundas BI looks at existing relationships between the Product table and other tables and constructs this multi-level hierarchy.
Even if the hierarchy is auto-generated, you have the option of customizing it by selecting a level and using the options in the Configure Hierarchy Level popup.
In this example, ProductID was automatically set as the Member Key Source of the Product level, which is what will allow us to use this hierarchy with our original ProductID column.
3.2. Replace ProductID with Product hierarchy
Next, go back to the metric set from Section 2.
Instead of replacing the product IDs with a simple Name column, we will replace it with our new hierarchy: remove Name from under Columns and re-add ProductID in the Data Analysis Panel.
From the Explore window, drag the hierarchy you created onto ProductID to replace it.
The Columns axis of the table visualization now shows product categories, which you can expand to see product subcategories and expand again to see products. Since the Name column was indicated as the Member Caption Source for the hierarchy, the captions are taken from the Name column of each hierarchy level.
Attributes are optional additional details from your hierarchy level's data source that automatically become available to add to the metric set once the hierarchy is used. Attributes can describe the members of a hierarchy level, like the color or size of each product in the Product level of the hierarchy.
4.1. Add attributes
To add attributes to our Product hierarchy, re-open it from the main menu. For this example, click to select the Product level to open its details.
Click Add Attribute in the Configure Hierarchy Level popup for each attribute that you want to add, and under Attributes, choose one of the columns from this level's data source. From our example data source, you can choose Color, Class, Size, Style, and others.
4.2. Select attributes
Navigate back to the metric set to select the attributes.
To have a better look at our products, we can click the x button next to ModifiedDate in the Data Analysis Panel to remove it, then drag ProductID from Columns to Rows in the Data Analysis Panel.
Next, we can click to add an attribute of the Product hierarchy.
Select an attribute from the list. Alternatively, you can find these attributes in the Attributes folder under the hierarchy in the Explore window, which you can then drag to add.
Since we added attributes to the Product level in our example, click to expand or right-click to change the level down to Product to see these product details we added as attributes.
Unlike regular hierarchies, attributes are known to describe the values of the hierarchy, so totals and expander buttons are unnecessary and will be left out automatically for the attribute when selected after your hierarchy.
If you choose to, you can also place an attribute before its hierarchy, or on its own. In this case, totals and expanding/collapsing work as they normally do for other hierarchies. The figure below shows the result of dragging to re-order Product Color first and displaying the Product level of the hierarchy afterward.
5. See also
- Video: Creating Custom Categorical Hierarchies
- Metric set analysis tools
- View data with a chart and drill down
- Add a relationship between tables
- Create a ragged hierarchy
- How to create a custom user hierarchy
- Transform: Process Result