Create a metric set and add a filter
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.
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.
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 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 > Month > Day to the ROWS field of the Data Analysis Panel.
Drag the Product hierarchy to the COLUMNS field of the Data Analysis Panel.
The table visualization beside the Data Analysis panel is automatically updated to show 'Year' members along the rows, and 'Product Category' members along the columns. These members are all expandable.
3. Analyze your data
Dundas BI comes with several tools to analyze data using a metric set. The presented data visualization is automatically selected to best match the data and may change if you add or remove data. In the example above, the visualization changed from the default Table to a Line chart, but can be manually re-visualized to any other data visualization.
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 ranges of data, Dundas BI displays a set of summary information in the status bar.
You can quickly apply filters to data using the filter icon on the data panel.
Or using 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.
When filtering data using multiple filter 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:
- If the data contains multilevel hierarchies, you can use the drill-down and drill-up to focus on specific parts of the data (such as, moving from 2012 values to January 2012 values)
- The level and top level can be changed to increase or decrease the overall granularity of the displayed data (for example, going from viewing values by year to viewing values by month)
- Group Members can be used to create custom data groups
- Values can be adjusted using Data Annotations
4. Complete the 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.
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'.
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 Metric Set Designer 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.
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'.
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.
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.
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.
A Member Range filter control 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.
The bar chart on the dashboard is filtered to display data from 2012 to 2013.
To use the member range filter control, switch to View mode from the toolbar.
7.2. Add a member filter
Switch back to Edit mode. 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 control 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
The second (Stacked Bar) chart is updated to show only the chosen members on the X axis.
To use the member filter control, switch to View mode from the toolbar.
7.3. Connect the filters
You can create a dependency between filters if you want one filter control to filter the values in another filter control.
Select the range member filter control and click Connect Filters on the toolbar.
In the Filter Visualizations panel, click Dependent Filters.
Select the checkbox next to the hierarchy from the dependent filter.
Switch to View mode 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.
8. See also
- Video: Introduction to Metric Sets
- Video: Filters
- Video: States
- Using a data cube to join tables
- Add a period over period comparison
- Using search in filter controls
- Add a relationship between tables