Skip to main content

Add a Degenerate Dimension or a Common Degenerate Dimension

This section describes how to model degenerate dimensions, which are logical dimensions based on one or more columns in one or more fact datasets.

note

Note: Unhandled NULL values in key columns will result in incomplete aggregate tables and unexpected query results. For more information, see Using Custom Empty Members For Levels And Attributes.

Create degenerate dimensions

You create degenerate dimensions using the Bulk Create Degenerate Dimensions panel on the model Canvas. This enables you to create just a single dimension, or multiple simultaneously.

To create degenerate dimensions:

  1. In Design Center, open a model and switch to the Canvas tab.

  2. Select the fact dataset columns that you want to base your dimensions on by clicking on them. The selected columns are highlighted in black.

  3. Click the Create Dimension icon in the menu that appears above the dataset.

    image

    The Bulk Create Degenerate Dimensions panel appears. The panel is prepopulated with values based on the dataset columns you selected.

  4. Edit the following fields as needed:

    • Dimension Label: The name of the dimension, as it appears in BI tools. This value does not need to be unique.
    • Unique Name: The unique name of the dimension, as it appears in AtScale. This value must be unique across all repositories and subrepositories.
    • Description: A description of the dimension.
    • Hierarchy Label: The name of the initial dimension hierarchy, as it appears in BI tools. This value does not need to be unique.
    • Unique Name: The unique name of the initial hierarchy, as it appears in AtScale. This value must be unique within the dimension.
    • Description: A description of the initial hierarchy.
    • Level Label: The name of the initial dimension level, as it appears in BI tools. This value does not need to be unique.
    • Unique Name: The unique name of the initial level, as it appears in AtScale. This value must be unique within the dimension.
    • Description: A description of the initial level.
    • BI Folder: The folder in which the dimension appears in BI tools.
  5. Click Create Dimensions.

The new degenerate dimensions are added to the Canvas. Each has a relationship to the fact dataset column on which it is based. You can edit these objects as needed.

Create a common degenerate dimension

If your AtScale model has multiple fact datasets, and more than one of them contain values that you want to include in a degenerate dimension, you add relationships from those columns to the dimension to create a common degenerate dimension.

To create a common degenerate dimension:

  1. Create a degenerate dimension on one of the fact datasets as described above.
  2. Drag the relevant columns from the other fact datasets to the degenerate dimension.

When you create a common degenerate dimension, be sure that it meets the following requirements:

  • It must use the same number of columns from each fact dataset.

  • The column data types must be consistent:

    • Key columns used from each fact dataset must all have the same data type.
    • Value columns used from each fact dataset must all have the same data type.
  • If an order column is selected in one dataset, the order column from each of the other fact tables must be selected, as well.

Create a hierarchical common degenerate dimension

To create a hierarchical (multi-level) shared degenerate dimension among two or more fact datasets:

  1. Create a degenerate dimension as described above.

  2. Open the degenerate dimension for editing.

  3. In the Edit Dimension panel, add a hierarchy level above the initial level.

    You can do this by dragging the key column you want to use from the dataset to the Hierarchies section in the panel. Place it just above the hierarchy's leaf level.

    Be sure to adhere to the type restrictions mentioned in the previous example, as well as the rules for modeling hierarchies.

  4. Repeat the previous step to model additional levels based on different dataset columns.

  5. Click Apply in the Edit Dimension panel.

  6. Return to the model Canvas. The degenerate dimension now includes a hierarchy with relationships to the fact dataset columns used to create its levels.

  7. Connect a different fact dataset to the hierarchical degenerate dimension.

    You can do this by dragging columns that represent the same keys to the appropriate levels. Repeat this process for each level in the hierarchy.

You can now aggregate metrics from both of the connected fact tables by the shared dimensional key values.