Create a metric set and add a filter

Contents[Hide]

1. Overview

This walkthrough shows you how to create and use metric sets in Dundas BI and apply filters.

Specifically, you will learn how to:

  • Create metric sets based on a data cube.
  • Visualize and re-visualize a metric set.
  • Display metric sets on a dashboard.
  • Add a member range filter to a dashboard.
  • Add a member filter to a dashboard.

This walkthrough is a continuation of Using a data cube to join tables.

Related video: Introduction to Metric Sets

2. Create a new metric set

From the main menu, click New, and then click Metric Set.

Create a metric set
Create a metric set

The Metric Set Designer screen is displayed.

2.1. Set up the metric set based on a data cube

From the EXPLORE window, locate and expand the data cube you created previously (e.g. DataCube1).

Expand the data cube's Measures folder and drag the OrderQty measure to the Metric Set Designer canvas.

Drag measure to canvas
Drag measure to canvas

By default, the measure is displayed in a table visualization beside a Metric Set panel. The table visualization shows a single aggregated value because no hierarchies have been specified yet. As you can see, you can visualize the metric set directly in the Metric Set Designer screen without having to create a dashboard.

Next, go to the EXPLORE window and expand your data cube's Dimensions folder. Drag the time dimension hierarchy Year > Half Year > Quarter > Month > Day to the ROWS field of the Metric Set panel.

Drag the Product hierarchy to the COLUMNS field of the Metric Set panel.

Drag hierarchies
Drag hierarchies

The table visualization beside the Data Binding panel is automatically updated to show 'Year' members along the rows, and 'Product Category' members along the columns. These members are all expandable.

Table shows expandable Year and Product Category members
Table shows expandable Year and Product Category members

Tip
Instead of dragging measures/hierarchies all the way from EXPLORE to a metric set region, use the click to add option to get a list of available elements to add.

Use the click to add option as an alternative to drag-and-drop
Use the click to add option as an alternative to drag-and-drop

Note
Starting in Version 3.0, it is now possible to drag measures/hierarchies from another data cube to a metric set region created from another data cube, assuming a relationship exists.

Drag hierarchies or measures from another data cube
Drag hierarchies or measures from another data cube

2.1.1. Analysing your data

Dundas BI comes with several tools to analyse data using a metric set. The default visualization is a Table, or data grid. Which displays the aggregated data points. For instance, data points that have a value that falls into the month of January 2005, will be aggregated into the January (2005) row, which will then be aggregated into the 2005 row.

2.1.2. Quick analysis

When making sense of the data, it is useful to get a sense of the data with some quick summary values. When selecting ranges of data, Dundas BI displays a set of summary information in the status bar.

Select rows in a table and get quick summary information
Select rows in a table and get quick summary information

2.1.3. Filtering & other analysis tools

You can quickly apply filters to data using the filter icon on the data panel.

Use the filter button to quickly filter data in a measure or hierarchy
Use the filter button to quickly filter data in a measure or hierarchy

When filtering data using multiple filters settings, they are applied in conjunction. For example, when filtering an Order quantity (a measure) between 100 and 200 and a date (hierarchy) between January and February, only data that has a cost between 100 and 200 AND is in January and February will be displayed.

The context menu contains several other analysis tools. It includes sorting, but also if the data contains multilevel hierarchies, you can use the drill-down and drill-up to focus on specific parts of the data, for instance moving from 2012 values, to January 2012 values. The level can also be change to increase or decrease the overall granularity of the displayed data, for example, going from viewing values by year, to viewing values by month.

The table can be re-visualized to change the visualization that is being used by this Metric Set. There are many visualizations to choose from so that the best way to communicate the important information contained in the data can be used. Bar, Line, Scatter and pie charts are available in several variations. This includes Range charts, Area charts, and Bubble Charts. In addition there are special use charts, such a Box Plots, Stock and Candlestick charts, as well as Waterfall and Pareto charts.

There are also several gauges, indicators, and diagram visualizations. Diagram visualizations include diagrams such as Maps, Tree Maps and Relationship Diagrams. These are detailed in further articles.

2.1.4. Complete metric set

To complete the setup of this metric set, go to the toolbar, click Re-Visualize, and then click Bar from the dropdown menu.

Re-visualize as Bar chart
Re-visualize as Bar chart

The table visualization is now changed to a bar chart that shows 'Year' values along the X axis and measure values on the Y axis. The bar chart also displays multiple series, where each series (i.e. bars of the same color) corresponds to a specific 'Product Category'.

Bar chart showing multiple series
Bar chart showing multiple series

Finally, check in your metric set from the toolbar so others can use it too.

2.2. Add a second metric set

Using similar steps as before, create a second metric set as follows:

  • Expand the data cube's Measures folder and drag the OrderQty measure to the Metric Set Designer canvas.
  • Expand the data cube's Dimensions folder and drag the FirstName hierarchy to the ROWS field of the Data Binding panel.
  • Drag the 'Product' hierarchy to the COLUMNS field of the Data Binding panel.

The resulting table visualization shows person names along the rows axis, and expandable product categories along the columns axis.

Table showing person names and product categories
Table showing person names and product categories

Go to the toolbar, click Re-Visualize, and then click Stacked Bar from the dropdown menu. The table visualization is changed to a stacked bar chart showing person names along the X axis and measure values on the Y axis. Each stacked bar series corresponds to a specific 'Product Category'.

Stacked bar chart
Stacked bar chart

Check in your metric set from the toolbar.

3. Display metric sets on a dashboard

To automatically create a new dashboard and put the current metric set on it, click the New Dashboard button.

Click the New Dashboard button to create a dashboard with this metric set to the dashboard
Click the New Dashboard button to create a dashboard with this metric set to the dashboard

Or, create a new dashboard (use the Blank template) from the main menu.

Go to the EXPLORE window and expand the Metric Sets folder.

Drag the two metric sets you just created to the dashboard canvas (e.g. MetricSet1 and MetricSet2). The two metric sets appear as a bar chart and a stacked bar chart, respectively. Observe that the chart types match the re-visualizations you selected in the Metric Set Designer previously.

Drag metric sets to dashboard canvas
Drag metric sets to dashboard canvas

4. Using filters on a dashboard

4.1. Add a member range filter

Go to the dashboard canvas and click on the first (Bar) chart in order to select it. From the toolbar, click Filter, and then click Member Range from the dropdown menu.

Member Range filter
Member Range filter

A Member Range filter control is added to the dashboard and its corresponding Filter Binding panel is displayed. From the panel, observe that the filter is already configured to filter the hierarchy along the X axis of the first chart (and metric set).

Filter Binding panel
Filter Binding panel

In the Filter Binding panel, set a default range for the filter by clicking the regions as shown in the above figure. For example, set the start of the range to 2006, and the end of the range to 2007.

Set start and end of range
Set start and end of range

The bar chart on the dashboard is automatically filtered to display data from 2006 to 2007.

Bar chart shows data from 2006 to 2007
Bar chart shows data from 2006 to 2007

To actually use the member range filter control, switch to View mode from the toolbar.

Using the member range filter control
Using the member range filter control

4.2. Add a member filter

Go to the dashboard canvas and select the second (Stacked Bar) chart. From the toolbar, click Filter, and then click Member from the dropdown menu.

A member filter control is added to the dashboard and the corresponding Filter Binding panel is displayed. The filter is already configured to filter on the FirstName hierarchy which appears on the X axis of the second chart.

You can choose the default members for this filter by clicking the region as shown in the figure below.

Member filter
Member filter

Expand the treeview and select the members you want.

Select members
Select members

The second (Stacked Bar) chart is automatically updated to show only the chosen members on the X axis.

Stacked bar chart displays chosen members
Stacked bar chart displays chosen members

To actually use the member filter control, switch to View mode from the toolbar.

Using the member filter control
Using the member filter control

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