416.467.9100 | Dundas Data Visualization | Login
Welcome Guest
This is the support page for the legacy Dundas Dashboard application. For assistance with the current Dundas BI application please click here.
Dashboard v5.0

This site makes extensive use of JavaScript.

Please enable JavaScript in your web browser and reload the page before proceeding.

Defining Dataset Columns

Hide navigation
RSS
Modified on Fri, 22 Nov 2013 11:20 AM Categorized as Datasets
Click to return to: Documentation | Defining Datasets


Overview

The Columns tab of the Dataset Designer lets you configure the columns that were chosen through the Dataset Setup Wizard. The configuration steps in this tab are different depending on whether your dataset is based on a virtual table or a virtual cube (OLAP).

One of the configuration options you have in this tab is determining the visibility of a dataset column. A visible column can be rendered on a dashboard (⪚ on a visualization axis), whereas a non-visible column is typically used for filtering only. A dataset must define at least one visible column. Otherwise, you will see an error message when you try to save, preview, or check in the dataset.

Dataset based on a virtual table

For a dataset based on a virtual table, you can define filters on columns, associate look-up tables, choose aggregation functions, perform grouping, or hide columns from the final result.

Alternatively, if you have previously defined standard or time dimensions, you can replace a dataset column with one of these existing, compatible dimensions. This is useful for adding more capabilities to your dataset, such as support for advanced filtering (⪚ multi-level user-selectable filters) and drill-downs.

The original columns that you chose through the setup wizard are listed in the Data Columns grid. Each row in this grid shows the properties for a single dataset column. You can edit most of these column properties directly in the grid itself as shown in the following sections.

The Columns tab in the Dataset Designer (for a dataset based on a virtual table).

The Columns tab in the Dataset Designer (for a dataset based on a virtual table).


Changing the column name or description

To change the name or description of a dataset column:

  1. In the Data Columns grid, select the cell that contains the name (or description) you want to change (if it is not already selected).
  2. Click inside the cell to begin editing.
  3. Type the new name (or description) in the text box, then press ENTER.

Hiding columns

To hide a column (&ie; exclude it from your dataset for visualization purposes):

  1. Locate the grid row that corresponds to the column you want to hide.
  2. Uncheck the check box in the Visible grid column.

To easily hide or include all columns in the grid, toggle the check box in the header of the Visible grid column.

Grouping

To apply grouping to data rows based on a column:

  1. Locate the grid row that corresponds to the column you want to use for grouping.
  2. Select the check box in the Group By grid column.

To easily group by or ungroup all columns in the grid, toggle the check box in the header of the Group By grid column.

Aggregation

An aggregate function takes a set of input values and computes a single output value. Aggregation (on a column) is typically used in conjunction with grouping to produce summarized data. The actual list of functions that you will see in the designer varies depending on the capabilities of the data provider that your dataset is based upon. For example, the MEDIAN aggregate function is available only for the Oracle data provider.

To apply an aggregate function to a column:

  1. Locate the grid row corresponding to your column.
  2. Select the cell in the Aggregator grid column.
  3. Click inside the cell. A dropdown list control appears.
  4. Click the dropdown list and choose an aggregate function from the resulting menu.

Defining a filter

This section shows how to define a data filter on a dataset and optionally make it public. A public filter can be connected to a dashboard parameter and ultimately displayed on the dashboard in the form of a selection control (⪚ dropdown list).

Tip: You can also define a data filter on a calculated column that is based on a database expression using similar steps as below.

To define a filter on a dataset column:

  1. Click the Set up data column filter button in the Action grid column that corresponds to your dataset column. The Data Filter Setup Wizard is displayed.
    Set up data column filter.
  2. If your dataset column is of a numeric data type, the first step of the Data Filter Setup Wizard lets you enable the handling of empty data points by choosing a rule for computing replacement data point values. An empty data point rule determines how missing or null values in your data will be rendered on a chart, for example. For more details, see Handling Empty Data Points.
  3. Follow the steps as shown in the article, Defining a Filter, to set up the column filter.
  4. Optionally add validation rules for filter values as shown in the article, Defining Validation Rules.

The Attributes cell in the Data Columns grid will be updated to reflect the changes made to the column filter. Hover over an attribute icon with your mouse to see a tooltip describing the attribute. For example, in the figure below, the attributes for the Product SKU column indicate that:

  • A filter has been defined on the column.
  • The filter is private (&ie; will not be accessible to dashboard designers).
  • The filter is for single value selection (as opposed to range or multiple value selection).
  • No validation rules have been placed on filter values.

A filter defined on a dataset column.

A filter defined on a dataset column.


Dynamic filters

A dynamic filter can be applied to a visible column that has not been replaced with a full dimension, to give users the ability to switch metric or dimension columns on-the-fly while viewing a dashboard. For more details, see Dynamic Filters.

Using a look-up table

There are situations where the data from your virtual tables is less than optimal. For example, your dataset may be based on a virtual table that has a Country ID column but lacks a corresponding name or description column. In this case, you can perform a join with another virtual table for look-up purposes.

The figure below shows the Data Columns grid for a dataset with a Country ID column.

A dataset with a Country ID column.

A dataset with a Country ID column.


To join with a column from a look-up table:

  1. Click the button in the Look-Up grid column that corresponds to your original dataset column. The Data Field Look-Up Wizard is displayed.
  2. Use the wizard to make the association with a column from another virtual table. See Using a Look-up Table.
  3. The name of the column in the Data Columns grid is replaced with the look-up column name.

Using a column from a look-up table.

Using a column from a look-up table.


Adding a calculated metric column

To add a calculated metric column, which is based on existing dataset columns, click the Add Calculated Column button above the Data Columns grid. The Calculated Metric Definition Wizard is displayed.

See Adding a Custom Column for more details.

The Calculated Metric Definition Wizard.

The Calculated Metric Definition Wizard.


Replacing an original column with an existing dimension

In the Columns tab of the Dataset Designer, you can choose to replace an original column with an existing standard or time dimension that is defined in the current &dw; project. This lets you take advantage of the hierarchy, labeling, and sorting rules defined by the existing dimension. This option is useful for setting up multi-level filters, drill-downs, and hover-overs.

As an example, if your dataset has a Product ID column, you can choose to replace it with a standard dimension, Product, which has multiple levels such as Product Group, Product Subgroup, and Product Item.

When you replace an original column with a dimension, the column is removed from the Data Columns grid and a corresponding dimension entry is added to the Existing Dimensions grid.

For more details on replacing a dataset column with a standard or time dimension, see Using an Existing Dimension.

Buttons for replacing columns with dimensions.

Buttons for replacing columns with dimensions.


Product ID column replaced with a Product dimension.

Product ID column replaced with a Product dimension.


If you want to revert an existing dimension back to an original column:

  1. Locate the existing dimension in the Existing Dimensions grid.
  2. Click the corresponding Revert to original Instant Dimension field button in the Action grid column.
  3. When prompted to confirm the operation, click Yes. The existing dimension is removed from the lower grid and the original column re-appears in the upper Data Columns grid.

If you want to delete an existing dimension and you don't want the original column either:

  1. Locate the existing dimension in the Existing Dimensions grid.
  2. Click the corresponding Delete Dimension button in the first grid column.
  3. When prompted to confirm the operation, click Yes. The existing dimension is removed from the lower grid. The original column will not re-appear in the upper Data Columns grid.

Adding virtual table columns

Click the Add Column button in the top-left corner if you want to add other columns from the base virtual table to your dataset. For example, these may be columns that you omitted when you first went through the Dataset Setup wizard.

Adding other columns from the virtual table.

Adding other columns from the virtual table.


Replacing a column

Click the Replace column button to replace an original column with another column from the base virtual table. Any existing dashboards that use this dataset will continue to work without modifications.

Searching columns

If your Columns tab shows a lot of columns, it can be hard to locate the columns you are interested in.

Use the Search columns box in the top-right corner to enter a term or other text that will be used to filter the list of columns.

To see the full list of columns again, simply clear the Search columns box.

Search columns box.

Search columns box.


Dataset based on a virtual cube (OLAP)

For a dataset based on a virtual cube, the Columns tab lists the OLAP measure and OLAP dimension columns that you selected through the Dataset Setup Wizard. You can change the name or description of any column, hide columns from the final result, or set up a filter on an OLAP dimension column.

The Columns tab in the Dataset Designer (for a dataset based on a virtual cube).

The Columns tab in the Dataset Designer (for a dataset based on a virtual cube).


Changing the name or description

To change the name or description of an OLAP measure or OLAP dimension column:

  1. In the appropriate Columns grid, select the cell that contains the name (or description) you want to change (if it is not already selected).
  2. Click inside the cell to begin editing.
  3. Type the new name (or description) in the text box, then press ENTER.

Hiding columns

To hide a column (&ie; exclude it from your dataset for visualization purposes):

  1. Locate the grid row that corresponds to the column you want to hide.
  2. Uncheck the check box in the Visible grid column.

To hide or include all columns in a Columns grid, toggle the check box in the header of the Visible grid column.

Defining an OLAP filter

Each OLAP dimension column will have a public filter already defined by default. A public filter can be connected to a dashboard parameter and ultimately displayed on the dashboard in the form of a selection control (⪚ dropdown list).

To configure the filter for an OLAP dimension column:

  1. Click the Configure Dimension Filter button in the Action grid column that corresponds to your OLAP dimension-based column. A wizard is displayed, which lets you set up a filter on an OLAP dimension.
    Configure Dimension Filter button.
  2. Follow the steps as shown in the article, Defining a Filter, to set up the OLAP dimension filter.
  3. The Attributes cell in the grid will be updated to reflect the changes made to the OLAP dimension filter. Hover over an attribute icon with your mouse to see a tooltip describing the attribute.

To configure the filter for an OLAP measure column:

  1. Click the Set up data column filter button in the Action grid column that corresponds to your OLAP measure-based column. A wizard is displayed, which lets you set up a filter on an OLAP measure.
  2. Follow the steps as shown in the article, Defining a Filter, to set up the OLAP measure filter.
  3. The Attributes cell in the grid will be updated to reflect the changes made to the OLAP measure filter.

Adding measures

Click the Add Metric button to add other measures from the base virtual cube to your dataset.

Adding another measure/metric.

Adding another measure/metric.


Adding dimensions

Click the Add Dimension button to add other dimensions from the base virtual cube to your dataset.

Adding another dimension.

Adding another dimension.


Notes

Aggregation and grouping example

The following is an example of using aggregation, group by, and calculated columns.

  1. Create a dataset that has two numeric columns.
  2. Set the MIN aggregator on these two columns.
  3. Create a calculated column that performs a summation of the two columns using a database expression. For example:
    {ValY1}+{ValY2}
  4. Set a SUM aggregator on the calculated column.
    SUM aggregator.

The following table illustrates the results depending on the aggregator and group by settings for the calculated column.

AggregatorGroup ByResult
NoneNoMIN(A) + MIN(B)
NoneYesA + B, grouped by
SUMNoSUM(A + B)
SUMYesInvalid settings

Related topics


Click to return to: Documentation | Defining Datasets

About Dundas | Contact Us Follow us on Twitter! | Privacy Statement | Report Site Issues

Copyright © 2009-2014 Dundas Data Visualization, Inc.