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 full-screen metric set editor 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 canvas.

Drag measure to canvas
Drag measure to canvas

By default, the measure is displayed in a table visualization beside a Data Analysis 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 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 > Month > Day under ROWS in the Data Analysis Panel, or drag it onto the canvas and it will be added under ROWS in the Data Analysis Panel for you.

Drag the Product hierarchy to the COLUMNS field of the Data Analysis Panel.

Drag hierarchies
Drag hierarchies

The table visualization is automatically re-visualized to a line chart because this is usually the best choice for visualizing changes to measure values over time.

A line chart visualizes order quantities over time for each product category.
A line chart visualizes order quantities over time for each product category.

At any time, you can change to the visualization of your choice by going to the toolbar and clicking Re-Visualize, then choosing an option. Once you have chosen a visualization yourself, customized it, or saved and re-opened this metric set, Dundas BI will not re-visualize it automatically.

Tip
Instead of dragging measures/hierarchies from EXPLORE, use the click to add option to get a list of elements to add from the data source you're already using.

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
If a relationship is defined or can be found, you can drag elements from another data cube or data source.

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

3. Analyze your data

Dundas BI comes with several tools to analyze data using a metric set.

3.1.1. 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 data, Dundas BI displays a set of summary information in the status bar.

With a chart, click and drag across an area to select the data points in that range, and see its statistics.

Select data points and get quick summary information
Select data points and get quick summary information

3.1.2. Filtering

You can quickly apply filters to data using the filter icon on the Data Analysis 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

You can also use the Filter option from the context menu:

Right-click a data point (or a group of selected data points) and select Filter. In the resulting menu, choose to Keep Only Selected data, Exclude Selected data, or select a dimension to open its filter menu.

Use the context menu to exclude a set of data points
Use the context menu to exclude a set of data points

For an example of excluding selected data, see the article Exclude data points from the visualization.

When filtering data using multiple filter settings, each filter setting is always applied. 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:

  • Sort: sort the data by a particular column or series
  • Drill Down/Drill Up: If the data contains multilevel hierarchies, drill down on a value to see the components that made up that value from the next level down in the hierarchy (e.g., drill down on 2012 to see values for January through December of 2012)
  • Change Level: change the level up or down (e.g., going from viewing all of the data grouped by year to viewing all of the data grouped by month) or set the top level to include upper levels
  • Group Members: group multiple members or values together into one
  • Data Annotations/Measure Corrections: make changes to values

Use the context menu to perform other analysis
Use the context menu to perform other analysis

4. Complete the metric set

To complete the setup of this metric set, go to the toolbar, click Re-Visualize, and then click Bar under Change Chart Type.

Re-visualize as bar chart
Re-visualize as bar chart

The visualization is now changed to a bar chart 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.

5. 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 canvas.
  • Expand the data cube's Dimensions folder and drag the FirstName hierarchy to the ROWS field of the Data Analysis panel.
  • Drag the 'Product' hierarchy to the COLUMNS field of the Data Analysis panel.

The resulting data bar table 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 under Change Chart Type. 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.

6. Display the metric sets on a dashboard

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

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

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., Metric Set 1 and Metric Set 2). 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 when editing each metric sets in their full-screen editor.

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

7. Using filters on a dashboard

7.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 is added to the dashboard and its Filter Visualizations panel is displayed. On the panel, observe that the filter is already configured to filter the hierarchy along the X axis of the first chart (and metric set).

Set a default range for the filter by clicking the Default Value regions. For example, set the start of the range to 2012 and the end to 2013.

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

The bar chart on the dashboard is filtered to display data from 2012 to 2013.

To use the member range filter, click View in the toolbar.

Using the member range filter
Using the member range filter

7.2. Add a member filter

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

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

Set the default members for this filter by clicking the Default Value region

Member filter
Member filter

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

To use the member filter, click View in the toolbar.

Using the member filter
Using the member filter

7.3. Dependent filters

You can create a dependency between filters if you want one to filter the values available in another.

Note
This functionality is available only when the hierarchies belong to the same data cube, and does not work with bridge parameters and OLAP data.

Select the range member filter and click Connect Filters on the toolbar.

Click Connect Filters
Click Connect Filters

 In the Filter Visualizations panel, click Dependent Filters.

Click Dependent Filters
Click Dependent Filters

Select the checkbox next to the hierarchy from the dependent filter.

Select the dependent hierarchy
Select the dependent hierarchy

View the dashboard and observe the effect of the range member filter on the member filter. For example, after setting the dates from 2011 to 2011, only ten members remain.

Note
The (All) member selects all the members in the hierarchy, including those not currently visible.

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