Automatic joins and hierarchies

Contents[Hide]

1. Overview

This article builds upon earlier walkthroughs in order to show a couple of features of Dundas BI: automatic join capability and user hierarchies.

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 columns between different tables.

A user hierarchy is a multi-level hierarchy that you define based on a data source, or the system can define for you automatically based on existing data relationships. Examples include a 'Product' or 'Geography' hierarchy. For more details about hierarchies, see Understanding the Dundas BI data model.

The Adventure Works for SQL Server 2012 database is used as an example.

Related video: Creating Custom Categorical Hierarchies

2. Automatic joins

This walkthrough begins with the table visualization from the View data with a chart and drill down article, which displays OrderQty as a measure, and a modified date-time hierarchy on the ROWS axis of a table visualization.

Table visualization
Table visualization

2.1. Display ProductID on COLUMNS

Click the EXPLORE window. Locate your data connector and expand it.

Find the native table [Sales].[SalesOrderDetail] and expand it as well.

Drag the ProductID column and hover over the table visualization on the canvas. The Data Binding (Metric Set) panel appears automatically beside the table. Drop the ProductID column onto the COLUMNS field of the panel.

Drag Product ID onto columns
Drag Product ID onto columns

The table visualization is reconfigured to additionally display product ID values (e.g. 707, 708, etc.) along the columns axis.

Table shows product IDs on columns axis
Table shows product IDs on 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 table's Name column, hover over the table visualization, and then drop the Name column onto the COLUMNS field of the Data Binding panel (replacing the ProductID column which was already there).

Drag Product Name onto COLUMNS
Drag Product Name onto COLUMNS

The table visualization is reconfigured automatically to show product names on the columns axis instead of product IDs.

Table shows product names on columns axis
Table shows product names on columns axis

Behind the scenes, Dundas BI performs an automatic join between the SalesOrderDetail and Product tables based on their common column, ProductID.

3. Hierarchies

3.1. Create a Product hierarchy

Use the main menu to create a new user hierarchy.

Create a new hierarchy
Create a new hierarchy

This opens the Hierarchy Designer screen which lets you define a hierarchy by dragging items from the EXPLORE window. 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.

Tip
You can also drag a data cube or one of its elements to the Hierarchy Designer canvas.

Drag Product table to canvas
Drag Product table to canvas

The Hierarchy Structure Explorer and Hierarchy Preview Explorer screens are displayed.

Hierarchy Structure Explorer and Hierarchy Preview Explorer
Hierarchy Structure Explorer and Hierarchy Preview Explorer

The Structure Explorer lets you define or modify the levels of your hierarchy. The Preview Explorer lets you preview the resulting hierarchy as a set of members arranged in a treeview. 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 (such as foreign key) between the Product table and other tables in the database and constructs this multi-level hierarchy for you automatically. 

Tip
In some situations, you may want to turn off this automatic hierarchy generation functionality so that you can define the levels yourself. To do this, press and hold the SHIFT key while dragging the Product table, for example, to the Hierarchy Designer. Only a single level will be created in this case (i.e., the Product level).

Even though the hierarchy is auto-generated, you have the option of customizing it from the Structure Explorer. For example, you can change the name of the hierarchy or a hierarchy level as shown in the figure below.

Change the name of a hierarchy level
Change the name of a hierarchy level

Once you are done with the changes, check in your hierarchy from the toolbar so others can use it too.

3.2. Replace Name column with Product hierarchy

Next, go back to the example dashboard with a table visualization (showing OrderQty as measure, time dimension hierarchy on ROWS, and product name on COLUMNS).

From the EXPLORE window, drag the hierarchy you just created, hover over the table visualization, and drop the hierarchy onto the COLUMNS field of the Data Binding panel (replacing the Name column which was already there). You can drag either the item named Hierarchy1 or Product.

Drag Product hierarchy to COLUMNS
Drag Product hierarchy to COLUMNS

The columns axis of the table visualization now shows product categories which you can expand (in View mode) to see product subcategories and then products.

Expandable product members
Expandable product members

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