Metric set analysis tools

Contents[Hide]

1. Overview

A metric set allows you to select and analyze a set of data in Dundas BI, while displaying it in your choice of data visualization.

Metric sets power all visualizations in Dundas BI. You can work with metric sets one at a time in the full-screen editor, or combine them in a dashboard or another view to edit and analyze them together. This article introduces metric set functions as seen when editing one full-screen, but they are also available when working with metric sets on a dashboard or another view.

The full set of analysis capabilities are available while editing a metric set that is checked out to you, while anyone viewing the metric set is able to access powerful metric set features such as drill down, sorting, and grouping.

Related videos:

Walkthrough: Create a metric set and add a filter

2. Selecting and grouping data

2.1. New metric set

To create a metric set in the full-screen editor, choose New in the main menu, then Metric Set.

Create a new Metric Set
Create a new Metric Set

Start by choosing your first data source.

Choose your data
Choose your data

This can be any data found under a data connector or data cube someone has already added to Dundas BI, or a file from your computer like a spreadsheet.

Drag it onto the canvas, or choose Use an existing data structure to locate it in Dundas BI.

A new metric set with a measure
A new metric set with a measure

A table visualization now displays whatever data you chose with the Data Analysis Panel to the left.

2.2. Adding more data

The Data Analysis Panel is central to selecting and analyzing data. Regardless of which visualization you use, you can group and filter your data by placing it under one of these same four options:

  • Measures: numeric values that can be grouped and filtered
  • Rows: group by this data into a row for each value
  • Slicers: filter the data by the selected values
  • Columns: group by this data into a column for each value

You can find more details and examples in Slicers versus columns and rows.

To select more data, drag it from the Explore window, or click to add more data from your original data source.

Add more data
Add more data

By placing data on Rows or Columns, you are grouping by those values in the order they are placed. For example, place a time dimension hierarchy on Rows to see sales numbers grouped by year, even if the data source contains many rows for individual dates.

Grouping by year then by category
Grouping by year then by category

Tip
You can drag data from another table or data source in the Explore window. This uses database relationships for tables from the same database, or Dundas BI relationships for different data sources.

If you click to add the special option <Row Number>, the data will not be grouped and all original records will be displayed unaggregated as 'raw data'.

2.3. Moving, reordering, and removing

To move data between Rows, Slicers, and Columns, drag it from where it is placed already in the Data Analysis Panel to the new area where the label drop a hierarchy appears.

To change the order of multiple items placed under the same heading, drag a lower item and drop it where you want it located.

Dragging and dropping to reorder
Dragging and dropping to reorder

The order of hierarchies determines their grouping order, while the order of measures determines their sorting priority when sorting by more than one measure.

Click the circular x button to the right of the hierarchy or measure to remove it from the metric set.

2.4. Replacing with a hierarchy

If you added data directly from a database, spreadsheet, or another regular table of data that was not prepared as a cube, you can replace a regular column with a hierarchy from the Explore window.

  • Date/time columns can be expanded in the Explore window to find time dimension hierarchies, which you can drag onto your metric set.
    Expand to find time dimensions
    Expand to find time dimensions
  • Other types of columns can be replaced by dropping on top of it a column displaying friendlier names, or a hierarchy from the Explore window's Hierarchies folder if a related one was created.
    Locate a predefined hierarchy
    Locate a predefined hierarchy

See Automatic joins and hierarchies for details on replacing columns with hierarchies.

3. Visualization

A metric set's data is always shown in a visualization that you can change and customize.

If you didn't choose a particular visualization yet or customize its options, Dundas BI will automatically change the visualization when you add data based on its recommendations. For example, a measure and a time dimension hierarchy will be automatically visualized as a line chart, which is best for seeing the trending of a measure over time.

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, it will not be changed automatically.

A measure and time dimension hierarchy visualized as a line chart
A measure and time dimension hierarchy visualized as a line chart

You can customize a particular visualization in a number of ways:

  • Drop zones may appear over your visualization when dragging data from the Explore window, allowing you to choose how it will be visualized. For example, you can decide whether to display a hierarchy as a regular column, a row header column, or as a row of column headers in a table.
    Choices for visualizing a hierarchy in a table visualization
    Choices for visualizing a hierarchy in a table visualization
  • Similarly, you can click the Visualization button in the Data Analysis Panel of any visualization and drag data from the Explore window or click to add data directly under a visualization option, such as Tooltip for displaying additional details in a popup when hovered or long-tapped.
    Visualizing data in a tooltip
    Visualizing data in a tooltip
    See Setting up the visualization and Visualization tab examples for more details and examples.

  • Common visualization options can be found in the toolbar under the blue-highlighted section, and in the right-click context menu, such as changing a vertical bar (column) chart to a horizontal bar chart.
    Visualization options in the toolbar
    Visualization options in the toolbar
  • The full set of visualization settings are found in the Properties window.

When a metric set is added to a dashboard or another view, the visualization in the full-screen metric set editor is used as the template for a new visualization. The metric set is reusable and each visualization is separate, so it can be used multiple times and visualized differently, by you or people you share the metric set with.

4. Sorting and filtering

To quickly filter or sort data while editing, use the sort & filter icon shown for the values you want to sort by or filter.

For example, you can filter the range of dates shown on a chart, or sort a table by a measure.

The Sort & Filter popup shown for OrderDate
The Sort & Filter popup shown for OrderDate

You can also right-click (or long-tap) data directly in the visualization and access sorting and filtering options from the context menu.

Sorting and filtering from the context menu
Sorting and filtering from the context menu

If you choose Sort, a list of measures or hierarchies may appear if there is more than one related to what you clicked. Choose what values should be used to sort the rows (or columns), then choose the sort order.

Context menu sorting options
Context menu sorting options

If there were multiple hierarchies placed under Rows in the Data Analysis Panel, you may see the option More.... This option brings up the same sort & filter popup shown above and allows you to change how the data is grouped, which can affect your sorting.

If you choose Filter, you can choose to keep only or exclude the data you right-clicked or selected, filter by your choice of values, or clear any previous filtering (if applicable).

Context menu filter options
Context menu filter options

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

These context menu options are available even when you or others are viewing this metric set after it's been added to a dashboard and shared. By sorting and filtering with the metric set checked out to you, you can determine the initial settings when it's viewed the first time.

5. Changing levels and drilling down

If you selected a multi-level hierarchy, you can change which levels are displayed, or drill down to see the values in the level below that make up a value.

Context menu options
Context menu options

To quickly change the level, right-click (or long-tap) anywhere on the visualization and choose Change Level. (If there are multiple hierarchies to choose from, choose one from the list that appears next.)

Change level options
Change level options

Choose a level to change the level for the entire current set of data. 

Many visualizations can display multiple levels at once, including tables and relationship diagrams. To select a range of levels instead of just one, choose Change Top Level and then a level. When the main level is different than the top level, both levels and the levels in between will be displayed at once.

Table showing monthly data with Year as top level
Table showing monthly data with Year as top level

Right-click (or long-top) data in a visualization and choose Drill Down to filter to that value and change the level of the hierarchy down. (If there are multiple hierarchies to choose from, choose one from the list that appears next.)

For example, drill down on 2013 to see the individual monthly values that make up that year.

Result of drilling down on 2013
Result of drilling down on 2013

To change the level back up and clear the filtering, choose Drill Up in the context menu.

Like sorting and filtering, these context menu options are available even when you or others are viewing this metric set after it's been added to a dashboard and shared. Changing the level and drilling down while the metric set is checked out to you determines the initial settings when it's viewed the first time.

6. Expanding and collapsing

When multiple hierarchies are selected, or if a multi-level hierarchy is selected, you can right-click (or long-tap) values and choose Expand or Collapse. Some visualizations may also offer triangular expander buttons.

Result of expanding 2013
Result of expanding 2013

Expanding is similar to drilling down, except the other data remains displayed as before, with the values added from the level below or from the next hierarchy just for the expanded value.

Result of expanding 2013
Result of expanding 2013

Collapsing removes the values below just one value, leaving the rest of the data in place.

Result of collapsing Canada
Result of collapsing Canada

Use the Collapse All option to collapse all of the values of the hierarchy you chose, e.g., to collapse all years and view a summary by the remaining values.

Result of collapsing all years
Result of collapsing all years

Use Expand All to reverse this action.

Unlike expanding and collapsing individual values, Collapse All is a metric set setting that is saved, and can also be found in the dialog for each hierarchy in the Data Analysis Panel by clicking its orange tile to edit it. For more details and examples of expanding and collapsing, see Expand and collapse hierarchy members.

7. Measure options

When numeric data is selected as a measure, its values are grouped together and summed. This is indicated with the text SUM in the Data Analysis Panel.

The measure's aggregator
The measure's aggregator

To quickly change the aggregator, click this indicator and choose another from the list.

You can add a measure multiple times: drag the same data from Explore or click to add it again under Measures. Each copy of the measure can be set to a different aggregator.

Selecting the same measure with different aggregators
Selecting the same measure with different aggregators

To access the full set of measure options, click its green tile or pencil icon in the Data Analysis Panel to edit it.

Measure settings dialog
Measure settings dialog

The dialog includes settings such as:

  • CaptionTo change how this measure is identified in visualizations, change this caption.
  • Format: To change how numeric data is formatted, set this and the related formatting settings. See Formatting text for details on formatting.
  • Missing Data Rules: Missing data rules determine if rows or columns of data should still be displayed if there is no corresponding measure value available, and whether to fill them in with replacement values. See Handling null data points for details.
  • Hidden: If you don't want to display the measure but only refer to it for formulas, sorting, or other metric set features, check this box to prevent the measure's values from being seen directly.

Changing settings such as Caption and Format here effect everywhere at once as long as you haven't customized a visualization setting on top of them. For example, changing the caption affects column headers and tooltips, and changing the format affects the cells of a table, chart labels, and tooltips. They will also take effect if you re-visualize the metric set.

8. Totals

Tables and other visualizations have support for displaying totals. To quickly turn all totals on or off when they are available, choose Shown Totals in the toolbar and then which totals you want to show.

Showing and hiding totals in the toolbar
Showing and hiding totals in the toolbar

Note
Charts would typically be disrupted by totals, so they are hidden by default by the property Hide Total Values in the Properties window for most chart types. This can be un-checked to Show or hide total values on a chart.

There are also separate settings available on each hierarchy, which you can access by clicking its orange tile in the Data Analysis Panel.

Showing and hiding totals for a hierarchy
Showing and hiding totals for a hierarchy

When changing the setting for a hierarchy, you can choose whether to include subtotals, the grand total, or both.

The main total for a hierarchy is its Grand Total. The figure below indicates the grand total for all territories plus each grand total for all years.

Grand totals for all territories and all years
Grand totals for all territories and all years

A hierarchy's Subtotals are displayed for its upper levels when displaying more than one or it has been expanded to a lower level. The figure below indicates the subtotal for 2011 above months, and the subtotal for June, 2011 above its days because it has been expanded.

Subtotals for 2011 and June, 2011
Subtotals for 2011 and June, 2011

9. Data summary

When selecting rows, columns, or data points in a visualization, some basic statistics of that data will appear in the status bar: sum (SUM), average (AVG), minimum (MIN), maximum (MAX), the number of points (COUNT), and number of distinct values (DISTINCT).

If there are multiple measures, it will cycle through the statistics for each measure plus the statistics for all measure values mixed together. Hover over the data summary for a tooltip showing all of the statistics at once.

Status bar data summary for selected data in a table
Status bar data summary for selected data in a table

Note
Data summary is not available for waterfall and stacked chart types.

You can click and drag your mouse to select multiple points in a chart. Hold the Ctrl or Shift keys to select more than one area of data points. To select a group of rows or columns in a table, click the beginning and then the end of the group while holding the Shift key. 

10. More tools

Metric sets include many more tools to help with your analysis, which are detailed in separate articles.

In the toolbar, you can find:

  • States for calling attention to some data, or displaying it differently depending on its value
  • Period over period comparisons for viewing data from other time periods, or using it for states or formulas
  • Formulas for adding a new measure or an entirely new metric set based on a formula or script
  • Contextual measures for inputting values to compare against your data

The context menu provides you with additional options when you right-click or long-tap data:

The metric set has overall settings you can access by clicking the Edit icon in the Data Analysis Panel.

Edit metric set settings
Edit metric set settings

  • Transpose Result: A transposed metric set displays a different measure in each row instead of in each column. This can be used to display a single column or chart series comparing different measure values, whereas each measure is normally a separate column or series.
    Transposed metric set with a row for each measure
    Transposed metric set with a row for each measure
  • Top/Bottom Items and Other Group: you can filter the metric to display only the top or bottom 'N' items of some number, or group together the smallest items into a single item (or use other criteria).

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