Add a 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

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 a degenerate dimension

To create a single degenerate dimension:

1. In Design Center, open a model and switch to the Canvas tab.
2. Click the column in the fact dataset you want to base your dimension on and drag it onto the Canvas.

A degenerate dimension is automatically created based on the selected column, and linked to the fact dataset.

Bulk create multiple degenerate dimensions

You can create multiple degenerate dimensions at once using the Bulk Create Degenerate Dimensions panel on the model Canvas.

To bulk 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.

   

   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 shared degenerate dimension

If your model contains multiple fact datasets, you can use columns from two or more of them to create a shared degenerate dimension. A shared degenerate dimension is one that is based on data from more than one fact dataset.

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

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

• The data types of the columns used must be consistent:

  • The key columns used from each fact dataset must have the same data type.
  • The value columns used from each fact dataset must have the same data type.

• If you include a sort column on one dataset, you must include sort columns on all other datasets. Additionally, all of the sort columns must have the same data type.

• For shared degenerate dimensions with multiple levels, all levels must be defined using the same datasets as the other levels.

• You cannot add secondary attributes on shared degenerate dimensions.

• If an order column is selected in one dataset, the order column from each of the other fact tables must be selected, as well.

To create a shared 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.
3. Open the dimension for editing, and update its values as needed.