Add Secondary Dimensional Attributes

Secondary dimensional attributes are independent "dimensional" attributes used to group metric data. You can add and edit them from the Dimension properties panel.

For additional information on secondary attributes, see About Dimension Attributes.

Add a single secondary attribute

To add a single secondary attribute:

1. In Design Center, open a model and switch to the Canvas tab.
2. Find the dimension you want to edit, click its context menu, and select Edit. The Dimension properties panel opens.
3. In the Hierarchies section, find the level you want to add a secondary attribute to, click its context menu, and select Create Secondary Attribute. The Secondary attribute properties panel opens.
4. Edit the available fields as needed. For descriptions, see Secondary attribute properties below.
5. Click Apply.
6. In the Dimension properties panel, click Apply to save the new attribute to the dimension.

Add multiple secondary attributes

You can create multiple secondary attributes at the same time by clicking and dragging columns from the dimensional dataset to a level in the Dimension properties panel. This automatically creates a new secondary attribute for each selected column.

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

2. Find the dimension you want to edit, click its context menu, and select Edit. The Dimension properties panel opens.

3. On the dimension Canvas, click the dimensional dataset columns you want to create secondary attributes from to select them. The selected columns are highlighted in black:

   

   Note

   The order in which you select the dataset columns determines the order of the resulting secondary attributes in the level.

4. Click and drag the selected columns to the level you want to add the secondary attributes to.

   AtScale automatically adds secondary attributes based on the selected columns to the level:

   

   All properties for the new attributes are set to the default values:

   • Display name and Unique name are derived from the name of the selected column.
   • Dataset is the dimensional dataset the selected column is from.
   • Key Columns and Value Column are both set to the selected column.

5. Edit the new secondary attributes as needed. For descriptions of available properties, see Secondary attribute properties below.

6. In the Dimension properties panel, click Apply to save the new attributes to the dimension.

Secondary attribute properties

The following sections describe the properties available for secondary attributes.

General

Display name

The user-friendly name for the secondary attribute. Consider the following specifics of the name:

• Should be unique among other secondary attributes in the parent level.
• Can be the same as the name of the parent level.
• Can be the same as the name of another secondary attribute in a different dimension.

Unique name

The SQL or MDX column name for this dimension attribute in the published AtScale model. This is how the AtScale engine refers to this attribute internally.

This value must be unique within the dimension. Additionally, you should not change this value after the model has been published, as changing it may break existing data sources and reports that rely on this model.

Description

Optional. A description of the data that this secondary attribute contains. Some BI tools can show this description, some can't. It's a good practice to enter descriptions to help other people get familiar with the data in the model. This information is also made available to AI tools.

Note

If the attribute doesn't have a manually entered description, but the Value Column the attribute is based on has a description in the data warehouse, the description of the Value Column automatically appears in the Description field. This information is not added to the dimension's SML, but is included when you deploy the model. From there, it becomes visible in BI tools (when supported), and is made available to AI tools.

You can edit the prepopulated description as needed. Any changes you make will override the description from the data warehouse.

If the description in the data warehouse changes, changes will be reflected in the attribute when you refresh the dataset in Design Center.

Sources

Dataset

The source dataset that contains the column that the secondary attribute is based on.

Key Columns

The dataset column(s) that the secondary attribute is based on. If the secondary attribute has a compound key, click the plus icon to specify other columns that comprise the key.

Unique

Specifies whether each row in the key columns contains a value that is unique within the dataset. Selecting this checkbox is equivalent to declaring the key to be a primary key. The AtScale engine uses this selection as input when joining rows from this dimension level to other datasets in the model.

• If the Key Column values are unique for each row, select this option. The join behavior would then consider the first matching row at query runtime.
• If the Key Column values are multi-valued, then leave unselected. The join behavior would then consider all matching rows at query runtime.

For more information about this check box and requirements for modeling hierarchies, see Requirements for Modeling Dimensional Hierarchies.

Value Column

The dataset column that the attribute is based on.

Sort Column

By default, sorts are on the column that you specify in the Value Column field. If you want to sort on a different column, select that column here. This applies to MDX queries only (i.e. queries received through the XMLA interface).

Data handling and formatting

Is Aggregatable

Determines whether the attribute's member values can be aggregated. When enabled, AtScale creates an All member for the attribute, whose value is the aggregation of all of the attribute's member values. The All member sits at the top of the attribute's hierarchy, though it is not a part of the attribute itself. It often serves as the attribute's default member.

Exclude from System-Generated Dimension-Only Aggregates

Excludes this attribute from system generated dimension-only aggregates. This is useful if the attribute contains a large number (millions) of distinct values that you don't want to aggregate.

Exclude from System-Generated Fact-Based Aggregates

Excludes this attribute from system generated fact-based aggregates. This is useful if the attribute contains a large number (millions) of distinct values that you don't want to aggregate.

Contains Unique Names

Determines whether each member of this level attribute has a unique name. Do not enable this functionality if two members have different keys but the same name.name.

Custom Empty Member

Enable this property to ensure un-joinable values such as NULL that may reside in the foreign key columns of the fact dataset are included in your query results. The Custom Empty Member feature provides the convenience of handling NULL values so data architects are not forced to fix their fact tables. For maximum compatibility with BI tools, you will have to add an empty member record to the dimension dataset. To learn more about Custom Empty Members, see Using Custom Empty Members for Levels and Attributes.

Dimensionally Modified Aggregates

Enables/disables the creation of dimensionally modified aggregates for the current level. The following calculation types are available:

• Prev, Next, Lead, Lag
• Siblings
• Moving Window (aka Range Operator + Lag)

When working with a time dimension, you can only select calculation types when the Time unit for the level is set to Day or longer.

For more information, see Dimensionally Modified Aggregates.

Visualization

Visualize in BI tool?

Determines whether the secondary attribute is visible in BI tools.

Expose as Excel Pivot Table property

Determines whether the secondary attribute appears in Microsoft Excel pivot tables.

When enabled, users can add the attribute to pivot tables in Excel. When disabled, users cannot add the attribute to pivot tables; however, they can still add it to reports using the Properties context menu. For more information, see Using MDX User-Defined Member Properties.

Note

For attributes that do not have this setting configured, it defaults to enabled when the model is deployed. You can control this behavior with the schema.userDefinedMember.defaultExcelPivotTableProperty global and model settings. For more information, see Schema Settings and Other Model Settings.

Expose as user-defined property

Determines whether the secondary attribute is exposed to Excel and MDX users as a user-defined member property. For more information, see Using MDX User-Defined Member Properties.

Folder

Defines the folder in which the secondary attribute appears in BI tools.