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 Columns for a Virtual Table

Hide navigation
RSS
Modified on Mon, 03 Mar 2014 05:33 PM Categorized as Data Preparation, Virtual Tables
Click to return to: Documentation | Defining Virtual Tables


Overview

This article shows how to use the Columns tab in the Virtual Table Designer to choose and configure data columns for your virtual table.

All of the data columns from your previously selected data structures are displayed in the Virtual Table Columns grid. Each row in this grid shows the properties for a single data column. You can edit most of these data column properties directly in the grid itself. For example, you can:


Additionally, you can reorder columns or add a custom column that functions as a counter.

The Columns tab of the Virtual Table Designer.

The Columns tab of the Virtual Table Designer.


Tip: The values in the Source column are aliases for the underlying database table or view. Hover with your mouse over a cell in this column to see a tooltip that displays the name of the corresponding table or view. For example, the alias DD1 corresponds to the [dbo].[Products] table.

Defining the columns of your virtual table

Changing the friendly name

Friendly column names are used when you create a dimension, Dataset, or KPI based on a virtual table. For example, during the setup procedure for a KPI, friendly column names from its base virtual table determine the initial names for the KPI's measure values, contextual metrics, and dimensions.

To change the friendly name of a virtual table column:

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

Note: Once a KPI has been configured to use a column from a virtual table, any subsequent changes to the friendly name of the virtual table column will not be reflected in the KPI since the KPI maintains its own names for measure values, contextual metrics, &etc;

Changing the description

To change the description of a column:

  1. Select the grid cell that contains the description you want to change (if it is not already selected).
  2. Click inside the cell to begin editing.
  3. Type the new description for your column, then press ENTER.

Hiding columns

To hide a column (&ie; exclude it from your virtual table):

  1. Locate the grid row that corresponds to the data column you want to hide.
  2. Select the check box in the Hidden grid column. The corresponding grid row is greyed out.
    Hiding a data column.

To hide or un-hide all columns in your virtual table, toggle the check box in the header of the Hidden grid column. This option is useful when you want to hide most of the columns in your virtual table.

Note: If you modify the underlying database of your virtual table by adding a new column, the next time you re-discover the data connector, or edit the virtual cube, the new column may be added automatically to the virtual table but it will be hidden by default. Prior to Dundas Dashboard 5, the new column(s) are visible by default.

Grouping data based on a column

To apply grouping to data rows based on a column:

  1. Locate the grid row that corresponds to the data column you want to use for grouping.
  2. Select the check box in the Group By grid column. The Group By option will be automatically selected for any other grid rows that have not been hidden and are not using an aggregate function. The reason why these other columns must also be grouped by is because in SQL, a column appearing in the SELECT list must also appear in the GROUP BY clause, unless that column appears in an aggregation expression.
    Grouping on a data column.

Note: You cannot use the Group By option if your virtual table contains any columns of type text, ntext, or image. These column data types do not support comparison or sorting and you will receive an error when trying to preview a virtual table that uses grouping and has columns of these types.

Aggregation

An aggregate function takes a set of input values and computes a single output value. Aggregation (on a data column) is typically used in conjunction with grouping to produce summarized data.

Dundas Dashboard supports the following aggregate functions, but the actual list that you will see in the designer varies depending on the capabilities of the data provider that your virtual table is based upon. For example, the MEDIAN aggregate function is available only for the Oracle data provider.

  • For numerical data column types:
    • AVG
    • COUNT_BIG
    • COUNT_BIG DISTINCT
    • MAX
    • MIN
    • SUM
    • STDEV
    • STDEVP
    • VAR
    • VARP
    • MEDIAN
  • For string or datetime column types:
    • COUNT_BIG
    • COUNT_BIG DISTINCT
    • MIN
    • MAX

To apply an aggregate function to a data column:

  1. Locate the grid row corresponding to your data 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.
    Choosing an aggregate function.

Note: Support for COUNT_BIG and COUNT_BIG DISTINCT was added in Dundas Dashboard 5.0.2, but it is not available for Microsoft Access or FoxPro data sources.

Date functions

For datetime column types, you can choose one of the following date-trimming functions to apply:

  • DateTrim: Year
  • DateTrim: Month
  • DateTrim: Day
  • DateTrim: Hour
  • DateTrim: Minute

Generally, if you choose a date function for a column, you will also group by that column.

Choosing a date function.

Choosing a date function.


Filtering

To define a filter on a column:

  1. Locate the grid row corresponding to your data column.
  2. Click the Set up value filtering button in the Column Filter grid column. The Data Filter Setup Wizard is displayed.
  3. Define the filter using the wizard as shown in the article, Defining a Filter.
  4. Click Finish to close the wizard.
  5. The Column Filter grid cell displays icons that represent the attributes of the filter you just created. Hover over an icon with your mouse to see a tooltip describing the attribute.
    Defining a data filter.

To remove a filter from a data column:

  1. Locate the grid row corresponding to your data column.
  2. Click the Remove filter button in the Column Filter grid column.
  3. When prompted to confirm the removal, click Yes. The filter is removed from the data column.

Adding custom columns

You can add a custom column to your virtual table by clicking the Add Custom Column button, which is located above the Virtual Table Columns grid. This will launch the Calculated Metric Definition Wizard, which lets you choose the type of custom column to add. Three types of custom columns are supported:

  • A constant value column has the same value for every data row.
  • A counter column keeps track of the number of records corresponding to every grouped set of values. For example, if your virtual table groups order-related records by region, a counter column will keep track of the number of orders per region.
  • A database expression column is a calculated-value column which is defined as a mathematical expression involving existing column values.

Note: You cannot create a filter or set an aggregate function for these custom columns. You can however do this at the KPI or dataset stage.

The Calculated Metric Definition Wizard.

The Calculated Metric Definition Wizard.


To define a custom column:

  1. In the first step of the Calculated Metric Definition Wizard, enter a name for your new column in the Friendly Name box.
  2. Enter a description for your column in the Description box.
  3. Select and configure the type of custom column you want as described in the article, Adding a Custom Column.
  4. The custom column will appear as a new top row in the Virtual Table Columns grid.
    A custom counter column.

To remove a custom column:

  1. Locate the grid row corresponding to the custom column and click the Remove custom column button in the Custom Column grid column.
  2. When prompted to confirm the removal, click Yes. The custom column is removed from the Virtual Table Columns grid.

Changing the order of columns

You can specify the order of the data columns for your virtual table by changing the order of the rows in the Virtual Table Columns grid. For example, you can move all of the visible rows in the grid to the top and adjust their order as desired.

Buttons for changing the column order.

Buttons for changing the column order.


To move a grid row to the top of the order:

  1. Select the grid row (&ie; data column) that you want to move.
  2. Click the first button above the top-right corner of the Virtual Table Columns grid. The selected row becomes the first row in the grid.

To move a grid row up one position in the order:

  1. Select the grid row (&ie; data column) that you want to move.
  2. Click the second button. The selected row moves up one position (if it is not already the first row).

To move a grid row down one position in the order:

  1. Select the grid row (&ie; data column) that you want to move.
  2. Click the third button. The selected row moves down one position (if it is not already the last row).

To move a grid row to the bottom of the order:

  1. Select the grid row (&ie; data column) that you want to move.
  2. Click the fourth button. The selected row becomes the last row in the grid.

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.


Cache indexes and primary keys

If your virtual table is or will be cached, you can define primary keys or indexes on the columns of the cached table to improve performance. For more details on setting up caching, see Caching Data.

To define an index or primary key for a cached virtual table:

  1. Click the Cache Indexes/Keys button above the Virtual Table Columns grid. The Editing Cache Indexes/Keys dialog is displayed.
  2. Click the Add button. An index is added to the list on the left and the properties of the (selected) index are displayed in the properties grid on the right.
  3. To define a primary key instead of an index, set the Type property to Primary Key. For example, you may want to build a primary key on a Product Group ID column.
  4. Enter a description for your index or primary key using the Description property.
    The Indexes/Keys dialog.
  5. The Columns property lets you choose the column(s) to build the index or primary key upon, and to choose a sort order. For example, you may want to build an index on a Product Group Name column and set the sort order to ASC (ascending). To select a different set of columns or change the sort order:
    • Click the Columns property value. An ellipsis button appears.
    • Click the ellipsis button. The Columns dialog is displayed.
    • Use the Columns dialog to add other columns, remove columns, or change the name or sort order of the selected column.
      The Columns dialog.
    • Click Close to close the Columns dialog.
  6. Click Close to close the Indexes/Keys dialog.

Summary

You have chosen and configured the data columns for your virtual table. You can now view the generated query representing your virtual table, optionally modify the query, and preview a subset of the resulting data.

Completed settings for the Columns tab.

Completed settings for the Columns tab.


Related topics


Click to return to: Documentation | Defining Virtual Tables

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

Copyright © 2009-2014 Dundas Data Visualization, Inc.