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.

Using an Existing Dimension

Hide navigation
RSS
Modified on Wed, 08 May 2013 05:53 PM Categorized as Datasets, Dimensions, Filtering, KPIs
Click to return to: Documentation | Datasets | KPIs


Overview

From the Columns tab of the Dataset Designer, or the Dimensions tab of the KPI Designer, you can choose to replace a column/instant dimension with an existing standard or time dimension that is defined in the current Dundas Dashboard 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 you have a Product ID instant dimension, you can choose to replace it with a standard dimension, Product, which has multiple levels such as Product Category, Product Subcategory, and Product Item.

Using an existing standard dimension

To replace a column/instant dimension with an existing standard dimension:

  1. (For a dataset) Locate the column you want to replace in the Columns grid.
  2. (For a KPI) Locate the instant dimension you want to replace in the Instant Dimension Fields grid.
  3. Click the corresponding Replace button in the Action grid column.
    The Replace button.

The Standard Dimension Setup Wizard is displayed.

Step 1 - Choose an existing standard dimension

In the first step of the Standard Dimension Setup Wizard:

  1. Select an existing standard dimension from the Compatible Dimensions grid. The standard dimension's hierarchy is displayed in the Available dimension hierarchies grid.
  2. Click the Hierarchy level dropdown list to choose the hierarchy level that you want to use for matching against the dataset column (or KPI instant dimension) that you want to replace. For example, you might choose the lowest (most detailed) hierarchy level Product Item if you are replacing a Product ID column/instant dimension.
  3. The Key Matching Fields grid shows the dataset column (or KPI instant dimension) that is being replaced (&ie; Matching Field) and a corresponding key column from the existing dimension (&ie; Field Name). The Matching Field value will either be set automatically or you must change its value to match the key column from the existing dimension. If the existing dimension uses composite keys, there will be more than one row in this grid and you must set the mappings for both. See the Examples section for more details.
  4. Click Next.
    The first step of the Standard Dimension Setup Wizard.

Step 2 - Review the standard dimension hierarchy

In the second step of the Standard Dimension Setup Wizard:

  1. Expand the tree to review the levels and level members of your selected dimension hierarchy.
  2. Click Next.
    The second step of the Standard Dimension Setup Wizard.

Step 3 - Define a filter on the existing standard dimension

In the third step of the wizard, define a filter for the dimension as follows:

  1. Select the Public check box if you want the filter to be public (&ie; accessible to dashboard designers).
  2. Enter a public name for your filter in the Dashboard label box.
  3. Enter a description for your filter in the Description box.
  4. If you want the filter to provide an option that includes all members, select the (All) member allowed check box.
  5. If you chose the (All) member allowed option and your instant dimension appears on the visualization axis, you will also have the option of selecting the Unknown members included check box. If you select this option, the (All) member allowed filter results will include data records which cannot be categorized by the dimension. See the Unknown members example for more details.
    Defining filter attributes.
  6. Select the multiple value selections check box if you want your filter to allow multiple values to be selected.
  7. Restrict the available hierarchy levels by choosing the desired Top Level and Bottom Level of the dimension.
    Choosing the default dimension grain (KPI).
  8. If the instant dimension being replaced is also selected as the visualization axis, you will be given the option of choosing the Default Dimension grain or level to use for grouping on the visualization axis. To enable interactive selection of the grain (⪚ drill-down), ensure that the filter is Public, and then click the User selectable check box.
  9. Select the Request ancestry fields option if you want to add extra columns to your KPI/dataset to display ancestry information, such as parent category values. In this case, also choose the top ancestor level that you want to be returned. The ancestry information is intended primarily to be accessed from script. See Requesting Ancestry Fields for more details.
  10. Click the Default value button.
    The Default value button.

    A dialog is displayed, which lets you choose a default dimension value for your filter.
    Choosing the default value.
  11. (Optional) If you specified a default value and you did not choose the (All) member allowed option, click Next to define validation rules on filter values. See Defining Validation Rules.
  12. Click Finish. The wizard closes.

(For a dataset) The replaced column is removed from the Columns grid and a new row appears in the Existing Dimensions Columns grid.

The Existing Dimensions Columns grid showing a standard dimension.

The Existing Dimensions Columns grid showing a standard dimension.


(For a KPI) The instant dimension is removed from the Instant Dimension Fields grid and a new row appears in the Existing Dimensions grid.

The Existing Dimensions grid showing a standard dimension.

The Existing Dimensions grid showing a standard dimension.


Using an existing time dimension

To replace a column/instant dimension with an existing time dimension:

  1. (For a dataset) Locate the column you want to replace in the Columns grid.
  2. (For a KPI) Locate the instant dimension you want to replace in the Instant Dimension Fields grid.
  3. Click the corresponding Replace button in the Action grid column.
    The Replace button.

The Time Dimension Configuration Wizard is displayed.

Note: When replacing a DateTime column/instant dimension from your dataset/KPI, you must ensure that the time portion of each of the DateTime values has been truncated to 12:00 AM (&ie; midnight) beforehand. Otherwise, the resulting dataset/KPI will not return any data (&ie; no records will be displayed when you preview the dataset/KPI). The Columns tab in the Virtual Table Designer offers the Day date-trimming (&ie; aggregation) function to help you with truncating the values of a DateTime column.

Step 1 - Choose an existing time dimension

In the first step of the Time Dimension Configuration Wizard:

  1. Select an existing time dimension from the Compatible Time Dimensions grid.
  2. Select a hierarchy from your chosen time dimension in the lower grid.
  3. Click Next.
    The first step of the Time Dimension Configuration Wizard.

Step 2 - Review the time dimension hierarchy

In the second step of the wizard:

  1. Expand the tree in order to review the levels and level members of your selected time dimension hierarchy.
  2. Click Next.
    The second step of the Time Dimension Configuration Wizard.

Step 3 - Define a filter on the existing time dimension

In the third step of the wizard, define a filter for your time dimension:

  1. Select the Public check box if you want the filter to be public (&ie; accessible to dashboard designers).
  2. Enter a public name for your filter in the Dashboard label box.
  3. Enter a description for your filter in the Description box.
  4. If you want the filter to provide an option that includes all members, select the (All) member allowed check box. In this case, your filter will also support the Beginning of time and End of time options (or default values).
    Defining filter attributes.
  5. Restrict the available hierarchy levels by choosing the desired Top Level and Bottom Level of the time dimension. If the instant dimension being replaced is also selected as the visualization axis, you will be given the option of choosing the Time Dimension grain or level to use for grouping on the visualization axis. To enable interactive selection of the grain (⪚ drill-down), ensure that the filter is Public, and then click the User selectable check box.
    Choosing the default dimension grain (KPI).
  6. The default value for your filter will be set to (All) initially. If you want to change this and choose a specific time dimension member as the default filter value:
    • Click the Default value button.
      Default value button.
    • The Select value dialog is displayed, which lets you choose a default value from the time dimension hierarchy.
      Choosing a specific time dimension member .
    • The time dimension member that you choose implicitly defines a date range. If you want to specify an end date explicitly instead, click the Choose a different member for range ending check box. Use the resulting Default end value controls to choose the default end date.
  7. Alternatively, to specify the default filter value using relative dates:
    • Click the dropdown list beside the Default value button and choose an item from the resulting menu.
      Choosing a relative date value.
    • Menu items such as All values, Today, and Year to date define a date range implicitly. If you choose any of these items, you do not have to specify an end date.
    • Menu items such as Now and Beginning of current month define the start of a date range. If you choose any of these items, the Choose a different member for range ending check box will be automatically selected and you will be required to specify a default end date by using the Default end value controls.
      Choosing a default end date value.
    • The Advanced menu option opens the Advanced Parameter Options dialog, which lets you specify an offset from a relative date value. The offset uses the same units as the relative date value, and can be negative in value.
      Advanced Parameter Options dialog.
  8. (Optional) If you specified a default value and you did not choose the (All) member allowed option, click Next to define validation rules on filter values. See Defining Validation Rules.
  9. Click Finish. The wizard closes.

(For a dataset) The original column is removed from the Columns grid and a new row appears in the Existing Dimensions Columns grid.

(For a KPI) The instant dimension is removed from the Instant Dimension Fields grid and a new row appears in the Existing Dimensions grid.

The Existing Dimensions grid showing a time dimension.

The Existing Dimensions grid showing a time dimension.


Reverting back to an instant dimension or original column

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

  1. Locate the existing dimension in the Existing Dimensions grid.
  2. Click the corresponding Revert button in the Action grid column.
  3. When prompted to confirm the operation, click Yes. The existing dimension is removed from the grid and the original column/instant dimension re-appears.

Examples

Standard dimension with a composite key

The following example shows how to replace two instant dimensions with one standard dimension which has a level that is defined using a composite key (&ie; a key comprised of two or more virtual table columns).

Consider a standard dimension, Geography, which has two levels: State and City.

Geography dimension.

Geography dimension.


Observe that city names are not unique because it is possible for two US states to each have a city with the exact same name (⪚ Portland, Oregon and Portland, Maine). To address this, the City level of the Geography dimension must be defined with a composite key. In the Level Definition screen, you define a composite key by selecting more than one column from the base virtual table to comprise the key.

City level uses a composite key.

City level uses a composite key.


Next, consider a KPI defined as follows:

  • Name: Population By City
  • Measure: Population
  • Instant dimension: State
  • Instant dimension: City (appears on visualization axis)
  • Filters: None

To replace both instant dimensions with the Geography dimension, go to the Dimensions tab of the KPI Designer, and then click the Replace field with an existing Regular Dimension button which corresponds to the City instant dimension.

Replacing instant dimensions.

Replacing instant dimensions.


In the Standard Dimension Setup screen, set the Hierarchy level dropdown list to the City level. In the Key matching fields grid, change the Matching Field values (which are the names of the KPI instant dimensions) to match the Field Name values (which are the virtual table column names for the Geography dimension).

Matching KPI fields with dimension fields.

Matching KPI fields with dimension fields.


Both instant dimensions are now replaced with the Geography dimension, as shown in the Existing Dimensions grid:

Instant dimensions are replaced with Geography dimension.

Instant dimensions are replaced with Geography dimension.


Here is the preview of the resulting KPI:

KPI preview.

KPI preview.


Related topics


Click to return to: Documentation | Datasets | KPIs

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

Copyright © 2009-2014 Dundas Data Visualization, Inc.