Automatic joins and hierarchies
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. 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
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.
In the EXPLORE panel, expand your data connector. Find the table [Sales].[SalesOrderDetail] and expand it as well.
Drag the ProductID column onto the COLUMNS field of the Data Analysis Panel.
The table visualization is reconfigured to additionally display Product ID values 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 the COLUMNS field of 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.
3.1. Create a Product hierarchy
Use the main menu to create a new user hierarchy.
This opens the Hierarchy Designer screen, which lets you define a hierarchy by dragging items from the EXPLORE panel. As an example, expand your data connector in the EXPLORE panel and find the [Production].[Product] table. Drag the table (or one of its columns) to the canvas.
The Hierarchy Structure Explorer and Hierarchy Preview Explorer screens are displayed.
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 between the Product table and other tables in the database and constructs this multi-level hierarchy.
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.
3.2. Apply the Product hierarchy to Columns
Next, go back to the dashboard created in Section 2.1.
From the EXPLORE panel, drag the hierarchy you created onto the COLUMNS field of the Data Analysis Panel, replacing the ProductID column.
The Columns axis of the table visualization now shows product categories, which you can expand (in View mode) to see product subcategories and 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.
4. See also
- Video: Creating Custom Categorical Hierarchies
- View data with a chart and drill down
- Add a relationship between tables